Triggers

So far, we have executed SQL logic manually using queries, procedures, and functions.

In this lesson, we will explore Triggers, which allow the database to automatically execute logic when data changes.

What is a Trigger?

A trigger is a block of SQL code that runs automatically in response to an event on a table.

Triggers are event-driven and do not need to be called explicitly.

When Do Triggers Execute?

Triggers execute when one of the following events occurs:

• INSERT – new row added
• UPDATE – existing row modified
• DELETE – row removed

They can run:

• BEFORE the event
• AFTER the event

Why Use Triggers?

Triggers are used to:

• Enforce complex business rules
• Automatically maintain audit logs
• Validate data before changes
• Keep related data in sync

They ensure consistency at the database level.

Basic Trigger Syntax (MySQL)

DELIMITER $$

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
  -- trigger logic here
END $$

DELIMITER ;
  

Triggers run once for each affected row.

NEW and OLD Keywords

Triggers use special keywords to access row data:

• NEW – refers to new values
• OLD – refers to existing values

Usage depends on the trigger type.

Example: BEFORE INSERT Trigger

Ensure salary is never negative.

DELIMITER $$

CREATE TRIGGER check_salary_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < 0 THEN
    SET NEW.salary = 0;
  END IF;
END $$

DELIMITER ;
  

This trigger automatically corrects invalid salary values.

AFTER INSERT Trigger Example

Log employee insert operations.

DELIMITER $$

CREATE TRIGGER log_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO employee_logs(employee_id, action)
  VALUES (NEW.id, 'INSERT');
END $$

DELIMITER ;
  

UPDATE Trigger Example

Track salary changes.

DELIMITER $$

CREATE TRIGGER log_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  IF OLD.salary <> NEW.salary THEN
    INSERT INTO salary_audit(emp_id, old_salary, new_salary)
    VALUES (OLD.id, OLD.salary, NEW.salary);
  END IF;
END $$

DELIMITER ;
  

DELETE Trigger Example

Archive deleted employees.

DELIMITER $$

CREATE TRIGGER archive_employee
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO deleted_employees
  VALUES (OLD.id, OLD.name, OLD.salary);
END $$

DELIMITER ;
  

Triggers vs Constraints

Limitations of Triggers

Triggers:

• Can make debugging harder
• Run invisibly in the background
• May impact performance if overused

Use them carefully and document clearly.

Dropping a Trigger

DROP TRIGGER IF EXISTS check_salary_before_insert;
  

Common Beginner Mistakes

• Overusing triggers for simple logic
• Creating recursive triggers accidentally
• Forgetting FOR EACH ROW
• Not testing triggers thoroughly

When Should You Use Triggers?

Use triggers when:

• Logic must always execute automatically
• Audit logging is required
• Data protection is critical

What’s Next?

In the next lesson, we will explore Cursors, which allow row-by-row processing in SQL.