SQL Triggers

A Trigger is a special kind of stored procedure that automatically executes in response to certain events on a table or view, such as inserts, updates, or deletes.

🔹 What is a Trigger?
Triggers are used to enforce business rules, maintain audit logs, or validate data automatically when data modification events occur.

🔹 Basic Types of Triggers

• BEFORE: Executes before the data change (useful to validate or modify data)
• AFTER: Executes after the data change (useful for logging or cascading changes)
• INSTEAD OF: (mostly for views) Replaces the triggering operation

🔹 Basic Syntax Example (MySQL)

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
   -- Trigger logic here
   SET NEW.column_name = UPPER(NEW.column_name);
END;

🔹 Example: Audit Log on UPDATE

CREATE TRIGGER update_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
   INSERT INTO audit_log(employee_id, changed_on)
   VALUES (NEW.employee_id, NOW());
END;

🔹 Use Cases

• Automatic data validation
• Maintaining audit trails
• Enforcing referential integrity rules
• Synchronous replication of data

🔹 Important Notes

• Triggers run automatically and cannot be called directly.
• Avoid complex logic inside triggers to prevent performance issues.
• Triggers differ in syntax and capabilities across DBMS (MySQL, Oracle, SQL Server, PostgreSQL).
• Test triggers carefully to avoid infinite loops or unintended side effects.

🧠 Quick Recap

💡 Triggers help automate database logic but use them wisely to maintain good performance and clarity.