Audit Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Audit Trigger

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-07-31 : 10:16:30

I need to keep track of changes made to a table. Any time the fields rqtyrequested and dtshipdate are changed I would like it to write the old record to a new table.

Here is my first attempt. Am I even close????


insert into shipdtlaud(
stradingpartner,
sshipto,
sorigdoc,
spartnbr,
rqtyrequested,
dtshipdate_sql
select
shipdtl.stradingpartner,
shipdtl.sshipto,
shipdtl.sorigdoc,
shipdtl.spartnbr,
shipdtl.rqtyrequested,
shipdtl.dtshipdate_sql
from shipdtl join inserted on shipdtl.stradingpartner = inserted.stradingpartner and shipdtl.sshipto = inserted.sshipto and shipdtl.spartnbr = inserted.spartnbr and shipdtl.sorigdoc = inserted.sorigdoc
where inserted.rqtyrequested <> shipdtl.rqtyrequested or inserted.dtshipdate_sql <> shipdtl.dtshipdate_sql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 10:21:04

try like this

insert into shipdtlaud(
stradingpartner,
sshipto,
sorigdoc,
spartnbr,
rqtyrequested,
dtshipdate_sql
select
deleted.stradingpartner,
deleted.sshipto,
deleted.sorigdoc,
deleted.spartnbr,
deleted.rqtyrequested,
deleted.dtshipdate_sql
from deleted join inserted on deleted.stradingpartner = inserted.stradingpartner and deleted.sshipto = inserted.sshipto and deleted.spartnbr = inserted.spartnbr and deleted.sorigdoc = inserted.sorigdoc
where inserted.rqtyrequested <> deleted.rqtyrequested or inserted.dtshipdate_sql <> deleted.dtshipdate_sql

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-07-31 : 11:10:52

I thought I had something slightly wrong. That works great.

Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources