Why triggers are needed in SQL

Introduction

Triggers help automatically do tasks in the database when data changes, so you don't have to do them by hand every time.

When you want to keep track of changes made to important data, like recording who updated a record.

When you need to check or fix data right after someone adds or changes it, to keep it correct.

When you want to automatically update related data in other tables after a change.

When you want to send alerts or notifications after certain data changes happen.

When you want to enforce rules that are hard to check with normal database constraints.

Syntax

SQL

CREATE TRIGGER trigger_name
AFTER|BEFORE INSERT|UPDATE|DELETE
ON table_name
FOR EACH ROW
BEGIN
  -- SQL statements to run automatically
END;

Triggers run automatically when data changes happen on a table.

You can choose to run them before or after the change.

Examples

This trigger adds a record to an audit log every time an employee's data is updated.

SQL

CREATE TRIGGER log_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(employee_id, changed_at)
  VALUES (NEW.id, CURRENT_TIMESTAMP);
END;

This trigger stops adding a new employee if the salary is less than zero.

SQL

CREATE TRIGGER check_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be negative';
  END IF;
END;

Sample Program

This example creates two tables and a trigger that logs every salary update. After updating Alice's salary, the audit_log table will have one record showing the change.

SQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  salary DECIMAL(10,2)
);

CREATE TABLE audit_log (
  log_id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT,
  changed_at TIMESTAMP
);

CREATE TRIGGER log_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(employee_id, changed_at)
  VALUES (NEW.id, CURRENT_TIMESTAMP);
END;

INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 50000);
UPDATE employees SET salary = 55000 WHERE id = 1;

SELECT * FROM audit_log;

Important Notes

Triggers can slow down data changes if they do a lot of work, so keep them simple.

Not all databases support triggers exactly the same way; check your database's documentation.

Use triggers carefully to avoid unexpected side effects or infinite loops.

Summary

Triggers run automatically when data changes happen in a table.

They help keep data correct, log changes, and enforce rules.

Triggers save you from doing repetitive tasks manually.