asked on

Trigger for INSERT, UPDATE, DELETE - determine which event happened within the trigger?

I'd like to implement a single trigger on a table for INSERT, UPDATE, DELETE. Within that trigger, how can I determine which event caused it to fire? For example, in pseudo-code

Thanks

create trigger [dbo].t_mytrigger
   on
      [dbo].[mytable]
 for
    INSERT, UPDATE, DELETE
AS
    -- begin pseudo code
 
    IF INSERT THEN
              DO ONE THING
    IF UPDATE THEN
            DO OTHER THINGS
     IF DELETE THEN
           DO YET ANOTHER THING

Open in new window

Cvijo123

you can do 3 different triggers if u need to do different things inside those triggers

create trigger [dbo].t_mytrigger_insert on [dbo].[mytable]
for INSERT

-- some code for insert trigger

create trigger [dbo].t_mytrigger_update on [dbo].[mytable]
for UPDATE
-- some code for update trigger

create trigger [dbo].t_mytrigger_delete on [dbo].[mytable]
for DELETE
-- some code for delete trigger

PMH4514

ASKER

I don't want to do three different triggers because that would result in a significant amount of duplicated code. I want one trigger that can do several steps, and then do a couple different things based on if insert, update or delete.

ASKER CERTIFIED SOLUTION

Cvijo123

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PMH4514

ASKER

Interesting..

would it not be better to use exists instead of count(*) ?

Is there any potential for confusion if for example an update trigger fires but no records were actually touched since update and insert both use INSERTED ?

Thanks!

Cvijo123

there is no confusion since update use both tables inserted and updated while insert trigger only use inserted and delete trigger only deleted, so basicly i dont see confusion.

If there is no record updated from update trigger then u wont do any code since no join will be matched with 0 records isnt it ?

Cvijo123

besides u can use first line as

if (select count(*) from inserted) = 0 and (select count(*) from deleted) = 0 )
return --no action

PMH4514

ASKER

I see. Thanks!