Example checking for DML type of trigger

Most of the time it is better to have separate insert/update/delete triggers if you need to do different things based on the DML type.  If you did want to have common code then could call a stored procedure with the DML type as a parameter.

In the rare instances where it may be more practical to have one trigger to rule them all, here is an example of how to check for the type of DML activity of the transaction executing the trigger.


ALTER TRIGGER dbo.tr_test_iud
   ON  dbo.test
   AFTER INSERT,UPDATE,DELETE
AS 
   DECLARE @trgtype varchar(10)
BEGIN
   SET NOCOUNT ON;

   IF EXISTS (SELECT TOP 1 1 FROM inserted)
      IF EXISTS (SELECT TOP 1 1 FROM deleted) SET @trgtype = 'UPDATE'
      ELSE SET @trgtype = 'INSERT'
   ELSE SET @trgtype = 'DELETE'

   PRINT @trgtype
END
GO


Keep in mind also, it is best practice to always code for there being more than one record involved in the transaction resulting in multiple records in the insert/deleted tables.

Server not configured for DATA ACCESS

Error: Server 'SERVER_NAME' is not configured for DATA ACCESS.


Resolution:
exec sp_serveroption 'SERVER_NAME', 'data access', 'true'
go