13. Triggers

What is a Trigger?

A trigger in SQL is a special kind of stored program that automatically executes when certain events occur in a table — such as an INSERT, UPDATE, or DELETE.

Think of it like a “background script” that runs whenever your data changes — no manual call needed!

---

Trigger Syntax 

CREATE TRIGGER TriggerName(
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON Table_Name
FOR EACH ROW
BEGIN 
--
END;

Example: Automatically Update Inventory After a Sale
Trigger Code 
CREATE TRIGGER Update_Inventory_Trigger(
AFTER INSERT 
ON Sales
FOR EACH ROW
BEGIN 
UPDATE Inventory
SET quantity = quantity - :NEW.qantity
WHERE product_id = :NEW.product_id
END;

Explanation

:NEW

→ Refers to the newly inserted row in Sales
Why Use Triggers?
Triggers help enforce rules and automate routine actions, such as:
 Automatically updating audit logs when data changes
 Validating data before inserting it
 Updating related tables to maintain consistency
Sending notifications or recalculating totals
Types of Triggers

Type
Fires When
Common Use Case
BEFORE INSERT
Before inserting a new record
Validate data
AFTER INSERT
After inserting a new record
Log or update related data
BEFORE UPDATE
Before updating an existing record
Validate or modify data
AFTER UPDATE
After updating an existing record
Sync related tables
BEFORE DELETE
Before deleting a record
Prevent accidental deletions
AFTER DELETE
After deleting a record
Archive deleted data