Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to Identify Which trigger fired (Updated or Delete)

Tags:

sql

triggers

If i am using a trigger which triggers on every update or Delete of my payment table, How can I identify the type of trigger which is occurred (If I explain more, the trigger function invoked because of updating a record or deleting a record.)

Because I need that information to store on my another table.

like image 743
New Developer Avatar asked Jan 14 '23 20:01

New Developer


1 Answers

Instruction: when you insert data into table, only 'inserted' has the new inserted rows; when you delete data from table, only 'deleted' has the deleted rows; when you update table, the 'inserted' saves the new rows, the 'deleted' saves the old rows.

I think this sample can give you a hint. (SQL Server 2012)
Sample:
Sample Table Definition:


create table Customer (
          ID int identity(1,1) not null, 
          FirstName varchar(30), 
          LastName varchar(30))
Trigger:

CREATE TRIGGER TrackAction
ON Customer
AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
   DECLARE @DELETEDCOUNT INT
   DECLARE @INSERTEDCOUNT INT
   SELECT @DELETEDCOUNT = COUNT() FROM deleted
   SELECT @INSERTEDCOUNT = COUNT() FROM inserted
   IF(@DELETEDCOUNT&@INSERTEDCOUNT > 0 )
       PRINT 'Trigger by update action'
   ELSE IF (@DELETEDCOUNT > 0 )
       PRINT 'Trigger by delete action'
   ELSE IF (@INSERTEDCOUNT > 0 )
       PRINT 'Trigger by insert action'
END
Test code:

insert into Customer 
values ('Bob','Ryan')
update customer 
set FirstName = 'Bob Jr.'
where FirstName = 'Bob'
delete customer 
where FirstName = 'Bob Jr.'
Test Result:
1. Trigger by insert action
2. Trigger by update action
3. Trigger by delete action
like image 130
ljh Avatar answered Jan 19 '23 11:01

ljh