Prev Next

MySQL Triggers

A Trigger in MySQL is a set of statements that are automatically executed in response to certain events on a particular table. Triggers can be activated before or after an INSERT, UPDATE, or DELETE operation.

Tables Setup for Triggers

To understand how triggers work, let's first create the tamil_kings table and insert some data.

Creating the tamil_kings Table

CREATE TABLE tamil_kings (
    king_id INT PRIMARY KEY,
    king_name VARCHAR(100),
    reign_start YEAR,
    reign_end YEAR,
    kingdom_id INT
);

Inserting Data into tamil_kings

INSERT INTO tamil_kings (king_id, king_name, reign_start, reign_end, kingdom_id) VALUES
(1, 'RAJA RAJA CHOLA', 985, 1014, 1),
(2, 'RAJENDRA CHOLA', 1014, 1044, 1),
(3, 'VIJAYALAYA CHOLA', 850, 871, 2);

Key Topics

1. Creating Triggers

To create a trigger, you use the CREATE TRIGGER statement. Specify the event (INSERT, UPDATE, DELETE) and whether the trigger should fire BEFORE or AFTER the event.

Syntax:

CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name
FOR EACH ROW
BEGIN
    -- SQL statements
END;

2. Examples

Example 1: Trigger BEFORE INSERT

This trigger checks if a king's name is being inserted in all uppercase and converts it to title case before inserting the record.

CREATE TRIGGER before_king_insert
BEFORE INSERT ON tamil_kings
FOR EACH ROW
BEGIN
    SET NEW.king_name = CONCAT(UPPER(SUBSTRING(NEW.king_name, 1, 1)), LOWER(SUBSTRING(NEW.king_name, 2)));
END;

Code Explanation: This trigger modifies the king_name to have the first letter in uppercase and the rest in lowercase before the new record is inserted into the tamil_kings table.

Example 2: Trigger AFTER UPDATE

This trigger logs changes to the reign_start of a king in a separate table called king_updates_log.

Creating the king_updates_log Table

CREATE TABLE king_updates_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    king_id INT,
    old_reign_start YEAR,
    new_reign_start YEAR,
    change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Creating the AFTER UPDATE Trigger

CREATE TRIGGER after_king_update
AFTER UPDATE ON tamil_kings
FOR EACH ROW
BEGIN
    IF OLD.reign_start != NEW.reign_start THEN
        INSERT INTO king_updates_log (king_id, old_reign_start, new_reign_start)
        VALUES (NEW.king_id, OLD.reign_start, NEW.reign_start);
    END IF;
END;

Code Explanation: This trigger logs any changes made to the reign_start field in the tamil_kings table, recording the old and new values along with the change date.

Best Practices

  • Use triggers sparingly, as they can complicate debugging and affect performance.
  • Ensure triggers are efficient and do not introduce significant overhead.
  • Document the purpose and behavior of each trigger in your database schema.

Key Takeaways

  • Triggers automatically execute in response to specified events on a table.
  • They can be used for data validation, logging, and enforcing business rules.
  • Be cautious of performance implications when using triggers.
Prev Next