What is a trigger in MySQL?

A trigger in MySQL is a set of instructions that are automatically executed (or "triggered") in response to certain events on a particular table. These events can include actions such as inserting, updating, or deleting rows in the table.

Triggers are useful for enforcing business rules, maintaining data integrity, and automating tasks. They can be defined to execute either before or after the specified event occurs.

Syntax for Creating a Trigger

Here’s a basic example of how to create a trigger in MySQL:

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

Example

Here’s an example of a trigger that logs changes to a users table:

CREATE TRIGGER log_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_log (user_id, old_value, new_value, change_time)
    VALUES (OLD.id, OLD.name, NEW.name, NOW());
END;

In this example, whenever a row in the users table is updated, a new entry is added to the user_log table, capturing the old and new values along with the timestamp of the change.