CREATE TRIGGER - MySQL

CREATE TRIGGER

Overview

CREATE TRIGGER defines a database trigger, a special type of stored procedure that automatically executes when a specific event occurs on a table (e.g., insert, update, delete). Triggers allow for automated data manipulation or enforcement of business rules.

Syntax

CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name
BEFORE | AFTER | INSTEAD OF
{ INSERT | UPDATE | DELETE } ON table_name
FOR EACH ROW
[ trigger_body ]

Options/Flags

  • DEFINER: Specifies the user who will own the trigger (defaults to the current user).
  • BEFORE | AFTER | INSTEAD OF: Determines when the trigger will fire:
    • BEFORE: Executes before the event occurs.
    • AFTER: Executes after the event occurs.
    • INSTEAD OF: Executes instead of the event (prevents the event from occurring).
  • INSERT | UPDATE | DELETE: Specifies the event that will trigger the trigger.
  • FOR EACH ROW: Indicates that the trigger should fire for each row affected by the event.

Examples

Example 1: Trigger to Log Inserts to a Table

CREATE TRIGGER log_inserts
AFTER INSERT ON table_name
FOR EACH ROW
INSERT INTO log_table (timestamp, row_data)
VALUES (NOW(), NEW());

Example 2: Trigger to Prevent Negative Values in a Column

CREATE TRIGGER prevent_negative_values
BEFORE UPDATE ON table_name
FOR EACH ROW
IF NEW.column_name < 0 THEN
  SET NEW.column_name = 0;
END IF;

Common Issues

  • Circular Trigger References: Avoid creating triggers that reference each other, as this can lead to infinite recursion.
  • Trigger Order: Triggers are executed in the order they are defined. Be aware of the order and ensure it aligns with the desired behavior.
  • Performance Impact: Triggers can impact performance if they perform complex operations or call multiple stored procedures. Consider optimizing triggers for efficiency.

Integration

  • Triggers can be combined with other MySQL commands to create complex data management systems.
  • Triggers can be used in conjunction with stored procedures, functions, and views to enhance data manipulation and validation capabilities.

Related Commands

  • DROP TRIGGER: Removes a trigger.
  • SHOW TRIGGERS: Lists defined triggers.

See the MySQL documentation for more details: https://dev.mysql.com/doc/refman/8.0/en/create-trigger.html