How to identify the operation type(insert,update,delete) in SQL Server trigger
10,946
Solution 1
For Inserted : Rows are in inserted only. For Updated: Rows are in inserted and deleted. For Deleted: Rows are in deleted only.
DECLARE @event_type varchar(42)
IF EXISTS(SELECT * FROM inserted)
IF EXISTS(SELECT * FROM deleted)
SELECT @event_type = 'update'
ELSE
SELECT @event_type = 'insert'
ELSE
IF EXISTS(SELECT * FROM deleted)
SELECT @event_type = 'delete'
ELSE
--no rows affected - cannot determine event
SELECT @event_type = 'unknown'
Solution 2
This is a simplified version of Mikhail's answer that uses a searched CASE expression.
DECLARE @Operation varchar(7) =
CASE WHEN EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
THEN 'Update'
WHEN EXISTS(SELECT * FROM inserted)
THEN 'Insert'
WHEN EXISTS(SELECT * FROM deleted)
THEN 'Delete'
ELSE
NULL --Unknown
END;
Author by
Gopal00005
Hey this is Gopal Ahir, I'm a passionate programmer and working as a Sr. Software Engineer at Crest Data Systems located at Ahmedabad, Gujarat, India.
Updated on June 24, 2022Comments
-
Gopal00005 almost 2 years
We are using the following trigger in SQL Server to maintain the history now I need to identify the operations just like insert,update or delete. I found some information HERE but it doesn't works with the SQL Server.
CREATE TRIGGER audit_guest_details ON [PMS].[GSDTLTBL] FOR INSERT,UPDATE,DELETE AS DECLARE @SRLNUB1 INT; DECLARE @UPDFLG1 DECIMAL(3,0); SELECT @SRLNUB1 = I.SRLNUB FROM inserted I; SELECT @UPDFLG1 = I.UPDFLG FROM inserted I; BEGIN /* Here I need to identify the operation and insert the operation type in the GUEST_ADT 3rd field */ insert into dbo.GUEST_ADT values(@SRLNUB1,@UPDFLG1,?); PRINT 'BEFORE INSERT trigger fired.' END; GO
But here I need to identify the operation and want to insert operation type accordingly.
Here I don't want to create three trigger for every operations