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.
Author |
Topic |
pvccaz
Yak Posting Veteran
87 Posts |
Posted - 2013-01-31 : 14:52:14
|
Hi,I need help writing a trigger that inserts records into audit table only if a couple of columns gets updated.I wrote this trigger for testing.CREATE TRIGGER upd_trg ON table1FOR UPDATEIF (UPDATE(col1) OR UPDATE(col2))BEGIN INSERT INTO audit_tbl(col1, col2, dt) SELECT col1, col2, getdate() from insertedENDBut when i update the table1 for a different column from the client application that uses this table the entire row gets updated in this table1 and this trigger fires. So i am trying rewrite the trigger to compare the values using the inserted and deleted tables of the trigger and running to problems.CREATE TRIGGER upd_trg ON table1FOR UPDATEIF inserted.col1 <> deleted.col1 OR inserted.col2 <> deleted.col2 INSERT INTO audit_tbl(col1, col2, dt) SELECT col1, col2, getdate() from insertedI am getting error in this one. Can somebody help in pointing me in the correct way of writing this.Thanks |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-01-31 : 15:02:56
|
inserted and deleted may have many rows so try this. And if any of the columns are NULL-able then you'll need to coalesce or isnull the values to compare in the WHERE clause because you can't compare NULL to anything.INSERT INTO audit_tbl(col1, col2, dt)SELECT i.col1, i.col2, getdate() from inserted iinner join deleted d on d.PKs = i.PKswhere not ( i.col1 = d.col1 and i.col2 = d.col2 ) Be One with the OptimizerTG |
|
|
pvccaz
Yak Posting Veteran
87 Posts |
Posted - 2013-01-31 : 17:08:56
|
Prefect !!. Thanks !! That worked. I also want to insert a record into the audit table and added FOR UPDATE, INSERT and checked for the deleted counts and insereted counts. Modified script below and it worksIs there any other good way to check if insert happened in that table and then insert into audit tableCREATE TRIGGER trg1 ON Tbl1FOR UPDATE, INSERTASBEGININSERT INTO audit_tbl(col1, col2, dt)SELECT i.col1, i.col2, getdate() from inserted iinner join deleted d on d.PKs = i.PKswhere not ( i.col1 = d.col1 and i.col2 = d.col2 )ENDBEGIN DECLARE @insertcount int DECLARE @deletecount int SET @insertcount = (SELECT count(*) from inserted) SET @deletecount = (SELECT count(*) from deleted) IF @insertcount > 0 AND @deletecount = 0 BEGIN INSERT INTO audit_tbl(col1, col2, dt) SELECT i.col1, i.col2, getdate() from inserted i ENDENDThanks |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-01-31 : 18:07:15
|
use my query but change it to LEFT OUTER JOIN to deleted. Then you'll know if it is an insert if the deleted PK values are NULL. Something like this:INSERT INTO audit_tbl(col1, col2, dt, userAction)SELECT i.col1, i.col2, getdate(), case when d.PK is null then 'I' else 'U' end from inserted iinner join deleted d on d.PKs = i.PKswhere d.PK is nullOR not ( i.col1 = d.col1 and i.col2 = d.col2 ) Be One with the OptimizerTG |
|
|
pvccaz
Yak Posting Veteran
87 Posts |
Posted - 2013-01-31 : 18:19:47
|
Great !. Thanks for you help ! |
|
|
|
|
|
|
|