Solved

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

Posted on 2008-10-23
7
1,599 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
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to enforce inte 8 52
Query - which index being used? 2 53
Need help debbuging stored procedure 21 41
SQL Query with Sum and Detail rows 2 50
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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