MySQL Triggers

MySQL triggers are database objects that allow you to execute a set of SQL statements in response to a specific event, such as an insert, update, or delete operation on a table. In this tutorial, we'll discuss how to create triggers in MySQL.

Syntax

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

CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
BEGIN
   -- trigger body
END;

• The trigger_name is a user-defined identifier for the trigger.
• The trigger_time specifies when the trigger will be activated. It can be BEFORE or AFTER.
• The trigger_event specifies the type of event that activates the trigger. It can be INSERT, UPDATE, or DELETE.
• The table_name is the name of the table that the trigger is associated with.
• The FOR EACH ROW clause specifies that the trigger will be invoked once for each row affected by the triggering event.
• The BEGIN and END keywords enclose the SQL statements that make up the trigger's body.

Example

Let's create a trigger called products_audit_trail that logs insert, update, and delete operations on the products table.

DELIMITER $$
CREATE TRIGGER products_audit_trail
AFTER INSERT, UPDATE, DELETE
ON products
FOR EACH ROW
BEGIN
   IF (NEW.id IS NOT NULL) THEN
      INSERT INTO products_audit (product_id, operation, operation_time)
      VALUES (NEW.id, 'INSERT, UPDATE, or DELETE', NOW());
   END IF;
END$$
DELIMITER ;

In this example, we've defined an AFTER trigger that logs insert, update, and delete operations on the products table to an products_audit table. The NEW keyword refers to the new row that is being inserted or updated, while the OLD keyword refers to the old row that is being updated or deleted.

Output

When a trigger is activated, it will execute the SQL statements that make up its body. In this example, the trigger logs insert, update, and delete operations on the products table to an products_audit table.

Explanation

In our example, we created a trigger called products_audit_trail that is executed AFTER INSERT, UPDATE, or DELETE on the products table. The IF (NEW.id IS NOT NULL) condition ensures that the trigger only logs changes to products records that actually have a unique id assigned. The trigger logs these changes to an products_audit table, which records the time of the operation.

Use

Triggers are useful for implementing business rules, auditing changes to data, and enforcing referential integrity constraints. They can help ensure data consistency and reduce the need for complex application code.

Important Points

• Triggers are stored in the database and executed by the database server automatically in response to specific events.
• Triggers can be used to enforce business rules, log changes to data, and enforce referential integrity.
• Triggers can be activated by INSERT, UPDATE, or DELETE operations on a table.
• Triggers can be defined to BEFORE or AFTER the triggering event.
• Triggers are defined using SQL statements enclosed in a BEGIN and END block.

Summary

In this tutorial, we discussed how to create triggers in MySQL. We covered the syntax, example, output, explanation, use, and important points of triggers in MySQL. By understanding these concepts, you can implement triggers in your MySQL databases to enforce business rules, log changes to data, and ensure data consistency.