• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1636
  • 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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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