SQL Triggers - Complete Guide with Diagrams
Master SQL triggers with visual diagrams, practical examples, and best practices. Learn BEFORE, AFTER, INSTEAD OF triggers, and advanced trigger concepts.
SQL Triggers - Complete Guide
Table of Contents
- Introduction to Triggers
- DML Triggers
- BEFORE Triggers
- AFTER Triggers
- INSTEAD OF Triggers
- DDL Triggers
- Trigger Execution Order
- INSERTED and DELETED Tables
- Trigger Management
- Best Practices
- Common Use Cases
- Interview Questions
Introduction to Triggers
Triggers are special stored procedures that automatically execute when specific events occur in the database. They are event-driven and execute without explicit calls.
What is a Trigger?
A trigger is a database object that automatically executes a set of SQL statements when a specified event occurs on a table or view.
Trigger Architecture
┌─────────────────────────────────────────────────────────────┐
│ TRIGGER ARCHITECTURE │
├─────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────────┐ │
│ │ DML/DDL │ │
│ │ Operation │ │
│ └──────┬───────┘ │
│ │ │
│ ▼ │
│ ┌──────────────┐ │
│ │ Trigger │ ← Automatically fires │
│ │ Execution │ │
│ └──────┬───────┘ │
│ │ │
│ ▼ │
│ ┌──────────────┐ │
│ │ Business │ ← Enforce rules, audit, etc. │
│ │ Logic │ │
│ └──────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
Key Characteristics
┌──────────────────────────────────────────────────────────┐
│ TRIGGER OVERVIEW │
├──────────────────────────────────────────────────────────┤
│ │
│ ✅ Automatic Execution │
│ • Fires automatically on events │
│ • No explicit call needed │
│ • Transparent to applications │
│ │
│ ✅ Event-Driven │
│ • INSERT, UPDATE, DELETE │
│ • DDL operations (CREATE, ALTER, DROP) │
│ • Database logon events │
│ │
│ ✅ Data Integrity │
│ • Enforce business rules │
│ • Maintain referential integrity │
│ • Audit changes automatically │
│ • Validate data before changes │
│ │
│ ⚠️ Performance Impact │
│ • Can slow down operations │
│ • Should be optimized carefully │
│ • Avoid complex logic │
│ │
└──────────────────────────────────────────────────────────┘
Trigger Types
┌──────────────────────────────────────────────────────────┐
│ TRIGGER TYPES │
├──────────────────────────────────────────────────────────┤
│ │
│ 1. DML Triggers │
│ ├─ BEFORE Triggers │
│ │ • Execute before DML operation │
│ │ • Can modify data before insert/update │
│ │ • MySQL, PostgreSQL, Oracle │
│ │ │
│ ├─ AFTER (FOR) Triggers │
│ │ • Execute after DML operation completes │
│ │ • Cannot modify triggering data │
│ │ • All major databases │
│ │ │
│ └─ INSTEAD OF Triggers │
│ • Replace the DML operation │
│ • Useful for views │
│ • SQL Server, Oracle, PostgreSQL │
│ │
│ 2. DDL Triggers │
│ • Fire on schema changes │
│ • CREATE, ALTER, DROP operations │
│ • Database-level or server-level │
│ │
│ 3. LOGON Triggers │
│ • Fire when user logs into database │
│ • Security and auditing │
│ • SQL Server, Oracle │
│ │
└──────────────────────────────────────────────────────────┘
Trigger Execution Flow
┌──────────────────────────────────────────────────────────┐
│ TRIGGER EXECUTION FLOW │
├──────────────────────────────────────────────────────────┤
│ │
│ BEFORE Trigger: │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ Client │───▶│ BEFORE │───▶│ DML Op │ │
│ │ Request │ │ Trigger │ │ Execution │ │
│ └─────────────┘ └─────────────┘ └─────────────┘ │
│ │ │
│ └─ Can modify data │
│ │
│ AFTER Trigger: │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ Client │───▶│ DML Op │───▶│ AFTER │ │
│ │ Request │ │ Execution │ │ Trigger │ │
│ └─────────────┘ └─────────────┘ └─────────────┘ │
│ │ │
│ └─ Audit │
│ │
│ INSTEAD OF Trigger: │
│ ┌─────────────┐ ┌─────────────┐ │
│ │ Client │───▶│ INSTEAD OF │ │
│ │ Request │ │ Trigger │ (DML Op replaced) │
│ └─────────────┘ └─────────────┘ │
│ │ │
│ └─ Custom logic │
│ │
└──────────────────────────────────────────────────────────┘
DML Triggers
DML triggers fire in response to INSERT, UPDATE, or DELETE operations on a table.
DML Trigger Syntax
SQL Server:
CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
-- Trigger logic here
END;
MySQL:
CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
-- Trigger logic here
END;
PostgreSQL:
CREATE TRIGGER trigger_name
AFTER INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
EXECUTE FUNCTION trigger_function();
DML Trigger Events
┌──────────────────────────────────────────────────────────┐
│ DML TRIGGER EVENTS │
├──────────────────────────────────────────────────────────┤
│ │
│ INSERT Trigger │
│ ┌─────────────┐ ┌─────────────┐ │
│ │ INSERT │───▶│ Trigger │ │
│ │ Statement │ │ Fires │ │
│ └─────────────┘ └─────────────┘ │
│ • NEW values available │
│ • OLD values not available │
│ │
│ UPDATE Trigger │
│ ┌─────────────┐ ┌─────────────┐ │
│ │ UPDATE │───▶│ Trigger │ │
│ │ Statement │ │ Fires │ │
│ └─────────────┘ └─────────────┘ │
│ • NEW values available (after update) │
│ • OLD values available (before update) │
│ │
│ DELETE Trigger │
│ ┌─────────────┐ ┌─────────────┐ │
│ │ DELETE │───▶│ Trigger │ │
│ │ Statement │ │ Fires │ │
│ └─────────────┘ └─────────────┘ │
│ • NEW values not available │
│ • OLD values available │
│ │
└──────────────────────────────────────────────────────────┘
BEFORE Triggers
BEFORE triggers execute before the DML operation. They can modify the data before it's written to the table.
BEFORE Trigger Characteristics
- Execute before INSERT, UPDATE, or DELETE
- Can modify NEW values (not available in SQL Server)
- Can prevent the operation by raising an error
- Useful for data validation and transformation
MySQL BEFORE Trigger Example
-- Validate and transform data before insert
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
-- Validate salary
IF NEW.salary < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary cannot be negative';
END IF;
-- Auto-generate employee code
IF NEW.employee_code IS NULL THEN
SET NEW.employee_code = CONCAT('EMP', LPAD(NEW.employee_id, 6, '0'));
END IF;
-- Set default values
SET NEW.created_at = NOW();
SET NEW.status = COALESCE(NEW.status, 'ACTIVE');
END;
PostgreSQL BEFORE Trigger Example
-- Create trigger function
CREATE OR REPLACE FUNCTION before_employee_insert()
RETURNS TRIGGER AS $$
BEGIN
-- Validate salary
IF NEW.salary < 0 THEN
RAISE EXCEPTION 'Salary cannot be negative';
END IF;
-- Transform email to lowercase
NEW.email = LOWER(NEW.email);
-- Set timestamps
NEW.created_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Create trigger
CREATE TRIGGER before_employee_insert_trigger
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION before_employee_insert();
BEFORE Trigger Data Flow
┌──────────────────────────────────────────────────────────┐
│ BEFORE TRIGGER DATA FLOW │
├──────────────────────────────────────────────────────────┤
│ │
│ INSERT INTO employees VALUES (...) │
│ │ │
│ ▼ │
│ ┌──────────────────────────────────┐ │
│ │ BEFORE Trigger Fires │ │
│ │ • Access NEW values │ │
│ │ • Modify NEW values │ │
│ │ • Validate data │ │
│ │ • Set defaults │ │
│ └──────────────┬───────────────────┘ │
│ │ │
│ ▼ │
│ ┌──────────────────────────────────┐ │
│ │ Modified Data Inserted │ │
│ │ into Table │ │
│ └──────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────┘
AFTER Triggers
AFTER triggers execute after the DML operation completes. They cannot modify the triggering data but are useful for auditing and cascading changes.
AFTER Trigger Characteristics
- Execute after INSERT, UPDATE, or DELETE
- Cannot modify the triggering data
- Can access INSERTED and DELETED tables (SQL Server)
- Useful for auditing, logging, and cascading operations
SQL Server AFTER Trigger Example
-- Audit trail trigger
CREATE TRIGGER after_employee_update
ON employees
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- Log changes to audit table
INSERT INTO employee_audit (
employee_id,
old_salary,
new_salary,
old_department,
new_department,
changed_by,
changed_at
)
SELECT
i.employee_id,
d.salary AS old_salary,
i.salary AS new_salary,
d.department_id AS old_department,
i.department_id AS new_department,
SYSTEM_USER,
GETDATE()
FROM inserted i
INNER JOIN deleted d ON i.employee_id = d.employee_id
WHERE i.salary <> d.salary
OR i.department_id <> d.department_id;
END;
MySQL AFTER Trigger Example
-- Update summary table after insert
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
-- Update customer statistics
UPDATE customer_stats
SET
total_orders = total_orders + 1,
total_spent = total_spent + NEW.total_amount,
last_order_date = NEW.order_date
WHERE customer_id = NEW.customer_id;
-- Update product inventory
UPDATE products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
SET p.stock_quantity = p.stock_quantity - oi.quantity
WHERE oi.order_id = NEW.order_id;
END;
AFTER Trigger Data Flow
┌──────────────────────────────────────────────────────────┐
│ AFTER TRIGGER DATA FLOW │
├──────────────────────────────────────────────────────────┤
│ │
│ UPDATE employees SET salary = 50000 WHERE id = 1 │
│ │ │
│ ▼ │
│ ┌──────────────────────────────────┐ │
│ │ Data Updated in Table │ │
│ └──────────────┬───────────────────┘ │
│ │ │
│ ▼ │
│ ┌──────────────────────────────────┐ │
│ │ AFTER Trigger Fires │ │
│ │ • Access OLD values (deleted) │ │
│ │ • Access NEW values (inserted) │ │
│ │ • Log to audit table │ │
│ │ • Update related tables │ │
│ │ • Send notifications │ │
│ └──────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────┘
INSTEAD OF Triggers
INSTEAD OF triggers replace the DML operation entirely. They are particularly useful for views.
INSTEAD OF Trigger Characteristics
- Replace the DML operation
- Useful for complex views
- Can implement custom logic
- Available in SQL Server, Oracle, PostgreSQL
SQL Server INSTEAD OF Trigger Example
-- Create a view
CREATE VIEW employee_view AS
SELECT
e.employee_id,
e.first_name,
e.last_name,
e.email,
d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
-- Create INSTEAD OF trigger for the view
CREATE TRIGGER instead_of_employee_view_insert
ON employee_view
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
-- Get or create department
DECLARE @dept_id INT;
SELECT @dept_id = department_id
FROM departments
WHERE department_name = (SELECT department_name FROM inserted);
IF @dept_id IS NULL
BEGIN
INSERT INTO departments (department_name)
SELECT DISTINCT department_name FROM inserted;
SET @dept_id = SCOPE_IDENTITY();
END;
-- Insert into employees table
INSERT INTO employees (first_name, last_name, email, department_id)
SELECT first_name, last_name, email, @dept_id
FROM inserted;
END;
PostgreSQL INSTEAD OF Trigger Example
-- Create trigger function
CREATE OR REPLACE FUNCTION instead_of_employee_view_update()
RETURNS TRIGGER AS $$
BEGIN
-- Update employee table
UPDATE employees
SET
first_name = NEW.first_name,
last_name = NEW.last_name,
email = NEW.email
WHERE employee_id = NEW.employee_id;
-- Update department if changed
IF NEW.department_name <> OLD.department_name THEN
UPDATE employees
SET department_id = (
SELECT department_id
FROM departments
WHERE department_name = NEW.department_name
)
WHERE employee_id = NEW.employee_id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Create trigger
CREATE TRIGGER instead_of_employee_view_update_trigger
INSTEAD OF UPDATE ON employee_view
FOR EACH ROW
EXECUTE FUNCTION instead_of_employee_view_update();
INSTEAD OF Trigger Flow
┌──────────────────────────────────────────────────────────┐
│ INSTEAD OF TRIGGER FLOW │
├──────────────────────────────────────────────────────────┤
│ │
│ INSERT INTO employee_view VALUES (...) │
│ │ │
│ ▼ │
│ ┌──────────────────────────────────┐ │
│ │ INSTEAD OF Trigger Fires │ │
│ │ • Original INSERT is cancelled │ │
│ │ • Custom logic executes │ │
│ │ • Insert into base tables │ │
│ │ • Handle complex relationships │ │
│ └──────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌──────────────────────────────────┐ │
│ │ Data Inserted into Base Tables │ │
│ │ (not directly into view) │ │
│ └──────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────┘
DDL Triggers
DDL triggers fire in response to schema changes like CREATE, ALTER, or DROP statements.
DDL Trigger Syntax (SQL Server)
-- Database-level DDL trigger
CREATE TRIGGER prevent_table_drop
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
PRINT 'Dropping tables is not allowed!';
ROLLBACK;
END;
-- Server-level DDL trigger
CREATE TRIGGER audit_database_changes
ON ALL SERVER
FOR CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE
AS
BEGIN
INSERT INTO ddl_audit_log (
event_type,
object_name,
login_name,
event_time
)
VALUES (
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'),
SYSTEM_USER,
GETDATE()
);
END;
Oracle DDL Trigger Example
-- Audit DDL changes
CREATE OR REPLACE TRIGGER audit_ddl_changes
AFTER DDL ON SCHEMA
BEGIN
INSERT INTO ddl_audit_log (
event_type,
object_type,
object_name,
sql_text,
username,
event_time
)
VALUES (
SYS.DICTIONARY_OBJ_TYPE,
SYS.DICTIONARY_OBJ_NAME,
SYS.DICTIONARY_OBJ_OWNER,
SYS.LOGIN_USER,
SYSDATE
);
END;
/
DDL Trigger Use Cases
┌──────────────────────────────────────────────────────────┐
│ DDL TRIGGER USE CASES │
├──────────────────────────────────────────────────────────┤
│ │
│ 1. Prevent Unauthorized Changes │
│ • Block DROP TABLE operations │
│ • Restrict ALTER TABLE in production │
│ • Enforce naming conventions │
│ │
│ 2. Audit Schema Changes │
│ • Log all DDL operations │
│ • Track who made changes │
│ • Record when changes occurred │
│ │
│ 3. Enforce Standards │
│ • Validate object names │
│ • Ensure proper permissions │
│ • Check compliance rules │
│ │
│ 4. Synchronize Environments │
│ • Replicate schema changes │
│ • Update documentation │
│ • Notify administrators │
│ │
└──────────────────────────────────────────────────────────┘
Trigger Execution Order
When multiple triggers exist on the same table for the same event, execution order matters.
Trigger Execution Sequence
┌──────────────────────────────────────────────────────────┐
│ TRIGGER EXECUTION SEQUENCE │
├──────────────────────────────────────────────────────────┤
│ │
│ Multiple Triggers on Same Event: │
│ │
│ ┌─────────────────────────────────────────────┐ │
│ │ 1. BEFORE Triggers (if multiple) │ │
│ │ • Execute in creation order (MySQL) │ │
│ │ • Can specify order (SQL Server) │ │
│ └─────────────┬───────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────┐ │
│ │ 2. DML Operation Executes │ │
│ └─────────────┬───────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────┐ │
│ │ 3. AFTER Triggers (if multiple) │ │
│ │ • Execute in creation order (MySQL) │ │
│ │ • Can specify order (SQL Server) │ │
│ └─────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────┘
SQL Server Trigger Order
-- Set trigger execution order
EXEC sp_settriggerorder
@triggername = 'audit_trigger',
@order = 'First',
@stmttype = 'UPDATE';
EXEC sp_settriggerorder
@triggername = 'validation_trigger',
@order = 'Last',
@stmttype = 'UPDATE';
MySQL Trigger Order
-- Specify trigger order (MySQL 5.7.2+)
CREATE TRIGGER trigger1
BEFORE INSERT ON table_name
FOR EACH ROW
FOLLOWS trigger2 -- Execute after trigger2
BEGIN
-- Trigger logic
END;
CREATE TRIGGER trigger3
BEFORE INSERT ON table_name
FOR EACH ROW
PRECEDES trigger1 -- Execute before trigger1
BEGIN
-- Trigger logic
END;
INSERTED and DELETED Tables
SQL Server provides special tables to access old and new values in triggers.
INSERTED and DELETED Tables
┌──────────────────────────────────────────────────────────┐
│ INSERTED AND DELETED TABLES │
├──────────────────────────────────────────────────────────┤
│ │
│ INSERT Operation: │
│ ┌─────────────────────────────────────┐ │
│ │ INSERTED table: New rows │ │
│ │ DELETED table: Empty │ │
│ └─────────────────────────────────────┘ │
│ │
│ UPDATE Operation: │
│ ┌─────────────────────────────────────┐ │
│ │ INSERTED table: New values │ │
│ │ DELETED table: Old values │ │
│ └─────────────────────────────────────┘ │
│ │
│ DELETE Operation: │
│ ┌─────────────────────────────────────┐ │
│ │ INSERTED table: Empty │ │
│ │ DELETED table: Deleted rows │ │
│ └─────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────┘
Using INSERTED and DELETED Tables
-- Comprehensive audit trigger
CREATE TRIGGER audit_employee_changes
ON employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
-- Handle INSERT
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
BEGIN
INSERT INTO employee_audit (
employee_id,
action_type,
new_salary,
changed_by,
changed_at
)
SELECT
employee_id,
'INSERT',
salary,
SYSTEM_USER,
GETDATE()
FROM inserted;
END
-- Handle UPDATE
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
INSERT INTO employee_audit (
employee_id,
action_type,
old_salary,
new_salary,
changed_by,
changed_at
)
SELECT
i.employee_id,
'UPDATE',
d.salary,
i.salary,
SYSTEM_USER,
GETDATE()
FROM inserted i
INNER JOIN deleted d ON i.employee_id = d.employee_id;
END
-- Handle DELETE
IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
INSERT INTO employee_audit (
employee_id,
action_type,
old_salary,
changed_by,
changed_at
)
SELECT
employee_id,
'DELETE',
salary,
SYSTEM_USER,
GETDATE()
FROM deleted;
END
END;
Trigger Management
Viewing Triggers
-- MySQL: List triggers
SHOW TRIGGERS;
-- MySQL: Specific database
SHOW TRIGGERS FROM database_name;
-- PostgreSQL: List triggers
SELECT
trigger_name,
event_manipulation,
event_object_table,
action_timing
FROM information_schema.triggers
WHERE trigger_schema = 'public';
-- SQL Server: List triggers
SELECT
name AS trigger_name,
OBJECT_NAME(parent_id) AS table_name,
is_disabled
FROM sys.triggers
WHERE type = 'TR';
-- Oracle: List triggers
SELECT
trigger_name,
table_name,
triggering_event,
status
FROM user_triggers;
Disabling and Enabling Triggers
-- SQL Server
DISABLE TRIGGER trigger_name ON table_name;
ENABLE TRIGGER trigger_name ON table_name;
-- Disable all triggers on a table
DISABLE TRIGGER ALL ON table_name;
-- MySQL (drop and recreate)
DROP TRIGGER IF EXISTS trigger_name;
-- Oracle
ALTER TRIGGER trigger_name DISABLE;
ALTER TRIGGER trigger_name ENABLE;
-- PostgreSQL (rename to disable)
ALTER TABLE table_name DISABLE TRIGGER trigger_name;
ALTER TABLE table_name ENABLE TRIGGER trigger_name;
Dropping Triggers
-- MySQL
DROP TRIGGER IF EXISTS trigger_name;
-- SQL Server
DROP TRIGGER trigger_name;
-- PostgreSQL
DROP TRIGGER trigger_name ON table_name;
-- Oracle
DROP TRIGGER trigger_name;
Viewing Trigger Definition
-- MySQL
SHOW CREATE TRIGGER trigger_name;
-- SQL Server
SELECT
OBJECT_NAME(object_id) AS trigger_name,
definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('trigger_name');
-- PostgreSQL
SELECT
pg_get_triggerdef(oid)
FROM pg_trigger
WHERE tgname = 'trigger_name';
-- Oracle
SELECT trigger_body
FROM user_triggers
WHERE trigger_name = 'TRIGGER_NAME';
Best Practices
Trigger Design Principles
┌──────────────────────────────────────────────────────────┐
│ TRIGGER BEST PRACTICES │
├──────────────────────────────────────────────────────────┤
│ │
│ ✓ DO: │
│ • Keep triggers simple and fast │
│ • Use for data integrity and auditing │
│ • Document trigger purpose clearly │
│ • Test thoroughly with edge cases │
│ • Handle errors gracefully │
│ • Use SET NOCOUNT ON (SQL Server) │
│ │
│ ✗ DON'T: │
│ • Perform complex calculations │
│ • Call external services │
│ • Create cascading triggers │
│ • Use for business logic (use stored procedures) │
│ • Modify data in the same table (recursion) │
│ • Ignore performance impact │
│ │
└──────────────────────────────────────────────────────────┘
Performance Considerations
-- Bad: Complex logic in trigger
CREATE TRIGGER slow_trigger
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
-- Multiple complex queries
SELECT ... FROM ... WHERE ... (slow query);
UPDATE ... (another slow operation);
-- More complex logic
END;
-- Good: Simple, focused trigger
CREATE TRIGGER fast_trigger
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
-- Single, simple operation
UPDATE customer_stats
SET order_count = order_count + 1
WHERE customer_id = NEW.customer_id;
END;
Error Handling
-- SQL Server: Error handling in trigger
CREATE TRIGGER safe_trigger
ON employees
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
-- Trigger logic
INSERT INTO audit_log (...)
SELECT ... FROM inserted;
END TRY
BEGIN CATCH
-- Log error
INSERT INTO error_log (
error_message,
error_time
)
VALUES (
ERROR_MESSAGE(),
GETDATE()
);
-- Optionally rollback
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
END;
Avoiding Recursive Triggers
-- SQL Server: Prevent recursion
ALTER DATABASE database_name
SET RECURSIVE_TRIGGERS OFF;
-- Or check recursion level in trigger
CREATE TRIGGER check_recursion
ON table_name
AFTER UPDATE
AS
BEGIN
IF TRIGGER_NESTLEVEL() > 1
RETURN;
-- Trigger logic
END;
Common Use Cases
1. Audit Trail
-- Complete audit trail trigger
CREATE TRIGGER audit_employee_changes
AFTER INSERT, UPDATE, DELETE ON employees
FOR EACH ROW
BEGIN
DECLARE v_action VARCHAR(10);
IF INSERTING THEN
SET v_action = 'INSERT';
INSERT INTO employee_audit (
employee_id, action_type, new_data, changed_at
)
VALUES (
NEW.employee_id, v_action,
JSON_OBJECT('salary', NEW.salary, 'dept', NEW.department_id),
NOW()
);
ELSIF UPDATING THEN
SET v_action = 'UPDATE';
INSERT INTO employee_audit (
employee_id, action_type, old_data, new_data, changed_at
)
VALUES (
NEW.employee_id, v_action,
JSON_OBJECT('salary', OLD.salary, 'dept', OLD.department_id),
JSON_OBJECT('salary', NEW.salary, 'dept', NEW.department_id),
NOW()
);
ELSIF DELETING THEN
SET v_action = 'DELETE';
INSERT INTO employee_audit (
employee_id, action_type, old_data, changed_at
)
VALUES (
OLD.employee_id, v_action,
JSON_OBJECT('salary', OLD.salary, 'dept', OLD.department_id),
NOW()
);
END IF;
END;
2. Data Validation
-- Validate business rules
CREATE TRIGGER validate_employee_data
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
-- Validate salary range
IF NEW.salary < 30000 OR NEW.salary > 500000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary must be between 30000 and 500000';
END IF;
-- Validate email format
IF NEW.email NOT LIKE '%@%.%' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid email format';
END IF;
-- Validate hire date
IF NEW.hire_date > CURDATE() THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Hire date cannot be in the future';
END IF;
END;
3. Maintaining Derived Data
-- Update summary tables
CREATE TRIGGER update_order_summary
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
-- Update order total
UPDATE orders
SET
total_amount = (
SELECT SUM(quantity * unit_price)
FROM order_items
WHERE order_id = NEW.order_id
),
item_count = (
SELECT COUNT(*)
FROM order_items
WHERE order_id = NEW.order_id
)
WHERE order_id = NEW.order_id;
END;
4. Enforcing Referential Integrity
-- Custom referential integrity
CREATE TRIGGER check_department_delete
BEFORE DELETE ON departments
FOR EACH ROW
BEGIN
DECLARE emp_count INT;
SELECT COUNT(*) INTO emp_count
FROM employees
WHERE department_id = OLD.department_id;
IF emp_count > 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Cannot delete department with employees';
END IF;
END;
Interview Questions
Basic Questions
Q1: What is a trigger in SQL?
A trigger is a special type of stored procedure that automatically executes when specific events occur in the database, such as INSERT, UPDATE, DELETE, or DDL operations.
Q2: What are the types of triggers?
- DML Triggers: BEFORE, AFTER, INSTEAD OF (for INSERT, UPDATE, DELETE)
- DDL Triggers: For schema changes (CREATE, ALTER, DROP)
- LOGON Triggers: For user login events
Q3: What's the difference between BEFORE and AFTER triggers?
- BEFORE: Executes before the DML operation, can modify data before it's written
- AFTER: Executes after the DML operation completes, cannot modify triggering data
Intermediate Questions
Q4: What are INSERTED and DELETED tables?
Special tables in SQL Server that store:
- INSERTED: New rows (INSERT) or new values (UPDATE)
- DELETED: Deleted rows (DELETE) or old values (UPDATE)
Q5: Can a trigger call another trigger?
Yes, this is called trigger cascading or nested triggers. However, it should be avoided as it can lead to:
- Performance issues
- Difficult debugging
- Infinite loops if not handled properly
Q6: How do you prevent recursive triggers?
-- SQL Server
ALTER DATABASE database_name SET RECURSIVE_TRIGGERS OFF;
-- Or check nesting level
IF TRIGGER_NESTLEVEL() > 1 RETURN;
Advanced Questions
Q7: What's the difference between triggers and stored procedures?
| Aspect | Trigger | Stored Procedure |
|---|---|---|
| Execution | Automatic | Manual call |
| Parameters | No parameters | Can have parameters |
| Return values | Cannot return | Can return values |
| Transaction | Part of triggering transaction | Can control transactions |
| Use case | Automatic actions | Business logic |
Q8: How do you optimize trigger performance?
- Keep triggers simple and fast
- Avoid complex queries
- Use SET NOCOUNT ON (SQL Server)
- Minimize logging
- Avoid cascading triggers
- Use appropriate indexes
- Test with realistic data volumes
Q9: When should you use INSTEAD OF triggers?
- Updating complex views with multiple base tables
- Implementing custom logic for view modifications
- Handling non-updatable views
- Implementing soft deletes
Q10: What are the disadvantages of triggers?
- Hidden logic (not visible in application code)
- Performance overhead
- Difficult to debug
- Can cause unexpected side effects
- Maintenance challenges
- Potential for cascading issues
Summary
Triggers are powerful database objects for automating tasks and enforcing business rules. Key takeaways:
- BEFORE Triggers: Validate and transform data before operations
- AFTER Triggers: Audit, log, and cascade changes after operations
- INSTEAD OF Triggers: Replace operations with custom logic
- DDL Triggers: Monitor and control schema changes
- Best Practices: Keep simple, avoid cascading, handle errors, optimize performance
Use triggers judiciously for data integrity, auditing, and automation, but avoid complex business logic that belongs in application code or stored procedures.