Introduction to MySQL Triggers

Course: MySQL Training

Topic: Introduction to MySQL Triggers

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to MySQL Triggers

A trigger in MySQL is a named database object associated with a table that automatically executes when a specified database event occurs. Triggers are commonly used to validate data, maintain audit records, update related tables, and enforce business rules.

Unlike a stored procedure, which normally executes when a user or application calls it, a trigger activates automatically when its associated table experiences a supported event, such as an INSERT, UPDATE, or DELETE operation.

For example, imagine an online shopping application. When a new order is inserted into the database, a trigger could automatically create an audit record. When a product's stock quantity changes, a trigger could record the previous and new quantities. When an employee's salary changes, a trigger could preserve the salary history.

MySQL triggers are row-level triggers. This means the trigger body executes for each affected row, rather than once for the entire SQL statement.

1.1 Real-World Applications of Triggers

• Data validation: Prevent invalid values from being inserted or updated.
• Audit logging: Record changes made to important database records.
• Stock management: Track inventory changes when transactions are recorded.
• Salary history: Maintain a record of previous and updated employee salaries.
• Order processing: Record status changes and related events.
• Data consistency: Keep related tables synchronized when an operation occurs.
• Automatic timestamps: Populate or adjust timestamp-related columns when needed.

2. Learning Objectives

After studying this topic, you will be able to:

• Define a MySQL trigger and explain its purpose.
• Understand how triggers differ from stored procedures and stored functions.
• Identify the three supported trigger events: INSERT, UPDATE, and DELETE.
• Differentiate between BEFORE and AFTER triggers.
• Understand the OLD and NEW row references.
• Create simple triggers using CREATE TRIGGER.
• Use triggers for validation, auditing, and history tracking.
• View and remove existing triggers.
• Identify common trigger errors and follow best practices.

3. What Is a MySQL Trigger?

A MySQL trigger is a database object that contains SQL statements executed automatically when a specified operation affects a table.

A trigger definition specifies four main elements:

1. Trigger name: The identifier used to reference the trigger.
2. Trigger timing: BEFORE or AFTER the triggering operation.
3. Trigger event: INSERT, UPDATE, or DELETE.
4. Trigger body: The SQL statement or statements executed when the trigger activates.

3.1 Basic Example

CREATE TABLE account (
    account_id INT PRIMARY KEY,
    balance DECIMAL(10,2)
);

CREATE TABLE account_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    account_id INT,
    action_message VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The first table stores account information. The second table stores audit information that can be generated automatically by a trigger.

3.2 How a Trigger Works

1. A user or application executes an INSERT, UPDATE, or DELETE operation.
2. MySQL identifies triggers associated with the affected table and event.
3. The appropriate BEFORE trigger, if present, executes before the row operation.
4. The row operation is performed if the operation and applicable BEFORE triggers succeed.
5. The appropriate AFTER trigger, if present, executes after the row operation succeeds.
6. The SQL statement completes, or fails if a trigger raises an error.

4. Types of MySQL Triggers

MySQL supports triggers based on timing and the event that activates them. Combining the two categories produces six basic trigger types.

Important: MySQL does not support statement-level triggers. Each trigger is defined with FOR EACH ROW and executes for each affected row. For example, inserting five rows in one INSERT statement can cause an INSERT trigger to execute five times.

5. Understanding BEFORE and AFTER Triggers

5.1 BEFORE Trigger

A BEFORE trigger executes before the corresponding row operation is completed. It is useful for checking incoming values or adjusting permitted column values before they are stored.

For example, a BEFORE INSERT trigger can reject a negative product price.

DELIMITER //

CREATE TRIGGER BeforeProductInsert
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 //

DELIMITER ;

This example assumes a table named products with a numeric price column already exists.

5.2 AFTER Trigger

An AFTER trigger executes after the corresponding row operation succeeds. It is useful for audit logging and recording changes that have successfully occurred.

DELIMITER //

