June 8, 2011 at 12:39 pm
I created a simple trigger for insert, update, delete which inserts data into an audit table.
1. How to find out which of these three actions took place? Currently I am manipulating with INSERTED and DELETED internal tables. But are there more elegant way to find it ?
2. It works fine if I update or delete just one single record. However, if I do this in batch, it catches only one record. How to make it for each record in a batch?
Thanks
June 8, 2011 at 12:59 pm
show us your trigger, and we can help you tweak it to handle multiple rows as a set based solution..
as far as whether it's insert/update/delete, if there is any data in both the INSERTED table and DELETED tables, it's an update, otherwise test one of the other two tables to determine if it is INSERT OR DELETE...
CREATE TRIGGER TR_TBSTATE ON TBSTATE
FOR INSERT,UPDATE,DELETE
AS
BEGIN
DECLARE @INDICATOR CHAR(1)
IF EXISTS(SELECT 1 FROM INSERTED()) AND EXISTS(SELECT 1 FROM DELETED())
BEGIN
SET @INDICATOR = 'U'
END
ELSE IF EXISTS(SELECT 1 FROM INSERTED())
BEGIN
SET @INDICATOR = 'I'
END
ELSE SET @INDICATOR = 'D'
--do audit stuff below
END
Lowell
June 8, 2011 at 1:26 pm
1) NEVER EVER put inserted/deleted values into variables (unless you are doing a cursor in the trigger, in which case you have other problems). :w00t: I guarantee you without seeing your code that that is what you are doing. It is a very common and CRITICAL flaw.
2) The best solution is to have 3 triggers if you need different things for insert/update/delete. Otherwise you need to do EXISTS type checks on inserted and deleted to figure out what the nature of the calling batch is, which is inefficient.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 8, 2011 at 1:38 pm
You can make some assumptions about what happened even if you haven't seperated the triggers out to individual actions (I/U/D). For example:
inserted will be empty for a delete
deleted will be empty for an insert
For updates its a little trickier, and as a rule I *NEVER* allow key values to change, which helps here.. If you have that rule..
inserted will join directly to deleted on the key columns for an update. This gives you before and after values for the record.
It is a very common error to code triggers with the expectation of single record changes. You must always expect that more than a single record could change and handle it appropriately.
Also, I would be careful with logging triggers, don't add them to tables that are modified often unless you really need to, otherwise your processing will spend large amounts of time just logging. And don't add them to tables that don't really need them.
I built a sproc about a year ago that will generate logging triggers and only write something when a field actually changes, which is not something you can assume just because the trigger was fired.
CEWII
June 9, 2011 at 9:20 am
Thanks all for your input.
I had similar techinique to what Lowell suggested, but yours is better. And I also removed the code where I select inserted and deleted into a table variable. I decided to stay with one common trigger instead of splitting them on three.
About Elliot's suggestions: all these tables that I create triggers on, are dimention tables, so they are changed infrequently. Now my code handles not just sinle update, but whole batch. That's because I insert entire record set from inserted/deleted internal tables.
June 9, 2011 at 9:30 am
Here are the articles I wrote on the subject.
http://www.sqlservercentral.com/articles/Auditing/63247/
http://www.sqlservercentral.com/articles/Auditing/63248/
Make sure to read the discussions as well, since there's a lot of good data in those.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply