Triggers in SQL Server

01 · WHAT IS A TRIGGER?

What Is a Trigger?

Without a Trigger vs. With a Trigger

Example: "don't allow a product's quantity to go below 0." When a user runs UPDATE SANPHAM SET SOLUONG = -10 WHERE MASP = 'SP01';, the Trigger detects SOLUONG < 0 ➜ raises an error ➜ ROLLBACK ➜ cancels the operation.

Why Do We Need Triggers?

Triggers are typically used to automatically enforce rules right at the database layer:

• Data validation: Product quantity ≥ 0
• Preventing invalid operations: Don't allow deleting a customer who has transactions
• Automatic updates: Adding an invoice line item ➜ automatically update the invoice total
• Change tracking: Old customer name ➜ new name
• Data synchronization: Invoice line changes ➜ update invoice total ➜ update customer's total spend

02 · TYPES OF TRIGGER

AFTER and INSTEAD OF

SQL Server supports two main types of Trigger. SQL Server does not support BEFORE TRIGGER the way some other database systems do.

AFTER - runs after the DML operation completes successfully (AFTER INSERT, AFTER UPDATE, AFTER DELETE). INSTEAD OF - replaces the original DML operation with the statements defined inside the Trigger.

03 · TRIGGER SYNTAX

CREATE TRIGGER Syntax

CREATE TRIGGER <Trigger_Name>
ON <Table_Name>
AFTER | INSTEAD OF INSERT, UPDATE, DELETE
AS
BEGIN
    -- Statements to run
END;

FOR can also be used in place of AFTER.

Breaking Down Each Part

CREATE TRIGGER trg_AfterInsert_KhachHang
ON KHACHHANG
AFTER INSERT
AS
BEGIN
    -- processing
END;

CREATE TRIGGER creates a new Trigger. The name should be descriptive, e.g. trg_AfterInsert_KhachHang, trg_CheckQuantity_SanPham, trg_PreventDelete_NhanVien. ON KHACHHANG attaches the Trigger to that table. AFTER INSERT runs after an INSERT occurs. BEGIN...END contains the statements to execute.

Example - AFTER INSERT

When a new customer is added, display a message:

CREATE TRIGGER trg_AfterInsert_KhachHang
ON KHACHHANG
AFTER INSERT
AS
BEGIN
    PRINT N'Khách hàng mới đã được thêm vào KHACHHANG.';
END;

Test it:

INSERT INTO KHACHHANG
VALUES (
    'KH99',
    N'Nguyễn Văn A',
    N'Hà Nội',
    '0909000000',
    '2026-08-11',
    0
);

Example - INSTEAD OF DELETE

Prevent customers from being deleted:

CREATE TRIGGER trg_PreventDelete_KhachHang
ON KHACHHANG
INSTEAD OF DELETE
AS
BEGIN
    PRINT N'Không được xóa khách hàng.';
END;

When running DELETE FROM KHACHHANG WHERE MAKH = 'KH01';, instead of deleting, the system just prints "Customers cannot be deleted."

04 · ERROR CHECKING AND HANDLING

A Data-Validating Trigger

Problem: the quantity in SANPHAM must not be less than 0.

CREATE TRIGGER trg_CheckQuantity_SanPham
ON SANPHAM
AFTER INSERT, UPDATE
AS
BEGIN

    IF EXISTS (
        SELECT *
        FROM SANPHAM
        WHERE SOLUONG < 0
    )
    BEGIN
        RAISERROR (
            N'Số lượng không thể âm.',
            16,
            1
        );

        ROLLBACK;
        RETURN;
    END

END;

Breaking It Down

IF EXISTS(...) checks whether a record violating the condition exists. RAISERROR(N'...', 16, 1) raises an error - severity level 16 is the level typically used for user-caused errors (bad input, rule violations). ROLLBACK cancels the current transaction, returning the data to its previous state.

PRINT vs. RAISERROR

05 · INSERTED AND DELETED

INSERTED and DELETED

Declaring Local Variables (@)

DECLARE @VariableName DataType;

Declare several variables at once, separated by commas:

DECLARE @MaKH CHAR(4), @NgayDK SMALLDATETIME;

A newly declared variable starts out as NULL. There are 2 ways to assign a value:

DECLARE @MaKH CHAR(4);

SELECT @MaKH = MAKH
FROM inserted;

PRINT N'Customer just added: ' + @MaKH;

Try It Yourself

Click a button to see what INSERTED/DELETED contain for each operation (example: renaming a customer from "Nguyễn Văn A" to "Nguyễn Văn B"):

INSERT INTO KHACHHANG (MAKH, HOTEN)
VALUES ('KH01', N'Nguyễn Văn A');

Example - Reading INSERTED

