SQL Server Triggers : Made Fun to Learn!

If you want SQL Server to do something automatically whenever data changes, you need Triggers.

Think of a trigger like a security guard for your database:

• It’s attached to a table.
• Watches for INSERT, UPDATE, DELETE.
• When something happens, it runs your code instantly.

---

What is a Trigger?

A trigger is SQL code that runs by itself when data changes in a table.

Remember:

• You don’t run it manually.
• It must be linked to a single table.

---

Types of Triggers

Type	When it Runs	Example Use
AFTER Trigger	After the action finishes	Audit logs, checks
INSTEAD OF Trigger	Replaces the action	For views or blocking
DDL Trigger	When schema changes	Stop unwanted changes
Logon Trigger	On user login	Security checks

---

Basic Syntax Structure for trigger:

CREATE TRIGGER trigger_name
ON table_name
{AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
AS
BEGIN
    -- Your SQL statements here
END;

CREATE TRIGGER trigger_name
ON table_name
FOR {INSERT | UPDATE | DELETE}
AS
BEGIN
    -- Your SQL statements here
END;

• trigger_name: Name you give to the trigger
• table_name: Table the trigger is attached to
• AFTER runs after the event
• INSTEAD OF runs instead of the event
• FOR runs after the event (same as AFTER)
• You can specify one or more events: INSERT, UPDATE, DELETE

---

Magic/Virtual Tables – INSERTED & DELETED

When a trigger runs, SQL Server gives two temporary tables:

Table	What it has
INSERTED	The new rows added or updated
DELETED	The old rows deleted or updated

How they work:

• When INSERT happens → INSERTED has that inserted data, DELETED is then empty.
• When DELETE happens → DELETED has that deleted data, INSERTED is then empty.
• When UPDATE happens → Both have data. [Update = First Delete + Then Insert]

Example for UPDATE:

Before: Price = 99.99 → After:  Price = 109.99
DELETED.Price = 99.99 → INSERTED.Price = 109.99  

---

Step 1 – Create Products Table

CREATE TABLE Products
(
    ProductId INT PRIMARY KEY IDENTITY(1,1),
    ProductName VARCHAR(200) NOT NULL,
    BrandName VARCHAR(200) NOT NULL,
    ReceiveDate DATE NULL,
    AvailableStock FLOAT NOT NULL,
    Price MONEY NOT NULL,
    CreateDate DATETIME NOT NULL DEFAULT GETDATE(),
    ModifyDate DATETIME NOT NULL DEFAULT GETDATE()
);

INSERT INTO Products (ProductName, BrandName, ReceiveDate, AvailableStock, Price)
VALUES 
('Logitech MX Master 3', 'Logitech', '2022-07-18', 100, 99.99),
('Apple Magic Keyboard', 'Apple', '2022-03-28', 200, 199.99);

---

Step 2 – Create Audit Table

CREATE TABLE AuditRecord
(
    RecordId INT PRIMARY KEY IDENTITY(1,1),
    ActionName VARCHAR(50) NOT NULL,
    TableName VARCHAR(50) NOT NULL,
    ColumnName VARCHAR(50) NULL,
    PreviousValue VARCHAR(500) NULL,
    ModifiedValue VARCHAR(500) NULL,
    PK INT NULL,
    CreateDate DATETIME NOT NULL DEFAULT GETDATE()
);

---

AFTER INSERT Trigger – Log New Products

CREATE TRIGGER TR_Products_Insert
ON Products
AFTER INSERT
AS
BEGIN
    INSERT INTO AuditRecord (ActionName, TableName)
    VALUES ('Insert', 'Products');
    PRINT 'Insert Trigger Fired!';
END;

Meaning:
If you run:

INSERT INTO Products (ProductName, BrandName, ReceiveDate, AvailableStock, Price)
VALUES ('Dell Wireless Mouse', 'Dell', '2023-06-15', 50, 29.99);

The trigger will add this to AuditRecord:

ActionName	TableName
Insert	Products

---

Show What’s in INSERTED & DELETED

ALTER TRIGGER TR_Products_Insert
ON Products
FOR INSERT
AS
BEGIN
    SELECT * FROM INSERTED; -- New data
    SELECT * FROM DELETED; -- Empty for INSERT
END;

Example after insert:
INSERTED → new product row
DELETED → empty

ProductId	ProductName	BrandName	ReceiveDate	AvailableStock	Price	CreateDate	ModifyDate
3	Dell Wireless Mouse	Dell	2023-06-15	50	29.99	2025-08-10 14:32:00	2025-08-10 14:32:00

ProductId	ProductName	BrandName	ReceiveDate	AvailableStock	Price	CreateDate	ModifyDate
(empty)	(empty)	(empty)	(empty)	(empty)	(empty)	(empty)	(empty)

---

AFTER UPDATE Trigger – Log Price Changes

*Let's keep track of value of a column when data updates. Keep both Previus + New *

ALTER TRIGGER TR_Products_Update
ON Products
AFTER UPDATE
AS
BEGIN
    INSERT INTO AuditRecord (ActionName, TableName, PK, ColumnName, PreviousValue, ModifiedValue)
    SELECT 'Update', 'Products', i.ProductId, 'Price', d.Price, i.Price
    FROM INSERTED i
    JOIN DELETED d ON i.ProductId = d.ProductId
    WHERE i.Price <> d.Price;
    PRINT 'Update Trigger Fired!';
END;

Meaning (with example):
If you run:

UPDATE Products
SET Price = 109.99
WHERE ProductId = 1;

Before: Price was 99.99 (in DELETED)
After: Price is 109.99 (in INSERTED)

Audit table will show:

ActionName	TableName	PK	ColumnName	PreviousValue	ModifiedValue
Update	Products	1	Price	99.99	109.99

---

AFTER DELETE Trigger – Log Deleted Products

CREATE TRIGGER TR_Products_Delete
ON Products
AFTER DELETE
AS
BEGIN
    INSERT INTO AuditRecord (ActionName, TableName, PK)
    SELECT 'Delete', 'Products', d.ProductId
    FROM DELETED d;
    PRINT 'Delete Trigger Fired!';
END;

Meaning (with example):
If you run:

DELETE FROM Products
WHERE ProductId = 2;

Audit table will show:

ActionName	TableName	PK
Delete	Products	2

Drop a Trigger

DROP TRIGGER TR_Products_Insert;

N.B: for Triggers

• Keep them short.
• Avoid heavy work inside.

Happy coding!
Find me on LinkedIn
Inspired from AurpanDash Bhai.