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