CREATE TRIGGER trg_AfterInsert_KhachHang
ON KHACHHANG
AFTER INSERT
AS
BEGIN
    DECLARE @MAKH CHAR(4);

    SELECT @MAKH = MAKH
    FROM INSERTED;

    PRINT N'Khách hàng mới được thêm vào với MAKH: '
        + CAST(@MAKH AS VARCHAR);
END;

Example - Reading DELETED

CREATE TRIGGER trg_AfterDelete_KhachHang
ON KHACHHANG
AFTER DELETE
AS
BEGIN
    SELECT *
    FROM DELETED;
END;

Full Example - Tracking a Name Change (UPDATE)

On UPDATE, DELETED holds the old value and INSERTED holds the new value, so you can compare "old value ➜ new value":

CREATE TRIGGER trg_AfterUpdate_KhachHang
ON KHACHHANG
AFTER UPDATE
AS
BEGIN

    DECLARE
        @OldName VARCHAR(40),
        @NewName VARCHAR(40);

    SELECT @OldName = HOTEN
    FROM DELETED;

    SELECT @NewName = HOTEN
    FROM INSERTED;

    PRINT N'Tên khách hàng đã thay đổi từ "'
        + @OldName
        + N'" sang "'
        + @NewName
        + N'".';

END;

06 · REAL-WORLD USES

Real-World Applications of Triggers

Checking Existence Before Insert

Don't allow adding an invoice if the customer doesn't exist:

IF EXISTS checks whether data matching a condition exists; IF NOT EXISTS checks the opposite - that no matching data exists.

A Trigger That Auto-Updates Data

Tables HOADON(MAHD, TRIGIA) and CTHD(MAHD, MASP, SOLUONG, DONGIA). When INSERT INTO CTHD happens, the Trigger recalculates TRIGIA and runs UPDATE HOADON.

A Trigger That Updates Customer Sales Totals

A more advanced problem: adding/editing an invoice ➜ the Trigger computes the total value ➜ updates DOANHSO in KHACHHANG.

Preventing Deletion of Referenced Data

Don't allow deleting an employee if they've already made sales:

Automatically Setting the Registration Date

When adding a new customer, if NGDK isn't provided, the system automatically uses the current date via GETDATE().

Some Extended Problems

• A product can only be added to an invoice if it exists and has enough stock
• The total value in CTHD must match HOADON
• Invoices over 1,000,000,000 must switch to "Special Review" status
• The total quantity of products sold in a single day must not exceed 10,000
• An employee cannot become their own manager

07 · TRIGGERS THAT HANDLE MULTIPLE ROWS

Triggers Should Think in Terms of Sets

Students often write:

DECLARE @MAKH CHAR(4);

SELECT @MAKH = MAKH
FROM INSERTED;

This is easy to understand when illustrating a single record, but a single INSERT INTO ... SELECT ... statement can insert many rows at once - and as section 05 already pointed out, SELECT @x = column FROM inserted raises no error at all with multiple rows, it just silently drops the rest.

Local Variables vs. IF EXISTS/JOIN

These are 2 common ways to write the same data-validation problem in a Trigger:

-- Local variable - only safe with exactly 1 row
DECLARE @SoLuong INT;
SELECT @SoLuong = SOLUONG FROM inserted;
IF (@SoLuong < 0)
BEGIN
    RAISERROR (N'Quantity cannot be negative.', 16, 1);
    ROLLBACK TRANSACTION;
    RETURN;
END

-- IF EXISTS - correct for any number of rows
IF EXISTS (SELECT * FROM inserted WHERE SOLUONG < 0)
BEGIN
    RAISERROR (N'Quantity cannot be negative.', 16, 1);
    ROLLBACK TRANSACTION;
    RETURN;
END

In the BTTH5 exercises, most questions should present IF EXISTS/NOT EXISTS as the main answer - the local-variable approach is still kept in the answer key as an "Alternative approach" for reference, with a clear note on its limitation: it's only correct when the operation affects exactly 1 row.

08 · BUILDING A TRIGGER, STEP BY STEP

The 6-Step Process

When facing a Trigger exercise, follow these 6 steps:

Managing Triggers

Dropping a Trigger:

DROP TRIGGER trg_AfterInsert_KhachHang;

Viewing Trigger information:

SELECT *
FROM INFORMATION_SCHEMA.TRIGGERS;

Week 5 Cheat Sheet

The First Five Weeks of IT004

Week 1: creating a database. Week 2: querying. Week 3: advanced querying. Week 4: processing data (CAST/CASE/Subquery). Week 5: automation & control with Triggers, INSERTED/DELETED, RAISERROR/ROLLBACK. Per the course outline, Week 6 will be review and a practical exam - the timing and format of the exam will be announced later.

09 · PRACTICE EXERCISES

Comprehensive Practice

Extra Trigger problems to practice with - not part of the BTTH5 submission. Each has a hidden hint - click to reveal.