[SOLVED] Identify the operation type (insert, update, delete) in trigger

Identify the operation type (insert, update, delete) in trigger

We are using the following trigger in SQL Server to maintain the history now I need to identify the operation e.g. insert, update or delete. I found some information HERE but it doesn't work with the SQL Server.

CREATE TRIGGER audit_guest_details ON [PMS].[GSDTLTBL] 
FOR INSERT,UPDATE,DELETE
AS
    DECLARE @SRLNUB1 INT;
    DECLARE @UPDFLG1 DECIMAL(3,0);

    SELECT @SRLNUB1 = I.SRLNUB FROM inserted I;
    SELECT @UPDFLG1 = I.UPDFLG FROM inserted I;   

    BEGIN
       /* Here I need to identify the operation and insert the operation type in the GUEST_ADT 3rd field */
       insert into dbo.GUEST_ADT values(@SRLNUB1,@UPDFLG1,?);

       PRINT 'BEFORE INSERT trigger fired.'
    END;
GO

But I need to identify the operation and want to insert the operation type accordingly.

I don't want to create three triggers for every operation.

Solution

For Inserted : Rows are in inserted only.
For Updated: Rows are in inserted and deleted.
For Deleted: Rows are in deleted only.

DECLARE @event_type varchar(42)

IF EXISTS (SELECT * FROM inserted)
  IF EXISTS (SELECT * FROM deleted)
    SELECT @event_type = 'update'
  ELSE
    SELECT @event_type = 'insert'
ELSE
  IF EXISTS (SELECT * FROM deleted)
    SELECT @event_type = 'delete'
  ELSE
    --no rows affected - cannot determine event
    SELECT @event_type = 'unknown'