Triggers

A Trigger in MySQL is a database object that is automatically executed (“fired”) when a specific event (INSERT, UPDATE, DELETE) occurs on a table.

They’re useful for:

• Enforcing business rules
• Maintaining audit logs
• Automatically updating related tables
• Validating or transforming data before saving

Syntax for Creating a Trigger

CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
BEGIN
   -- SQL statements
END;

• trigger_name → Name of the trigger (must be unique per table + event + timing).
• trigger_time → Either BEFORE or AFTER (when the trigger runs relative to the event).
• trigger_event → The event that activates the trigger:
  • INSERT
  • UPDATE
  • DELETE
• table_name → The table on which the trigger is defined.
• FOR EACH ROW → Trigger executes once for every affected row.
• Inside the body, you can use:
  • NEW.column_name → Refers to new values (for INSERT/UPDATE)
  • OLD.column_name → Refers to existing values (for UPDATE/DELETE)

BEFORE INSERT Trigger (data validation)

Suppose we want to prevent inserting employees with negative salaries:

DELIMITER //

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
   IF NEW.salary < 0 THEN
      SET NEW.salary = 0;  -- fix invalid salary
   END IF;
END //

DELIMITER ;

• Runs before an INSERT on employees.
• If someone tries to insert a salary < 0, it automatically changes it to 0.

AFTER INSERT Trigger (audit logging)

Suppose we want to log every new employee added into an employee_audit table:

CREATE TABLE employee_audit (
   audit_id INT AUTO_INCREMENT PRIMARY KEY,
   employee_id INT,
   action VARCHAR(50),
   action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Now create the trigger:

DELIMITER //

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
   INSERT INTO employee_audit (employee_id, action)
   VALUES (NEW.employee_id, 'INSERT');
END //

DELIMITER ;

• Runs after inserting a row into employees.
• Writes a record in employee_audit.

Managing Triggers

• Show triggers in a database: SHOW TRIGGERS
• Drop a trigger: DROP TRIGGER after_employee_insert;
• Check trigger definition: SHOW CREATE TRIGGER before_employee_update

Common Use Cases for Triggers

1. Audit Logging (Tracking Changes): Keep a record of all changes (INSERT, UPDATE, DELETE) to critical tables (like employees, accounts, orders).
2. Data Validation and Integrity: Prevent invalid data from being inserted/updated (business rules enforcement).
3. Cascading Actions (Automatic Updates/Deletes): Maintain consistency between related tables.
4. Derived/Computed Values: Automatically calculate or update derived fields.
5. Enforcing Security Rules: Restrict certain operations automatically.
6. Synchronizing Tables (Replication-like Behavior): Automatically keep a summary table in sync.

Summary of Use Cases of Triggers

Use Case	Trigger Type	Example
Audit logging	AFTER INSERT/UPDATE/DELETE	Track who changed what & when
Validation	BEFORE INSERT/UPDATE	Prevent invalid salaries
Cascading actions	AFTER DELETE	Archive deleted rows
Computed values	BEFORE INSERT/UPDATE	Auto-calc total price
Security enforcement	BEFORE DELETE	Prevent deleting managers
Synchronization	AFTER INSERT/UPDATE	Maintain summary table