CODEWITHSUNDEEP
sqlsql-servertriggerssql-server-2012
Red Devil
Red Devil

Reputation: 2403

How to get the action performed in trigger

What I want to achieve in this question is if I made any update/delete/insert in 1 of my table it should insert that record into 1 of the table for the logs purpose

I have 1 table as test. Suppose If I insert the below row in my table test, it should fire a trigger and insert a row in my another table which is logs

test

Id  |   Name    |   Location        
1   |   Test    |   America

log

ID  |   updatetime              |   Action      
----------------------------------------------------------      
1   |   2017-04-06 16:51:18.190 |   Insert

And same if I delete or update any thing under action it should have delete or update respectively 

ID  |   updatetime              |   Action      
----------------------------------------------------------      
1   |   2017-04-06 16:51:18.190 |   Insert  
1   |   2017-04-06 16:51:18.190 |   Delete

I have created a single trigger

create trigger abc 
on test
after insert, update, delete
as
begin
    declare @id int

    select @id = i.id from inserted i

    insert into log values (@id, GETDATE())
end

From the above trigger I am able to get the ID and updatetime, But how to get the action column like what action has been performed how to achieve that. Can anyone have a clue

Upvotes: 2

Views: 5040

Answers (2)

Jeff Moden
Jeff Moden

Reputation: 3503

If the action was caused by an INSERT, there will be no rows in the DELETED psuedo table.

If the action as caused by a DELETE, there will be no rows in the INSERTED pseudo table.

If neither of the above is true, then it was an UPDATE because an UPDATE inserts rows into BOTH the INSERTED and DELETED tables.

Of course, there is the occasional "4th condition" where something was executed but no rows were affected and you'd like to simply "Short Circuit" the trigger and leave it without executing any part of it.

All that makes the "Trigger Action" really simple to determine...

--===== Shortcircuit any zero-row activity because there will be no changes to record.
     IF @@ROWCOUNT = 0 RETURN
;
--===== Determine the type of trigger action
DECLARE @Operation CHAR(1);
 SELECT @Operation    = CASE
                        WHEN NOT EXISTS (SELECT TOP 1 1 FROM DELETED)  THEN 'I'
                        WHEN NOT EXISTS (SELECT TOP 1 1 FROM INSERTED) THEN 'D'
                        ELSE 'U'
                        END
;

You could probably combine the CASE determination in the DECLARATION of the variable but that frightens some people, so I didn't offer it that way.

This is a part of "KISS"... "Keep It Super Simple".

And, yes... you could change the "TOP 1 1" to just * but that also seems to get people and automatic code checkers upset.

Upvotes: 0

ahmed abdelqader
ahmed abdelqader

Reputation: 3568

You can Create the next approach for Determining which action happens:-

DECLARE @Action as char(1);
    SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED)
                         AND EXISTS(SELECT * FROM DELETED)
                        THEN 'U'  -- Set Action to Updated.
                        WHEN EXISTS(SELECT * FROM INSERTED)
                        THEN 'I'  -- Set Action to Insert.
                        WHEN EXISTS(SELECT * FROM DELETED)
                        THEN 'D'  -- Set Action to Deleted.
                    END)

-- For Getting the ID
if  @Action = 'D'
select @id=i.id from DELETED i
else -- Insert or Update
select @id=i.id from INSERTED i

Upvotes: 4

Related Questions