Introduction

This section will cover the fundamentals of Triggers in MySQL. Triggers are special stored programs that are automatically executed in response to certain events on a particular table. They are a powerful tool for enforcing data integrity, auditing changes, and performing complex actions automatically.

What are Triggers?

Triggers are essentially database event listeners. They allow you to define actions that will automatically happen *before* or *after* a specified data manipulation event (INSERT, UPDATE, or DELETE) on a particular table. They are used to enforce business rules, validate data, and maintain data consistency.

Basic Trigger Syntax

The fundamental syntax for creating a trigger in MySQL is as follows:

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

• CREATE TRIGGER trigger_name: This statement creates a new trigger with the specified name. Choose a descriptive name that clearly indicates the trigger's purpose.
• {BEFORE | AFTER}: Specifies when the trigger will be executed. BEFORE means the trigger logic executes *before* the event (INSERT, UPDATE, DELETE) occurs, potentially allowing you to modify the data being inserted/updated/deleted. AFTER means the trigger logic executes *after* the event has already occurred.
• {INSERT | UPDATE | DELETE}: Specifies the event that will activate the trigger. The trigger will only execute when the specified data manipulation event occurs on the target table.
• ON table_name: Specifies the table that the trigger is associated with. The trigger will only be active for this specific table.
• FOR EACH ROW: This clause ensures that the trigger logic is executed *for each row* that is affected by the triggering event. This is crucial for most trigger applications.
• BEGIN ... END;: This block contains the actual SQL code that will be executed when the trigger is activated. This code can include SQL statements, variable assignments, conditional logic (IF statements), and more. You can have multiple statements within the BEGIN...END block.

Trigger Timing: BEFORE and AFTER

Choosing between BEFORE and AFTER is crucial and depends on the desired outcome:

• BEFORE Triggers: Executed *before* the event. This is useful for:
  • Data validation: Checking if the data being inserted or updated meets specific criteria.
  • Data transformation: Modifying the data before it's written to the table (e.g., converting a string to uppercase).
  • Preventing the event: You can prevent the INSERT, UPDATE, or DELETE from happening entirely by setting a condition within the trigger.
• AFTER Triggers: Executed *after* the event. This is useful for:
  • Auditing: Logging the changes made to the table to a separate audit table.
  • Propagating changes: Updating related tables based on the changes made to the target table.
  • Sending notifications: Triggering email alerts or other notifications based on changes.

Key difference: BEFORE triggers can modify the data being affected, and potentially cancel the operation. AFTER triggers cannot modify the data that triggered the event.

Event Types: INSERT, UPDATE, DELETE

Triggers respond to three primary data manipulation events:

• INSERT: The trigger is activated when a new row is inserted into the table.
• UPDATE: The trigger is activated when an existing row in the table is updated.
• DELETE: The trigger is activated when a row is deleted from the table.

Within the trigger logic, you can access the data being affected using special keywords:

• NEW: Available in INSERT and UPDATE triggers. Refers to the *new* row being inserted or the *new* version of the row being updated. You can access its columns like NEW.column_name.
• OLD: Available in UPDATE and DELETE triggers. Refers to the *old* version of the row being updated or the row being deleted. You can access its columns like OLD.column_name.

Example: BEFORE INSERT Trigger for Data Validation

 CREATE TRIGGER before_insert_products
BEFORE INSERT
ON products
FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price cannot be negative';
    END IF;
END; 

This trigger prevents the insertion of a new product if the price is negative. It uses the SIGNAL SQLSTATE statement to raise an error, effectively rejecting the INSERT operation.

Example: AFTER UPDATE Trigger for Auditing

 CREATE TRIGGER after_update_products
AFTER UPDATE
ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_audit (product_id, old_price, new_price, updated_at)
    VALUES (OLD.product_id, OLD.price, NEW.price, NOW());
END; 

This trigger logs price changes to an audit table (product_audit). It records the old and new prices, along with the timestamp of the update.

Conclusion

This section covered the fundamentals of MySQL triggers. Understanding their syntax, timing (BEFORE/AFTER), and event types (INSERT/UPDATE/DELETE) is crucial for effectively using them to enforce data integrity, automate tasks, and audit database changes.