How to Identify Which trigger fired (Updated or Delete)
sql, triggers
Solution
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
Problem
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.