Have a table that has following fields (pkid, field1, field2, field3,
field4). I need to create a trigger that will insert a row into another
table with the pkid column that was updated. Any help will be appreciated. 9 1966
Chico Che,
This is just an outline since I don't have your table structures, but the
pseudo-table 'inserted' contains the data of any inserted rows and the after
data for any updated rows.
INSERT INTO AnotherTable
SELECT pkid FROM inserted
RLF
"Chico Che" <js*****@yahoo.comwrote in message
news:Xn**********************@216.196.109.144...
Have a table that has following fields (pkid, field1, field2, field3,
field4). I need to create a trigger that will insert a row into another
table with the pkid column that was updated. Any help will be appreciated.
First, thanks for the response
But keep getting invalid object name on the table
Please show your code. And which table has the invalid name? Etc?
RLF
"Chico Che" <js*****@yahoo.comwrote in message
news:Xn**********************@216.196.97.131...
First, thanks for the response
But keep getting invalid object name on the table
"Russell Fields" <ru***********@nomail.comwrote in news:##pzw30bIHA.5348
@TK2MSFTNGP03.phx.gbl:
Please show your code. And which table has the invalid name? Etc?
RLF
"Chico Che" <js*****@yahoo.comwrote in message
news:Xn**********************@216.196.97.131...
>First, thanks for the response
But keep getting invalid object name on the table
CREATE TRIGGER iolta_change on tbldata
AFTER INSERT AS
BEGIN
INSERT INTO tblaudit
SELECT pkid FROM inserted
END
Chico Che (js*****@yahoo.com) writes:
>>First, thanks for the response
But keep getting invalid object name on the table
CREATE TRIGGER iolta_change on tbldata
AFTER INSERT AS
BEGIN
INSERT INTO tblaudit
SELECT pkid FROM inserted
END
I will need to ask: what do you really expect us to help with? What
the name is of your tables?
The only thing I can say is that the above looks funny. Does tblaudit
only have a single column? Best practice is to always include a column
list with the INSERT statement to state which columns you are inserting
into.
Also, in your original post, you talke about updates. Here you have a
trigger for INSERT.
I'm afraid that if you don't give us more detail on what you want to
achieve, that we will not be able to help you.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Chico,
Perhaps this link will help you understand what we are looking for. Read
the article "Please provide DDL and sample data" at http://www.aspfaq.com/etiquette.asp?id=5006
RLF
"Chico Che" <js*****@yahoo.comwrote in message
news:Xn**********************@216.196.97.131...
"Russell Fields" <ru***********@nomail.comwrote in news:##pzw30bIHA.5348
@TK2MSFTNGP03.phx.gbl:
>Please show your code. And which table has the invalid name? Etc?
RLF
"Chico Che" <js*****@yahoo.comwrote in message news:Xn**********************@216.196.97.131...
>>First, thanks for the response
But keep getting invalid object name on the table
CREATE TRIGGER iolta_change on tbldata
AFTER INSERT AS
BEGIN
INSERT INTO tblaudit
SELECT pkid FROM inserted
END
We have two tables
Create Table tblData(
pkid int not null,
field1 varchar(55),
field2 varchar(55),
field3 varchar(55)
field4 varchar(55))
Create Table tblAudit
(recid int not null)
A program is contantly updating or insert rows in table tblData. We want to
create a trigger that will insert the pkid from tblData into tblAudit when
a new row is inserted or a row is updated.
I apologize, first time poster.
Thanks
Chico Che,
Aside from a missing comma in your tblData DDL, everything works just fine.
I created the tables, the trigger from the previous post, inserted data and
the trigger inserted data to the audit table. So, I still don't know what
error you are getting, unless it was that tblData was not created. But your
trigger was also only for INSERT not for UPDATE.
So...
Create Table tblData(
pkid int not null,
field1 varchar(55),
field2 varchar(55),
field3 varchar(55), -- Added a comma here
field4 varchar(55))
Create Table tblAudit
(recid int not null)
CREATE TRIGGER iolta_change on tbldata
AFTER INSERT, UPDATE AS -- Added for both update and insert
BEGIN
INSERT INTO tblaudit
SELECT pkid FROM inserted
END
Having said that, Erland correctly pointed out the tiny audit table is not
very useful for auditing. Perhaps at least tracking who made the change and
when:
Create Table tblAudit
(recid int not null,
loginname nvarchar(128),
changedatetime datetime)
CREATE TRIGGER iolta_change on tbldata
AFTER INSERT, UPDATE AS
BEGIN
INSERT INTO tblaudit
SELECT pkid,
SUSER_SNAME(), -- current login name
GETDATE() -- current date and time
FROM inserted
END
Then if you want a delete trigger, you can create one of those as well.
RLF
"Chico Che" <js*****@yahoo.comwrote in message
news:Xn**********************@216.196.97.131...
We have two tables
Create Table tblData(
pkid int not null,
field1 varchar(55),
field2 varchar(55),
field3 varchar(55)
field4 varchar(55))
Create Table tblAudit
(recid int not null)
A program is contantly updating or insert rows in table tblData. We want
to
create a trigger that will insert the pkid from tblData into tblAudit when
a new row is inserted or a row is updated.
I apologize, first time poster.
Thanks
On Fri, 15 Feb 2008 14:20:57 -0600, Chico Che wrote:
>We have two tables
Create Table tblData(
pkid int not null,
field1 varchar(55),
field2 varchar(55),
field3 varchar(55)
field4 varchar(55))
Create Table tblAudit
(recid int not null)
A program is contantly updating or insert rows in table tblData. We want to create a trigger that will insert the pkid from tblData into tblAudit when a new row is inserted or a row is updated.
I apologize, first time poster.
Thanks
Hi Chico,
The table is called tblAudit (capital A) here, but tblaudit (small a) in
the trigger code you posted. If your database uses a case sensitive
collation, these names are considered different.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Joel Thornton |
last post by:
Whenever something is inserted to a given table, I want to run some
shell commands using xp_cmdshell. Would it be a bad idea to put this
xp_cmdshell in the INSERT trigger of this table?
I...
|
by: Matik |
last post by:
Hello to all,
I have a small question.
I call the SP outer the DB. The procedure deletes some record in table
T1.
The table T1 has a trigger after delete.
This is very importand for me, that...
|
by: Scott CM |
last post by:
I have a multi-part question regarding trigger performance. First of
all, is there performance gain from issuing the following within a
trigger:
SELECT PrimaryKeyColumn FROM INSERTED
opposed...
|
by: robert |
last post by:
typed this into the ibm.com search window, but didn't get anything
that looked like it would answer a question:
+trigger +faster +db2 +cobol
the question: are DB2 (390/v6, at the moment)...
|
by: Dave Sisk |
last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table.
(Note this is an external trigger defined with the ADDPFTRG CL command, not
a SQL trigger defined with the CREATE...
|
by: Bob Stearns |
last post by:
I have two (actually many) dates in a table I want to validate on
insertion. The following works in the case of only one WHEN clause but
fails with two (or more), with the (improper?...
|
by: ChrisN |
last post by:
Hello all,
I have a quick question. I'm using a C# object to commit new rows to a
database. In the database I have an INSERT Trigger watching values come in.
If the record to be committed...
|
by: teddysnips |
last post by:
I need a trigger (well, I don't *need* one, but it would be optimal!)
but I can't get it to work because it references ntext fields.
Is there any alternative? I could write it in laborious code...
|
by: Ots |
last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app.
I have an Audit trigger that logs changes to tables. I want to apply
this trigger to many different tables. It's the same trigger,...
|
by: JohnO |
last post by:
Hi All,
This question is related to iSeries V5R4 and db2.
I want to implement an AFTER DELETE trigger to save the deleted rows
to an archive table, I initially defined it as a FOR EACH...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |