Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1627
  • Last Modified:

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

0
PMH4514
Asked:
PMH4514
  • 4
  • 3
1 Solution
 
Cvijo123Commented:
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
0
 
PMH4514Author Commented:
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.
0
 
Cvijo123Commented:
well you can do it this way than:

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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
PMH4514Author Commented:
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!
0
 
Cvijo123Commented:

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 ?
0
 
Cvijo123Commented:
besides u can use first line as

if (select count(*) from inserted) = 0 and (select count(*) from deleted) = 0 )
return --no action
0
 
PMH4514Author Commented:
I see. Thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now