What is a trigger and what are its types?

Question

Accepted Answer

69
   What is a trigger and what are its types?
  Level: Advanced — Triggers automate actions in response to data changes; common in auditing scenarios.

  A trigger is a special stored program that automatically executes ("fires") in response to a specific event on a table — namely an INSERT, UPDATE, or DELETE. You don't call a trigger; the database invokes it automatically.

  
     Timing × Event = 6 trigger types: Each event (INSERT/UPDATE/DELETE) can fire either BEFORE or AFTER it happens, giving 6 combinations. MySQL triggers are always row-level (fire once per affected row).
  

   The Six Trigger Types
  
    
      
        
          Timing
          INSERT
          UPDATE
          DELETE
        
      
      
        
          BEFORE
          BEFORE INSERT
          BEFORE UPDATE
          BEFORE DELETE
        
        
          AFTER
          AFTER INSERT
          AFTER UPDATE
          AFTER DELETE
        
      
    
  

   The NEW and OLD Keywords
  
     NEW: Refers to the new row values (available in INSERT and UPDATE triggers).
     OLD: Refers to the existing row values (available in UPDATE and DELETE triggers).
  

   Common Uses of Triggers
  
     Auditing: Log every change to a history table.
     Validation: Enforce complex business rules before a change.
     Derived values: Auto-update totals or timestamps.
     Cascading actions: Keep related tables in sync.
  

   Quick Example — Audit Trigger
-- BEFORE INSERT: auto-format data before it is stored
DELIMITER $$
CREATE TRIGGER before_emp_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    SET NEW.email = LOWER(NEW.email);   -- standardize email
END $$
DELIMITER ;

-- AFTER UPDATE: log salary changes to an audit table
DELIMITER $$
CREATE TRIGGER after_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, changed_at)
        VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW());
    END IF;
END $$
DELIMITER ;

-- View triggers on the database
SHOW TRIGGERS;

  
     Caution: Triggers run invisibly, which can make debugging harder and add overhead to writes. Use them judiciously, and avoid heavy logic that slows down every INSERT/UPDATE/DELETE.
  

  
     Interviewer tip: The one-liner they want — "A trigger automatically fires BEFORE or AFTER an INSERT, UPDATE, or DELETE — giving 6 types. It uses NEW and OLD to access row values and is commonly used for auditing and validation."

Timing	INSERT	UPDATE	DELETE
BEFORE	BEFORE INSERT	BEFORE UPDATE	BEFORE DELETE
AFTER	AFTER INSERT	AFTER UPDATE	AFTER DELETE

What is a trigger and what are its types?

Answer with Explanation

What is a trigger and what are its types?

The Six Trigger Types

The NEW and OLD Keywords

Common Uses of Triggers

Quick Example — Audit Trigger