update insert delete trigger - how to determine which action caused the trigger

update insert delete trigger - how to determine which action caused the trigger

Post by mordgelli » Thu, 07 Feb 2008 02:32:51


In an update, insert, delete trigger how do I deterimine which action
caused the trigger. Is the best way to do it by checking the row count
in the deleted and inserted tables, as shown below?

if (select count(*) from inserted) <> 0 and (select count(*) from
deleted) = 0 --insert
begin

end
if (select count(*) from inserted) <> 0 and (select count(*) from
deleted) <> 0 --update
begin

end
if (select count(*) from inserted) = 0 and (select count(*) from
deleted) <> 0 --delete
begin

end
 
 
 

update insert delete trigger - how to determine which action caused the trigger

Post by Tom Morea » Thu, 07 Feb 2008 03:20:33

Use exists, instead of COUNT(*).

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau




In an update, insert, delete trigger how do I deterimine which action
caused the trigger. Is the best way to do it by checking the row count
in the deleted and inserted tables, as shown below?

if (select count(*) from inserted) <> 0 and (select count(*) from
deleted) = 0 --insert
begin

end
if (select count(*) from inserted) <> 0 and (select count(*) from
deleted) <> 0 --update
begin

end
if (select count(*) from inserted) = 0 and (select count(*) from
deleted) <> 0 --delete
begin

end

 
 
 

update insert delete trigger - how to determine which action caused the trigger

Post by Alex Kuzne » Thu, 07 Feb 2008 03:45:36


note that even if your INSERT/UPDATE /DELETE did not touch any rows,
the trigger is still fired, and both

select count(*) from inserted
and
select count(*) from deleted

return zero. So you cannot distinguish a DELETE which deleted 0 rows
from an update which updated 0 rows.
 
 
 

update insert delete trigger - how to determine which action caused the trigger

Post by Tom Morea » Thu, 07 Feb 2008 03:51:14

*Usually*, that's not a problem and we start off a trigger with the
following:

if @@ROWCOUNT = 0
return

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau






note that even if your INSERT/UPDATE /DELETE did not touch any rows,
the trigger is still fired, and both

select count(*) from inserted
and
select count(*) from deleted

return zero. So you cannot distinguish a DELETE which deleted 0 rows
from an update which updated 0 rows.
 
 
 

update insert delete trigger - how to determine which action caused the trigger

Post by Alex Kuzne » Thu, 07 Feb 2008 05:24:51


yep, usually that's not a problem at all. One just needs to be aware
of this situation, and might want to include this boundary condition
in unit tests for the trigger.
 
 
 

update insert delete trigger - how to determine which action caused the trigger

Post by TheSQLGur » Thu, 07 Feb 2008 08:23:23

Unit tests?? What are those?? :-))

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
 
 
 

update insert delete trigger - how to determine which action caused the trigger

Post by Alex Kuzne » Thu, 07 Feb 2008 12:02:34


Unit tests are those little yellow-and-green dots on the left side ;)