SQL Triggers

A trigger is a special type of stored procedure that automatically executes when a specific event occurs on a table — such as inserting, updating, or deleting a row. You do not call a trigger manually; it fires on its own whenever the defined event takes place.

Think of a trigger like a motion sensor light. You do not manually turn it on — it activates automatically whenever motion is detected. Similarly, a trigger activates automatically whenever a specified database event happens.

Key Differences Between MySQL and MS SQL Server

AuditLog Table Setup

The following examples log changes to an AuditLog table when data in the Students table is inserted, updated, or deleted.

MySQL Query

CREATE TABLE AuditLog (
    LogID     INT AUTO_INCREMENT PRIMARY KEY,
    TableName VARCHAR(50),
    Action    VARCHAR(10),
    StudentID INT,
    OldFees   DECIMAL(8,2),
    NewFees   DECIMAL(8,2),
    ChangedAt DATETIME
);

MS SQL Server Query

CREATE TABLE AuditLog (
    LogID     INT          IDENTITY(1,1) PRIMARY KEY,
    TableName NVARCHAR(50),
    Action    NVARCHAR(10),
    StudentID INT,
    OldFees   DECIMAL(8,2),
    NewFees   DECIMAL(8,2),
    ChangedAt DATETIME2
);

AFTER INSERT Trigger

Fires automatically after a new row is inserted into the Students table.

MySQL Query

DELIMITER //
CREATE TRIGGER trg_after_student_insert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
    INSERT INTO AuditLog (TableName, Action, StudentID, NewFees, ChangedAt)
    VALUES ('Students', 'INSERT', NEW.StudentID, NEW.Fees, NOW());
END //
DELIMITER ;

MS SQL Server Query

-- No DELIMITER, no FOR EACH ROW.
-- INSERTED virtual table replaces NEW.
CREATE TRIGGER trg_after_student_insert
ON Students
AFTER INSERT
AS
BEGIN
    INSERT INTO AuditLog (TableName, Action, StudentID, NewFees, ChangedAt)
    SELECT 'Students', 'INSERT', StudentID, Fees, GETDATE()
    FROM INSERTED;
END
GO

AFTER UPDATE Trigger

Fires after a row is updated. Records both the old and new fee values.

MySQL Query

DELIMITER //
CREATE TRIGGER trg_after_fees_update
AFTER UPDATE ON Students
FOR EACH ROW
BEGIN
    IF OLD.Fees != NEW.Fees THEN
        INSERT INTO AuditLog (TableName, Action, StudentID, OldFees, NewFees, ChangedAt)
        VALUES ('Students', 'UPDATE', NEW.StudentID, OLD.Fees, NEW.Fees, NOW());
    END IF;
END //
DELIMITER ;

MS SQL Server Query

-- INSERTED holds the new values; DELETED holds the old values.
CREATE TRIGGER trg_after_fees_update
ON Students
AFTER UPDATE
AS
BEGIN
    INSERT INTO AuditLog (TableName, Action, StudentID, OldFees, NewFees, ChangedAt)
    SELECT 'Students', 'UPDATE',
           i.StudentID,
           d.Fees,        -- old fee from DELETED
           i.Fees,        -- new fee from INSERTED
           GETDATE()
    FROM INSERTED i
    JOIN DELETED d ON i.StudentID = d.StudentID
    WHERE i.Fees <> d.Fees;
END
GO

BEFORE INSERT Trigger — Validate Data Before Saving

MySQL supports BEFORE INSERT triggers on tables. MS SQL Server does not — use INSTEAD OF INSERT to achieve the same result.

MySQL Query

DELIMITER //
CREATE TRIGGER trg_before_student_insert
BEFORE INSERT ON Students
FOR EACH ROW
BEGIN
    -- Ensure fees are never below 10000
    IF NEW.Fees < 10000 THEN
        SET NEW.Fees = 10000;
    END IF;
    -- Store name in uppercase
    SET NEW.StudentName = UPPER(NEW.StudentName);
END //
DELIMITER ;

MS SQL Server Query

-- BEFORE INSERT does not exist on tables in MS SQL Server.
-- Use INSTEAD OF INSERT to intercept and modify data before saving.
CREATE TRIGGER trg_instead_of_student_insert
ON Students
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO Students (StudentName, Fees)
    SELECT
        UPPER(StudentName),
        CASE WHEN Fees < 10000 THEN 10000 ELSE Fees END
    FROM INSERTED;
END
GO

AFTER DELETE Trigger

Fires after a row is deleted. Logs the details of the removed record.

MySQL Query

DELIMITER //
CREATE TRIGGER trg_after_student_delete
AFTER DELETE ON Students
FOR EACH ROW
BEGIN
    INSERT INTO AuditLog (TableName, Action, StudentID, OldFees, ChangedAt)
    VALUES ('Students', 'DELETE', OLD.StudentID, OLD.Fees, NOW());
END //
DELIMITER ;

MS SQL Server Query

-- DELETED virtual table holds the values of the removed rows.
CREATE TRIGGER trg_after_student_delete
ON Students
AFTER DELETE
AS
BEGIN
    INSERT INTO AuditLog (TableName, Action, StudentID, OldFees, ChangedAt)
    SELECT 'Students', 'DELETE', StudentID, Fees, GETDATE()
    FROM DELETED;
END
GO

View and Drop Triggers

MySQL Query

-- View all triggers
SHOW TRIGGERS;

-- View triggers for a specific table
SHOW TRIGGERS WHERE `Table` = 'Students';

-- Drop a trigger
DROP TRIGGER IF EXISTS trg_after_student_insert;

MS SQL Server Query

-- View all triggers
SELECT name, type_desc, OBJECT_NAME(parent_id) AS TableName
FROM sys.triggers;

-- Drop a trigger
DROP TRIGGER IF EXISTS trg_after_student_insert;

Key Points

• Triggers fire automatically — they do not need to be called manually.
• MySQL uses NEW.column (new values) and OLD.column (original values). MS SQL Server uses the virtual tables INSERTED (new values) and DELETED (old values).
• MySQL requires DELIMITER and FOR EACH ROW. MS SQL Server requires neither.
• MySQL supports BEFORE INSERT on tables. MS SQL Server does not — use INSTEAD OF INSERT.
• MySQL uses NOW(); MS SQL Server uses GETDATE().