Solved

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

Posted on 2008-10-23
7
1,610 Views
Last Modified: 2012-05-05
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
Comment
Question by:PMH4514
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22787066
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
 

Author Comment

by:PMH4514
ID: 22787265
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
 
LVL 5

Accepted Solution

by:
Cvijo123 earned 125 total points
ID: 22787335
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:PMH4514
ID: 22787738
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
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22787843

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
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22787864
besides u can use first line as

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

Author Comment

by:PMH4514
ID: 22787875
I see. Thanks!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question