MySQL Create Trigger

To create a trigger in MySQL, you can use the CREATE TRIGGER statement. The syntax for creating a trigger is as follows:

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

Let’s break down the components of this syntax:

• trigger_name: Choose a unique name for your trigger.
• BEFORE or AFTER: Specifies whether the trigger should execute before or after the associated event (e.g., BEFORE INSERT, AFTER UPDATE).
• INSERT, UPDATE, or DELETE: Specifies the SQL operation that triggers the action. You can choose one or more of these operations for your trigger.
• table_name: The name of the table on which the trigger is defined.
• FOR EACH ROW: Indicates that the trigger will execute once for each row affected by the event.
• BEGIN and END: These enclose the trigger logic, which consists of one or more SQL statements. You should define the specific actions you want the trigger to perform within this block.

Here’s an example of creating a simple trigger that logs the changes to a table called products in an audit table named products_audit whenever a new row is inserted:

DELIMITER //
CREATE TRIGGER log_product_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO products_audit (product_id, action, action_time)
    VALUES (NEW.product_id, 'INSERT', NOW());
END;
//
DELIMITER ;

In this example:

• The trigger name is log_product_insert.
• It is an AFTER INSERT trigger, so it executes after an INSERT operation on the products table.
• The trigger logs the product_id and the action as ‘INSERT’ along with the current timestamp into the products_audit table.

After creating the trigger, it will automatically execute each time a new row is inserted into the products table.

Remember to be careful when using triggers, as they can have a significant impact on database performance and behavior. Always thoroughly test your triggers and consider their implications before deploying them in a production environment.