Learn BEFORE, AFTER, and Validation Triggers with Examples - Textnotes

Learn BEFORE, AFTER, and Validation Triggers with Examples

Master SQL triggers in this beginner-friendly guide. Learn about BEFORE INSERT, AFTER UPDATE, and BEFORE DELETE triggers. Practice logging updates in employee tables and preventing salary decreases using SQL triggers for automation and auditing.

1. Introduction

A trigger is a special kind of stored procedure that automatically executes in response to certain events on a table.

  1. Triggers are used for automation, validation, and auditing.
  2. They help maintain data integrity without manual intervention.

Key Points:

  1. Trigger executes before or after insert, update, or delete operations.
  2. Useful for audit trails, automatic updates, and business rules enforcement.

2. Types of Triggers

Trigger TypeDescription
BEFORE INSERTExecutes before a row is inserted; used for validation or default values.
AFTER UPDATEExecutes after a row is updated; used for logging or cascading updates.
BEFORE DELETEExecutes before a row is deleted; used to prevent deletions or log activity.

3. Creating Triggers

3.1 Trigger to Log Updates

Example:


-- Create audit table
CREATE TABLE EmployeeAudit (
AuditID INT IDENTITY(1,1),
EmployeeID INT,
OldSalary DECIMAL(10,2),
NewSalary DECIMAL(10,2),
UpdateDate DATETIME DEFAULT GETDATE()
);

-- Trigger for logging salary updates
CREATE TRIGGER trg_LogSalaryUpdate
ON Employees
AFTER UPDATE
AS
BEGIN
INSERT INTO EmployeeAudit(EmployeeID, OldSalary, NewSalary)
SELECT i.ID, d.Salary, i.Salary
FROM inserted i
INNER JOIN deleted d ON i.ID = d.ID
WHERE i.Salary <> d.Salary;
END;

3.2 Trigger to Prevent Salary Decrease

Example:


CREATE TRIGGER trg_PreventSalaryDecrease
ON Employees
BEFORE UPDATE
AS
BEGIN
IF EXISTS (
SELECT 1
FROM inserted i
INNER JOIN deleted d ON i.ID = d.ID
WHERE i.Salary < d.Salary
)
BEGIN
RAISERROR('Salary decrease is not allowed!', 16, 1);
ROLLBACK TRANSACTION;
END
END;

Note:

  1. inserted table contains new values.
  2. deleted table contains old values.

4. Practical Exercises

  1. Create a trigger to log updates in the Employees table (audit trail).
  2. Create a trigger to prevent salary decreases.
  3. Create a trigger to automatically set default values before inserting a row.
  4. Create a trigger to update a last_modified column after a record update.
  5. Test triggers by inserting, updating, and deleting rows in Employees table.

5. Tips for Beginners

  1. Use triggers for critical validations and auditing, not for all operations.
  2. Avoid complex logic inside triggers as it can slow down transactions.
  3. Always test triggers thoroughly to prevent unintended rollbacks.
  4. Use BEFORE triggers for validation and AFTER triggers for logging or cascading actions.


Next Step: After mastering triggers, the next module is Transactions in SQL, where you’ll learn to use BEGIN, COMMIT, ROLLBACK, and savepoints to manage data integrity.