CREATE TRIGGER AfterProductInsert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_audit (
        product_id,
        action_message
    )
    VALUES (
        NEW.product_id,
        'Product inserted'
    );
END //

DELIMITER ;

This example assumes a compatible product_audit table exists. The trigger inserts an audit record after a product row has been inserted successfully.

5.3 BEFORE vs AFTER

6. Understanding INSERT, UPDATE, and DELETE Events

6.1 INSERT Event

An INSERT trigger activates when a row is inserted into a table. This includes rows inserted by statements such as INSERT and operations such as LOAD DATA. An INSERT trigger can access the incoming row using NEW.column_name.

CREATE TRIGGER BeforeCustomerInsert
BEFORE INSERT ON customers
FOR EACH ROW
SET NEW.customer_name = TRIM(NEW.customer_name);

This trigger removes leading and trailing spaces from a new customer's name. It assumes that the customers table contains a customer_name column that permits the resulting value.

6.2 UPDATE Event

An UPDATE trigger activates when an existing row is modified. It can use OLD.column_name to access the previous value and NEW.column_name to access the proposed updated value.

CREATE TRIGGER BeforeEmployeeUpdate
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
END;

This example illustrates a salary validation rule. The complete trigger definition with multiple statements should be created using the delimiter supported by the SQL client.

6.3 DELETE Event

A DELETE trigger activates when a row is deleted. The old row can be accessed using OLD.column_name. There is no NEW row for a DELETE operation.

CREATE TRIGGER AfterCustomerDelete
AFTER DELETE ON customers
FOR EACH ROW
INSERT INTO deleted_customer_log (
    customer_id,
    customer_name,
    deleted_at
)
VALUES (
    OLD.customer_id,
    OLD.customer_name,
    CURRENT_TIMESTAMP
);

This example stores information about deleted customers in a separate log table. The referenced table and columns must exist before the trigger is created.

7. Understanding OLD and NEW in MySQL Triggers

MySQL provides two special row references for triggers: OLD and NEW. They allow a trigger to access the values associated with the row affected by an operation.

7.1 Using NEW in an INSERT Trigger

CREATE TRIGGER BeforeOrderInsert
BEFORE INSERT ON orders
FOR EACH ROW
SET NEW.created_at = CURRENT_TIMESTAMP;

This example sets the incoming order's creation timestamp. It assumes the table contains a compatible created_at column.

7.2 Using OLD and NEW in an UPDATE Trigger

DELIMITER //

CREATE TRIGGER AfterSalaryUpdate
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_history (
            employee_id,
            old_salary,
            new_salary,
            changed_at
        )
        VALUES (
            OLD.employee_id,
            OLD.salary,
            NEW.salary,
            CURRENT_TIMESTAMP
        );
    END IF;
END //

DELIMITER ;

This trigger records a salary change only when the old and new salary values differ. If salary can be NULL, use a NULL-safe comparison such as NOT (OLD.salary <=> NEW.salary) to detect changes reliably.

7.3 Important Rules for OLD and NEW

• OLD is available for UPDATE and DELETE triggers.
• NEW is available for INSERT and UPDATE triggers.
• OLD values are read-only.
• In a BEFORE trigger, eligible NEW column values can be modified with SET.
• In an AFTER trigger, the row change has already occurred, so assigning a value to NEW is not permitted.
• Generated columns cannot be referenced through OLD or NEW in MySQL triggers.

8. Creating Your First MySQL Trigger

Follow these steps to create and test a basic trigger.

Step 1: Create a Database

CREATE DATABASE trigger_demo;
USE trigger_demo;

Step 2: Create a Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2) NOT NULL
);

Step 3: Create an Audit Table

CREATE TABLE employee_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    action_message VARCHAR(100) NOT NULL,
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 4: Create an AFTER INSERT Trigger

DELIMITER //

CREATE TRIGGER AfterEmployeeInsert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (
        employee_id,
        action_message
    )
    VALUES (
        NEW.employee_id,
        'Employee record inserted'
    );
END //

DELIMITER ;

Step 5: Insert a Record

