Different Operations with Triggers

Triggers are special database operations that automatically execute in response to specific events on a table, such as INSERT, UPDATE, or DELETE. They are useful for enforcing business rules, auditing changes, and automating tasks.

1. Creating a Trigger

General Syntax:

CREATE TRIGGER trigger_name
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON table_name
FOR EACH ROW
BEGIN
  -- Trigger logic here
END;

Example – AFTER INSERT Trigger:

This trigger logs every new user added to the users table.

CREATE TABLE user_logs (
  log_id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  action_time DATETIME
);

CREATE TRIGGER log_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_logs(user_id, action_time)
  VALUES (NEW.id, NOW());
END;

2. BEFORE Triggers:

Used to validate or modify data before it is written to the table.

Example:

CREATE TRIGGER check_name
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
  IF NEW.name IS NULL THEN
    SET NEW.name = 'Unknown';
  END IF;
END;

3. AFTER Triggers

Used to perform actions after data is inserted, updated, or deleted, such as logging, notifications, or syncing.

Example use cases:

  • Logging changes

  • Sending alerts

  • Updating audit tables

4. OLD and NEW Keywords

  • NEW.column_name: Refers to the new value being inserted or updated.
  • OLD.column_name: Refers to the existing value before update or deletion.

Example – Audit price changes:

CREATE TRIGGER audit_price_change
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  INSERT INTO price_logs(product_id, old_price, new_price, changed_on)
  VALUES (OLD.id, OLD.price, NEW.price, NOW());
END;

5. Dropping a Trigger:

To remove an existing trigger

DROP TRIGGER trigger_name;

6. Viewing Existing Triggers:

To list all triggers in the current database

SHOW TRIGGERS;
PreviousNext