SQL Triggers

🔹 What is a Trigger?

A trigger is a stored program that automatically runs when specific events occur on a table—typically INSERT, UPDATE, or DELETE. Think of it as a built-in event listener inside your database that helps automate responses to data changes.

🔹 1. Creating a Trigger

CREATE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
   SET NEW.created_at = NOW();
END;

This trigger fires before a new employee record is inserted and automatically stamps the row with the current timestamp.

🔹 2. AFTER Trigger Example

CREATE TRIGGER after_update_salary
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
   INSERT INTO salary_audit(emp_id, old_salary, new_salary, changed_at)
   VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
END;

Whenever an employee’s salary changes, this AFTER UPDATE trigger writes an entry to an audit table so you always have a history of changes.

🔹 3. Dropping a Trigger

DROP TRIGGER before_insert_employee;

When a trigger is no longer needed—or is causing unexpected behavior—you can drop it to remove the automatic logic.

🔹 Advantages

• Automates auditing and logging without application changes.
• Enforces business rules such as preventing invalid data.
• Improves data integrity by reacting instantly to table events.

🔹 Disadvantages

• Logic can be hidden in the database, making debugging harder.
• Too many triggers may impact performance on busy tables.
• Complex trigger chains can introduce unexpected side effects.