Generic DML Trigger Code

Generic code to cope with DML actions on your tables

CREATE TRIGGER [schemaname].[TRG_triggername]
ON [schemaname].[tablename]
FOR DELETE,INSERT,UPDATE
AS
DECLARE @ACTION CHAR(6)
DECLARE @DELETE BIT
DECLARE @INSERT BIT

SET @DELETE = 0
SET @INSERT = 0

IF EXISTS (SELECT TOP 1 1 FROM DELETED)  SET @DELETE = 1
IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INSERT = 1

IF @INSERT = 1 AND @DELETE = 1 SET @ACTION = 'UPDATE'
IF @INSERT = 1 AND @DELETE = 0 SET @ACTION = 'INSERT'
IF @DELETE = 1 AND @INSERT = 0 SET @ACTION = 'DELETE'
IF @INSERT = 0 AND @DELETE = 0 RETURN

/*

Do Stuff here.
Refer to Added and Updated records from the INSERTED table
select * from inserted
Refer to Removed records from the DELETED table
select * from deleted
e.g.
INSERT INTO AuditTable 'INSERT', GETDATE(), * FROM INSERTED
*/
GO