Triggers in MySQL

Introduction:

A trigger in MySQL is a database object that automatically executes a specified action or set of actions in response to certain events occurring in the database. These events can include INSERT, UPDATE, DELETE, or even database startup and shutdown events. Triggers are used to enforce data integrity, maintain consistency, and automate repetitive tasks within the database.

Basic example of creating a trigger in MySQL:

CREATE TRIGGER my_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
    -- Trigger body: Perform actions before inserting into my_table
    SET NEW.column_name = 'value';
END;

In this example, a BEFORE INSERT trigger named my_trigger is created on the table my_table. The trigger body modifies the value of a column before inserting a new row into the table.

Overall, triggers in MySQL are powerful tools for automating database tasks, enforcing data integrity, and implementing business logic within the database. However, they should be used judiciously to avoid unintended consequences and maintain the performance of the database system.

Understanding Triggers:

Key components and concepts related to triggers in MySQL:

1. Event: Triggers are associated with specific events that occur on a particular table, such as INSERT, UPDATE, or DELETE operations. When the specified event occurs on the table, the trigger is activated, and its defined actions are executed.

2. Timing: Triggers can be classified based on their timing, which determines when the trigger’s actions are executed in relation to the triggering event. The two main types of timing are:

• BEFORE: Actions defined in a BEFORE trigger are executed before the triggering event is applied to the table. These triggers are commonly used to validate or modify data before it is inserted, updated, or deleted.
• AFTER: Actions defined in an AFTER trigger are executed after the triggering event has been applied to the table. These triggers are often used to perform tasks such as logging changes, updating other tables, or enforcing referential integrity constraints.

3. Trigger Body: The trigger body contains the SQL statements or procedural code that define the actions to be performed when the trigger is activated. This can include single or multiple SQL statements, as well as calls to stored procedures or functions.

4. Access to Data: Triggers in MySQL have access to both the old and new values of the rows affected by the triggering event. This allows triggers to compare values before and after the event, enabling actions such as data validation, auditing, or maintaining referential integrity.

5. Multiple Triggers: Multiple triggers can be defined on the same table for different events and timing. Triggers are executed in a predefined order based on their timing (BEFORE triggers execute before AFTER triggers).

6. Trigger Creation: Triggers in MySQL are created using the CREATE TRIGGER statement, which specifies the trigger name, the triggering event, the timing, the table to which the trigger is attached, and the trigger body.

Creating Triggers:

• Syntax for creating triggers in MySQL.
• Example scenario: Creating a trigger to update the “Company_expenses” table after inserting new employee salaries in the “Employees” table.

CREATE TRIGGER update_expenses AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
    UPDATE Company_expenses
    SET salaries_expenses = salaries_expenses + NEW.salary;
END;

Using BEFORE Triggers:

• Explanation of BEFORE triggers and their application.
• Example scenario: Using a BEFORE trigger to validate and adjust new employee salaries before insertion into the “Employees” table.

CREATE TRIGGER adjust_salary BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 30000 THEN
        SET NEW.salary = 30000;
    END IF;
END;

Working with AFTER Triggers:

• Application of AFTER triggers and their significance.
• Example scenario: Employing an AFTER trigger to calculate and update tax expenses in the “Company_expenses” table after updating employee salaries.

CREATE TRIGGER update_tax AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
    UPDATE Company_expenses
    SET taxes = (SELECT SUM(salary * 0.2) FROM Employees);
END;

Ref: MySQL :: MySQL 8.0 Reference Manual :: 27.3.1 Trigger Syntax and Examples