INSERT INTO employees (
    employee_id,
    employee_name,
    salary
)
VALUES (
    101,
    'Aarav Sharma',
    55000.00
);

Step 6: Check the Employee Table

SELECT * FROM employees;

Expected employee data:

Step 7: Check the Audit Table

SELECT * FROM employee_audit;

The audit table should contain a record similar to the following:

The audit record is created automatically by the trigger. You do not need to issue a separate INSERT statement against the audit table.

9. Trigger Syntax in MySQL

The general syntax for creating a trigger is:

CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
trigger_body;

Where:

• trigger_name is the unique trigger name within the database schema.
• trigger_time is either BEFORE or AFTER.
• trigger_event is INSERT, UPDATE, or DELETE.
• table_name is the permanent table associated with the trigger.
• FOR EACH ROW specifies row-level execution.
• trigger_body contains the SQL statement or compound statement to execute.

9.1 Single-Statement Trigger

CREATE TRIGGER SetDefaultStatus
BEFORE INSERT ON orders
FOR EACH ROW
SET NEW.status = 'Pending';

This example sets the status to Pending for every inserted row. Use this pattern only when the business rule requires the status to be overwritten regardless of the submitted value. If an existing value should be preserved, check it explicitly.

9.2 Multiple-Statement Trigger

DELIMITER //

CREATE TRIGGER ValidateProduct
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;

    IF NEW.product_name IS NULL
       OR TRIM(NEW.product_name) = '' THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Product name is required';
    END IF;
END //

DELIMITER ;

A compound trigger body uses BEGIN and END to group multiple statements. The example assumes that the product table has price and product_name columns.

10. Using Triggers for Data Validation

Triggers can enforce business rules when a row is inserted or updated. They are useful when a rule must apply to changes from multiple applications that write to the same table.

10.1 Validate Product Price

DELIMITER //

CREATE TRIGGER ValidateProductPrice
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Product price must not be negative';
    END IF;
END //

DELIMITER ;

If a user attempts to insert a negative price, the trigger raises an error and the row operation fails.

10.2 Validate Salary During an Update

DELIMITER //

CREATE TRIGGER ValidateEmployeeSalary
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Salary must not be negative';
    END IF;
END //

DELIMITER ;

This trigger prevents a negative salary from being assigned during an UPDATE operation. If INSERT operations also need validation, define an appropriate BEFORE INSERT trigger or use suitable database constraints.

11. Using Triggers for Audit Logging

Audit logging means recording changes to data, including which record changed, the previous value, the new value, and the time of the change. Audit tables are useful for troubleshooting and reviewing business activity.

11.1 Create an Audit Table

