What is a trigger, and how do you create one in MySQL?

What is a Trigger?

Trigger is a specialized type of stored procedure within a Database Management System that automatically executes when specific conditions are met. Specifically, it is defined as a code block that triggers execution automatically during INSERT, UPDATE, or DELETE operations. Triggers are used to ensure data integrity, automatically update or compute values, or for auditing data changes.

Steps to Create Triggers in MySQL

Creating a trigger in MySQL involves the following steps:

1. Determine the trigger timing and event: First, identify whether the trigger fires before (BEFORE) or after (AFTER) data modifications, and on which data operation type (INSERT, UPDATE, DELETE) it triggers.
2. Write the trigger logic: Develop SQL code for the operations to be executed automatically.
3. Use the CREATE TRIGGER statement to define the trigger: The syntax is as follows:

sql
CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name FOR EACH ROW
BEGIN
    -- Trigger logic, which may include multiple SQL statements
END;

Example

Suppose we have an orders table containing the total field for order amounts and a last_updated field to record the last modification time. We want to automatically set last_updated to the current time whenever the order total is updated.

Here is the MySQL statement to create this trigger:

sql
CREATE TRIGGER UpdateLastUpdatedTime
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF NEW.total != OLD.total THEN
        SET NEW.last_updated = NOW();
    END IF;
END;

In this example:

• UpdateLastUpdatedTime is the trigger name.
• AFTER UPDATE ON orders specifies that the trigger activates after data updates on the orders table.
• FOR EACH ROW indicates the trigger operates on each row.
• IF NEW.total != OLD.total THEN checks for changes in the total amount.
• SET NEW.last_updated = NOW(); updates the last_updated field to the current timestamp.

By following these steps, we define a trigger that ensures last_updated is updated whenever the order total changes. This helps track modification times for order data, supporting data integrity maintenance and audit processes.