SQL Server Triggers – After Insert, Update, and Delete – An example

When a DML statement is issued against a table, a DML trigger is fired. It can execute before or after the DML procedure depending on the condition.

In this article, you’ll know how to write a trigger that writes an entry into an audit table, whenever a row is inserted or updated, or deleted on the source table.

CREATE TRIGGER trgEmpInsDelUpd ON Emp AFTER INSERT, UPDATE, DELETE
AS

BEGIN

SET NOCOUNT ON;

IF EXISTS(SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) 

BEGIN

INSERT INTO EmpAudit

( EmpID, Ename, JobID, Mgr, HireDate, Sal, Comm, DeptNo, LocID, EventType, EventDateTime )

SELECT
i.EmpID,
i.Ename,
i.JobID,
i.Mgr,
i.HireDate,
i.Sal,
i.Comm,
i.DeptNo,
i.LocID,
‘Updated‘,
GETDATE()
FROM inserted i

END

ELSE IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)

BEGIN

INSERT INTO EmpAudit

( EmpID, Ename, JobID, Mgr, HireDate, Sal, Comm, DeptNo, LocID, EventType, EventDateTime )

SELECT
i.EmpID,
i.Ename,
i.JobID,
i.Mgr,
i.HireDate,
i.Sal,
i.Comm,
i.DeptNo,
i.LocID,
‘Inserted‘,
GETDATE()

FROM inserted i

END

ELSE IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)

BEGIN

INSERT INTO EmpAudit

( EmpID, Ename, JobID, Mgr, HireDate, Sal, Comm, DeptNo, LocID, EventType, EventDateTime )

SELECT 
d.EmpID,
d.Ename,
d.JobID,
d.Mgr,
d.HireDate,
d.Sal,
d.Comm,
d.DeptNo,
d.LocID,
‘Deleted‘,
GETDATE()

FROM deleted d;

END

END

If you insert a row, the row is copied to the audit table, and the event-type is set to “inserted,” and if you update a row in the Emp table, the row is inserted into the audit table and the audit-type is set to “updated.” Similarly, if you delete an entry, a row will be added to the audit table and the audit-type is tagged ‘deleted’.

Hope you find this article helpful.