Solved

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

Posted on 2008-10-23
7
1,605 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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

Suggested Solutions

Title # Comments Views Activity
Sql Permission 6 81
How to place a condition in a filter criteria in t-sql? 12 92
My Query is not giving correct result. Please help 5 57
SQL- GROUP BY 4 53
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

752 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