How to Identify Which trigger fired (Updated or Delete)

Question

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.

This post might help: http://stackoverflow.com/questions/5252802/how-to-use-update-trigger-to-update-another-table — ophintor, Mar 24 '13 at 01:36
You really need to identify the database product you are using; the answer will depend on that. — Euro Micelli, Mar 24 '13 at 01:41
I found the answer. Thanks for every one who spent time on my question. — New Developer, Mar 24 '13 at 08:30

ljh · Accepted Answer · 2013-03-24T08:03:31.297

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

score 0 · Answer 2 · answered Mar 24 '13 at 00:56

MS SQL Server?

You don't.

There are single commands in SQL that will result in updates, inserts and deletes at the same time (see MERGE, for example). The trigger will be called once, with the [deleted], [inserted], and [updated] pseudo-tables all populated. It doesn't make sense to talk about whether one or the other happened.

score 0 · Answer 3 · answered Jan 27 '16 at 22:38

One trick I use while debugging is to use a PRINT inside the trigger that references the trigger name. If you include COUNT(*) FROM INSERTED and COUNT(*) FROM DELETED, you can then watch the Messages output (in SSMS at least) to see which triggers fired, the order they fired and if it was an insert, update or delete that triggered it. Inserts will have COUNT(*)=0 for DELETED, Deletes will have COUNT(*)=0 for INSERTED and updates will have COUNT(*)>0 for both INSERTED and DELETED.

How to Identify Which trigger fired (Updated or Delete)

3 Answers3