March 18, 2008 at 1:38 am
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
March 18, 2008 at 6:59 am
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.
March 18, 2008 at 7:14 am
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
March 19, 2008 at 3:10 pm
Both pseudo tables exist for every trigger. Here is a code snippet I include in every multiple-operation trigger:
if @@RowCount = 0
return; -- 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
March 19, 2008 at 3:29 pm
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]
March 19, 2008 at 6:59 pm
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
March 20, 2008 at 2:24 am
Thanks all. I actually use a code snippet very similar to the count from inserted/deleted tables.
March 20, 2008 at 11:40 am
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.
March 20, 2008 at 1:39 pm
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