Finding DML action that fired trigger
-
Hi all,
If I have a trigger for insert, update, and delete, is there an easy way to find out if the DML action that fired the trigger was insert, update, or delete? Right now I just check the counts in the inserted and deleted pseudo-tables, but it seems rather inelegant
Thanks!
Dennis
-
Triggers are supposed to be transparent. If your goal is to determine what DML action occured and base your code logic on what happened, I would suggest that you do not combine your triggers. You should only combine your triggers if they have a common task. You should try creating 3 triggers, one for each event. This way you do not have to worry about checking what happened.
-
True.
Remember that you can have as many triggers as you want, even for the same event.
Regardless of that, I guess you can try to check the existance of the INSERTED and DELETED tables to check the action.
INSERT => Inserted
UPDATED => Inserted | Deleted
DELETED => Deleted
Cheers,
Gonzalo
-
Both pseudo tables exist for every trigger. Here is a code snippet I include in every multiple-operation trigger:
if @@RowCount = 0return; -- Nothing to work with, so get out.declare @ICount integer,@DCount integer;select @ICount = Count(*)from Inserted;select @DCount = Count(*)from Deleted;if @DCount = 0 begin -- This is an INSERT operation...end;else if @ICount = 0 begin -- This is a DELETE operation...end;else begin -- This is an UPDATE operation...end;Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
Adam Haines (3/18/2008)
Triggers are supposed to be transparent. If your goal is to determine what DML action occured and base your code logic on what happened, I would suggest that you do not combine your triggers. You should only combine your triggers if they have a common task. You should try creating 3 triggers, one for each event. This way you do not have to worry about checking what happened.Sometimes you have a case where 90% of the logic is the same, however, you have to do one thing different if it is an INSERT vs UPDATE, etc. Like say set a [font="Courier New"]LastAction [/font]field to [font="Courier New"]'I'[/font] or [font="Courier New"]'U'[/font]. In such a case it seems reasonable to me to minimize the edit-sensitivity of the maintenance burden by using only a single trigger with a branch for that one difference.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Tomm Carr (3/19/2008)
Both pseudo tables exist for every trigger. Here is a code snippet I include in every multiple-operation trigger.Nicely done :). Actually, I've never checked the "existence" of those table. When the code is "almost" the same I've created a SP to reutilize the code
(IF POSSIBLE).Cheers,
G
-
Thanks all. I actually use a code snippet very similar to the count from inserted/deleted tables.
-
Sometimes you have a case where 90% of the logic is the same, however, you have to do one thing different if it is an INSERT vs UPDATE, etc. Like say set a LastAction field to 'I' or 'U'. In such a case it seems reasonable to me to minimize the edit-sensitivity of the maintenance burden by using only a single trigger with a branch for that one difference.
A very valid point :). Sometimes manageability does outweigh simplicity. I guess it depends on the environment and personal preference.
-
Agreed, Adam.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply