What is a Trigger?
A trigger is a set of SQL statements that are automatically executed (or “triggered”) in response to certain events on a table or view. Triggers are typically used to enforce business rules, validate data, or perform automatic updates.
2.1 Types of Triggers
Triggers are classified by the timing of their execution and the event that fires them.
- BEFORE Trigger: Executes before the operation (INSERT, UPDATE, DELETE) takes place.
- AFTER Trigger: Executes after the operation has completed.
- INSTEAD OF Trigger: Replaces the operation (INSERT, UPDATE, DELETE) with the trigger action.
BEFORE Trigger
A BEFORE trigger executes before an insert, update, or delete operation. This can be useful for validation or modification of data before it is saved to the database.
Example: Preventing an update if the price is negative:
CREATE TRIGGER BeforeUpdateProductPrice
BEFORE UPDATE ON Products
FOR EACH ROW
BEGIN
IF NEW.price < 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price cannot be negative';
END IF;
END;
AFTER Trigger
An AFTER trigger executes after an insert, update, or delete operation. This can be used for logging changes or updating related data.
Example: Logging an entry after a new order is inserted:
CREATE TRIGGER AfterInsertOrder
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
INSERT INTO OrderLog (order_id, action)
VALUES (NEW.order_id, 'INSERT');
END;
INSTEAD OF Trigger
An INSTEAD OF trigger is used to replace the normal action. For example, it can be used to perform complex logic instead of a simple insert, update, or delete.
Example: Replacing an insert operation:
CREATE TRIGGER InsteadOfInsertProduct
INSTEAD OF INSERT ON Products
FOR EACH ROW
BEGIN
-- Custom logic to modify the data before insert
INSERT INTO Products (product_name, price, category)
VALUES (NEW.product_name, NEW.price, 'General');
END;
2.2 Trigger Syntax
The general syntax for creating a trigger is as follows:
Syntax:
CREATE TRIGGER trigger_name
[BEFORE | AFTER | INSTEAD OF]
[INSERT | UPDATE | DELETE] ON table_name
FOR EACH ROW
BEGIN
-- Triggered SQL statements
END;
