How to get the action performed in trigger

Question

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

Answer 1

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