MySQL triggers are explained by example: Automating database operations

MySQL Trigger: Automated Database Management

MySQL triggers are a powerful database feature that allows you to automatically perform predefined actions when specific events occur in tables, such as insertion, update, or delete. This is critical to implementing business rules, maintaining data integrity, and documenting database changes without explicit management in application code. This article will dive into the concept, usage of MySQL triggers, and some practical examples.

What is a MySQL trigger?

A trigger is essentially a set of SQL statements that are automatically executed by the MySQL database system when a specific event occurs on the table. These events include:

• INSERT: Triggered when a new row is inserted into the table.
• UPDATE: Fired when an existing row in the table is updated.
• DELETE: Triggered when rows in the table are deleted.

Triggers can be configured to execute before or after an event occurs, giving you flexible data processing capabilities.

Syntax for creating triggers

The basic syntax for creating triggers in MySQL is as follows:

 <code class="sql">CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_body;</code>

in:

• trigger_name : The name of the trigger.
• trigger_time : BEFORE (before event) or AFTER (after event).
• trigger_event : INSERT , UPDATE , or DELETE .
• table_name : The name of the table associated with the trigger.
• trigger_body : The SQL statement block executed by the trigger.

Triggering time: BEFORE and AFTER

• BEFORE Trigger: Executes before the trigger event occurs, allowing you to modify data before it is written to the table.
• AFTER Trigger: Executes after the trigger event occurs, and is often used to perform other operations based on data changes, such as logging.

MySQL trigger example

1. Insert trigger: automatically set default value

Suppose there is an employee table named employees that contains created_at column. We can create an AFTER INSERT trigger that automatically sets created_at to the current timestamp:

 <code class="sql">CREATE TRIGGER set_created_at AFTER INSERT ON employees FOR EACH ROW BEGIN UPDATE employees SET created_at = NOW() WHERE id = NEW.id; END;</code>

The NEW keyword refers to the newly inserted line.

2. Update trigger: Automatically calculate discount price

Assuming that the products table contains price and discount columns, we hope to automatically calculate discounted_price when price is updated:

 <code class="sql">CREATE TRIGGER update_discounted_price AFTER UPDATE ON products FOR EACH ROW BEGIN IF NEW.price  OLD.price THEN UPDATE products SET discounted_price = NEW.price * (1 - NEW.discount / 100) WHERE id = NEW.id; END IF; END;</code>

The OLD keyword references the line before the update.

3. Delete triggers: prevent key data deletion

To prevent key employees from being deleted, you can use the BEFORE DELETE trigger:

 <code class="sql">CREATE TRIGGER prevent_delete_critical_employee BEFORE DELETE ON employees FOR EACH ROW BEGIN IF OLD.is_critical = 1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete critical employee'; END IF; END;</code>

SIGNAL statement is used to raise an error and prevent delete operations.

4. Insert trigger: Create audit log

You can use triggers to record audit logs, such as logging the insertion operation of the employees table to audit_log table:

 <code class="sql">CREATE TRIGGER log_employee_insert AFTER INSERT ON employees FOR EACH ROW BEGIN INSERT INTO audit_log (action, table_name, record_id, action_time) VALUES ('insert', 'employees', NEW.id, NOW()); END;</code>

Manage triggers

• View triggers: Use SELECT * FROM information_schema.triggers; view triggers in the database.
• Delete trigger: Use DROP TRIGGER IF EXISTS trigger_name; delete the trigger.

Things to note when using triggers

• Performance impact: Triggers will increase database operation overhead, especially in large table or high frequency operation scenarios.
• Trigger nesting: Avoid triggers calling each other and prevent infinite loops.
• Data Integrity: Triggers help maintain data integrity, but require careful design and testing.
• Testing: Be sure to thoroughly test the triggers in the test environment before deploying in the production environment.

in conclusion

MySQL triggers are powerful database tools that automate many database management tasks. Rational use of triggers can improve data integrity and efficiency, but requires careful design and testing to avoid potential performance issues.

The above is the detailed content of MySQL triggers are explained by example: Automating database operations. For more information, please follow other related articles on the PHP Chinese website!