CREATE TABLE salary_history (
    history_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    old_salary DECIMAL(10,2),
    new_salary DECIMAL(10,2),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

11.2 Create the Audit Trigger

DELIMITER //

CREATE TRIGGER RecordSalaryChange
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NOT (OLD.salary <=> NEW.salary) THEN
        INSERT INTO salary_history (
            employee_id,
            old_salary,
            new_salary,
            changed_at
        )
        VALUES (
            OLD.employee_id,
            OLD.salary,
            NEW.salary,
            CURRENT_TIMESTAMP
        );
    END IF;
END //

DELIMITER ;

The NULL-safe equality operator <=> compares two values while treating two NULL values as equal. Negating the comparison detects a change even when one of the values is NULL.

11.3 Test the Trigger

UPDATE employees
SET salary = 60000.00
WHERE employee_id = 101;

SELECT * FROM salary_history;

The history table should contain the previous salary and the updated salary for employee 101.

12. Using Triggers for DELETE History

When a record is deleted, its values are no longer available in the base table. A DELETE trigger can preserve selected information in a separate table before the operation completes or after it succeeds.

12.1 Create a Deleted-Employee Log

CREATE TABLE deleted_employees (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    employee_name VARCHAR(100),
    last_salary DECIMAL(10,2),
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

12.2 Create an AFTER DELETE Trigger

DELIMITER //

CREATE TRIGGER LogDeletedEmployee
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO deleted_employees (
        employee_id,
        employee_name,
        last_salary,
        deleted_at
    )
    VALUES (
        OLD.employee_id,
        OLD.employee_name,
        OLD.salary,
        CURRENT_TIMESTAMP
    );
END //

DELIMITER ;

12.3 Test the Trigger

DELETE FROM employees
WHERE employee_id = 101;

SELECT * FROM deleted_employees;

The log table preserves the selected details of the deleted employee. For transactional tables, if the triggering statement fails, changes made by the trigger are rolled back along with the statement.

13. Viewing and Managing Triggers

MySQL provides commands and metadata tables for inspecting triggers defined in the current database schema.

13.1 Display Existing Triggers

SHOW TRIGGERS;

This command lists triggers visible in the current schema.

13.2 Display Triggers for a Particular Table

SHOW TRIGGERS LIKE 'employees';

The LIKE pattern filters the displayed trigger information by table name.

13.3 View Trigger Metadata

SELECT
    TRIGGER_NAME,
    EVENT_MANIPULATION,
    EVENT_OBJECT_TABLE,
    ACTION_TIMING,
    ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE();

This query returns trigger names, events, associated tables, timing, and trigger-body information for the current database.

13.4 Display a Trigger Definition

SHOW CREATE TRIGGER AfterEmployeeInsert;

This statement displays the CREATE TRIGGER definition for the named trigger, subject to the permissions of the current account.

13.5 Drop a Trigger

DROP TRIGGER AfterEmployeeInsert;

DROP TRIGGER removes the trigger definition. It does not delete rows that the trigger previously inserted into an audit table.

To remove a trigger from a specified schema, you can use a qualified name:

DROP TRIGGER trigger_demo.AfterEmployeeInsert;

Always verify the trigger name and schema before dropping a trigger in a production environment.

14. Trigger Execution and Transactions

Trigger execution is part of the SQL operation that activates the trigger. The success or failure of the trigger can affect the overall statement.

• If a BEFORE trigger raises an error, the corresponding row operation is not performed.
• An AFTER trigger executes only when the row operation and applicable BEFORE triggers succeed.
• If an error occurs in a trigger, the statement that caused the trigger to execute fails.
• For transactional tables such as InnoDB tables, trigger changes made as part of a failed statement are rolled back with that statement.
• Triggers cannot independently issue transaction-ending statements such as COMMIT or ROLLBACK.
• For nontransactional tables, rollback behavior differs, and changes made before an error may remain.

For reliable transactional behavior, use suitable transactional storage engines and test trigger error handling carefully.

15. MySQL Trigger Limitations

Triggers are powerful, but they have restrictions that should be understood before using them in application design.

• Triggers can be associated with permanent tables, not views or temporary tables.
• MySQL supports INSERT, UPDATE, and DELETE trigger events; it does not provide native BEFORE SELECT triggers.
• Triggers execute once per affected row, not once per SQL statement.
• A trigger cannot use transaction-ending statements such as COMMIT or ROLLBACK.
• A trigger cannot call a stored procedure that returns result sets to the client or uses dynamic SQL.
• A trigger cannot modify a table that is already being used by the statement that invoked the trigger in a prohibited way.
• Triggers do not activate for TRUNCATE TABLE or DROP TABLE as DELETE triggers do for row deletions.
• Cascaded foreign-key actions do not activate triggers.
• Triggers require appropriate privileges and access to any tables or columns referenced in their body.

16. Common Errors and Troubleshooting

16.1 Troubleshooting Checklist

• Confirm that the correct database is selected.
• Check whether the trigger exists using SHOW TRIGGERS.
• Verify the trigger event and timing.
• Confirm that OLD and NEW are used appropriately.
• Check the spelling and data types of referenced columns.
• Review all BEGIN and END blocks.
• Use the delimiter syntax supported by your SQL client.
• Test with a small sample record and inspect the expected output.
• Check whether a SIGNAL statement or constraint is rejecting the operation.

17. Best Practices for MySQL Triggers

• Keep triggers focused: Each trigger should have a clear, limited responsibility.
• Choose timing carefully: Use BEFORE for validation and permitted incoming-value changes; use AFTER for actions that depend on a successful row change.
• Use descriptive names: Names such as AfterEmployeeInsert or BeforeProductUpdate make trigger behavior easier to identify.
• Avoid unnecessary complexity: Long trigger bodies are difficult to test and maintain.
• Validate inputs explicitly: Handle negative values, missing values, and unexpected conditions where appropriate.
• Consider NULL values: Use NULL-safe comparisons when checking whether values have changed.
• Be aware of side effects: A trigger may update other tables or insert audit records automatically.
• Test multi-row statements: Remember that the trigger runs once for each affected row.
• Avoid hidden business logic: Document important behavior so application developers understand automatic database actions.
• Monitor performance: A trigger that executes expensive queries for every row can slow large INSERT, UPDATE, or DELETE operations.
• Review privileges: Ensure that trigger creation and execution use appropriate database permissions.
• Use constraints when appropriate: A NOT NULL, CHECK, UNIQUE, or foreign-key constraint may be a simpler solution for basic data integrity rules.

18. Difference Between Triggers, Stored Procedures, and Stored Functions

19. Practical Project: Product Inventory Audit System

Project objective: Create a product table and automatically record changes to its stock quantity using a trigger.

19.1 Create the Products Table

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

19.2 Create the Inventory Audit Table

CREATE TABLE inventory_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    old_quantity INT,
    new_quantity INT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

19.3 Create the Trigger

DELIMITER //

CREATE TRIGGER AfterProductQuantityUpdate
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.quantity <> NEW.quantity THEN
        INSERT INTO inventory_audit (
            product_id,
            old_quantity,
            new_quantity,
            changed_at
        )
        VALUES (
            OLD.product_id,
            OLD.quantity,
            NEW.quantity,
            CURRENT_TIMESTAMP
        );
    END IF;
END //

DELIMITER ;

Because the quantity column is declared NOT NULL, an ordinary NULL-to-value comparison is not needed in this example. If nullable quantities were allowed, use a NULL-safe comparison.

19.4 Insert Sample Products

INSERT INTO products (
    product_id,
    product_name,
    quantity,
    price
)
VALUES
(1, 'Keyboard', 50, 1200.00),
(2, 'Mouse', 100, 600.00);

19.5 Update Product Quantity

UPDATE products
SET quantity = 45
WHERE product_id = 1;

19.6 View the Audit History

SELECT * FROM inventory_audit;

The audit table should contain the previous quantity of 50 and the new quantity of 45 for product 1.

19.7 Expected Output

This project demonstrates how a trigger can automatically maintain an inventory history without requiring every application to issue a separate audit INSERT statement.

20. Practice Exercises

1. Create a BEFORE INSERT trigger that prevents a product from having a negative price.
2. Create an AFTER INSERT trigger that writes a record to an audit table whenever a customer is created.
3. Create a BEFORE UPDATE trigger that rejects a negative employee salary.
4. Create an AFTER UPDATE trigger that records old and new employee department values.
5. Create an AFTER DELETE trigger that preserves selected details of deleted orders.
6. Create a trigger that trims spaces from an incoming customer name.
7. Use OLD and NEW to detect whether a product's price has changed.
8. Use SHOW TRIGGERS to list triggers in the current schema.
9. Use INFORMATION_SCHEMA.TRIGGERS to retrieve trigger names and events.
10. Drop a test trigger using DROP TRIGGER.

21. Interview Questions and Answers

Q1. What is a trigger in MySQL?

A trigger is a database object associated with a table that executes automatically when a specified INSERT, UPDATE, or DELETE event occurs.

Q2. What is the purpose of a trigger?

Triggers are used for data validation, audit logging, history tracking, and other actions that should happen automatically in response to table changes.

Q3. What are the six basic trigger types in MySQL?

BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, and AFTER DELETE.

Q4. What is the difference between BEFORE and AFTER triggers?

A BEFORE trigger runs before the row operation is completed. An AFTER trigger runs after the row operation succeeds.

Q5. What is the purpose of OLD and NEW?

OLD provides the previous row values for UPDATE and DELETE operations. NEW provides incoming or updated row values for INSERT and UPDATE operations.

Q6. Can we modify OLD values?

No. OLD values are read-only.

Q7. Can we modify NEW values?

Eligible NEW column values can be changed in a BEFORE trigger. They cannot be changed through NEW in an AFTER trigger.

Q8. Does MySQL support statement-level triggers?

No. MySQL triggers are row-level triggers and use FOR EACH ROW.

Q9. Can a table have multiple triggers?

Yes. A table can have multiple triggers for different events and timings. MySQL 8.4 also permits multiple triggers with the same event and timing, with ordering controlled by creation order or FOLLOWS/PRECEDES.

Q10. How can we view triggers?

Use SHOW TRIGGERS; or query INFORMATION_SCHEMA.TRIGGERS.

Q11. How do we remove a trigger?

Use DROP TRIGGER trigger_name;.

Q12. Can a trigger return a result set directly to the client?

A trigger does not return a result set to the calling client in the way a stored procedure can. It performs permitted database operations as part of the triggering statement.

Q13. Can a trigger call a stored procedure?

Yes, subject to restrictions. A trigger cannot call a procedure that returns result sets to the client or uses dynamic SQL.

Q14. Does TRUNCATE TABLE activate a DELETE trigger?

No. TRUNCATE TABLE does not perform row-by-row DELETE operations and does not activate DELETE triggers.

Q15. What happens if a trigger raises an error?

The statement that activated the trigger fails. For transactional tables, changes made by the failed statement and its triggers are rolled back.

Q16. Why are triggers useful for audit logging?

They automatically record changes made through different SQL-based applications, helping maintain a consistent history of database activity.

Q17. Can triggers be created on views?

No. MySQL triggers are associated with permanent tables, not views.

Q18. What does SIGNAL SQLSTATE '45000' do?

It raises a user-defined exception, allowing a trigger to reject an operation that violates a business rule.

22. Quick Revision Notes

• A trigger executes automatically when a supported table event occurs.
• MySQL supports INSERT, UPDATE, and DELETE trigger events.
• Triggers can run BEFORE or AFTER a row operation.
• All MySQL triggers are row-level and use FOR EACH ROW.
• OLD refers to previous row values; NEW refers to incoming or updated values.
• INSERT triggers use NEW; DELETE triggers use OLD; UPDATE triggers can use both.
• BEFORE triggers can modify eligible NEW column values.
• AFTER triggers are useful for recording successful changes.
• Use CREATE TRIGGER to create a trigger.
• Use SHOW TRIGGERS or INFORMATION_SCHEMA.TRIGGERS to inspect triggers.
• Use DROP TRIGGER to remove a trigger definition.
• Use SIGNAL to raise an error when validation fails.
• Triggers cannot independently commit or roll back transactions.
• TRUNCATE TABLE does not activate DELETE triggers.
• Keep triggers focused, test thoroughly, and document their side effects.

23. Conclusion

MySQL triggers provide an automatic way to respond to changes in database tables. By understanding trigger timing, event types, and the OLD and NEW references, developers can validate incoming data, maintain audit trails, preserve deleted information, and support important business rules.

Triggers should be designed carefully because they run automatically and may affect application behavior, database performance, and transaction outcomes. Use them for clear database-level requirements, test them with both single-row and multi-row operations, and document their behavior so that the entire application team understands their effects.

24. Learn More with JustAcademy

Explore the MySQL Training Course to learn more about SQL queries, database design, stored procedures, stored functions, triggers, and other essential database concepts.

MySQL Training Course: https://www.justacademy.co/course-detail/mysql-training

Register for a Course Demo: https://www.justacademy.co/register-for-course-demo