Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TRIGGER: UPDATE, DELETE or INSERT? Expand / Collapse
Author
Message
Posted Tuesday, October 5, 2010 2:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 14, 2014 7:39 AM
Points: 111, Visits: 307
Hi,

I hope a simple question. When I create a trigger on update, insert and delete, is there a way to find out which of these events fired the trigger?

Here is some pseudocode:

CREATE TRIGGER justatest
ON testtable
FOR UPDATE, INSERT, DELETE
AS
BEGIN
IF @@update-fired-trigger = 1
INSERT INTO log 'a record was updated'
ELSE IF @@insert-fired-trigger = 1
INSERT INTO log 'a record was inserted'
ELSE IF @@delete-fired-trigger = 1
INSERT INTO log 'a record was deleted'
END

Thanks
Post #998135
Posted Tuesday, October 5, 2010 2:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
IF EXISTS (SELECT 1 FROM inserted) 
IF EXISTS (SELECT 1 FROM deleted)
PRINT 'Is Update'
ELSE
PRINT 'Is Insert'
ELSE
PRINT 'Is Delete'

Basically, if there are rows in both inserted and deleted, it's an update. If there are rows only in inserted, it's an insert. If there are rows only in deleted, it's a delete.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #998137
Posted Tuesday, October 5, 2010 2:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 14, 2014 7:39 AM
Points: 111, Visits: 307
Thanks for your reply.... it's clear!

Ray
Post #998143
Posted Wednesday, October 6, 2010 1:46 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 30, 2011 12:09 AM
Points: 10, Visits: 29
t
Post #999020
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse