Triggers - Learning Module

Loading content...

0/252

INSERT/UPDATE/DELETE Triggers

Three Operations, Three Different Contexts

Every trigger responds to one or more Data Manipulation Language (DML) events: INSERT, UPDATE, or DELETE. While the trigger mechanism is similar across these events, each presents a unique context:

INSERT: A row that didn't exist is being created. There's no "before" state.
UPDATE: A row is being modified. Both the "before" and "after" states exist.
DELETE: A row is being removed. There's no "after" state.

These fundamental differences affect what data your trigger can access (OLD vs. NEW), what modifications are meaningful, and what patterns are appropriate. Understanding these distinctions is essential for writing triggers that work correctly across all scenarios.

What You Will Learn

By the end of this page, you will understand how each DML event type affects trigger behavior, master OLD and NEW row access patterns, implement column-specific UPDATE triggers, handle combined event triggers, and apply event-specific patterns for audit logging, data transformation, and referential integrity.

INSERT Triggers: Handling New Data

INSERT triggers fire when new rows are added to a table. Since the row didn't exist before the operation, only the NEW row is available—there is no OLD row.

Data Availability for INSERT:

Correlation	BEFORE INSERT	AFTER INSERT
OLD row	Not available	Not available
NEW row	Available, modifiable	Available, read-only

Common Use Cases for INSERT Triggers:

Data initialization — Setting timestamps, generating codes, computing derived values
Default value logic — Complex defaults that depend on other columns or external state
Validation — Business rules that cannot be expressed as CHECK constraints
Cascading inserts — Creating related records in other tables
Audit logging — Recording that a new record was created
Notification — Alerting external systems of new data

insert_trigger_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- INSERT Trigger Example 1: Data Initialization
-- PostgreSQL syntax
 
CREATE OR REPLACE FUNCTION initialize_order()
RETURNS TRIGGER AS $$
BEGIN
    -- Generate order number: ORD-YYYYMMDD-SEQUENCE
    NEW.order_number := 'ORD-' || TO_CHAR(CURRENT_DATE, 'YYYYMMDD') 
                        || '-' || LPAD(NEXTVAL('order_seq')::TEXT, 6, '0');
    
    -- Initialize timestamps
    NEW.created_at := CURRENT_TIMESTAMP;
    NEW.updated_at := CURRENT_TIMESTAMP;
    
    -- Set initial status
    IF NEW.status IS NULL THEN
        NEW.status := 'pending';
    END IF;
    
    -- Set created_by from session context
    NEW.created_by := CURRENT_USER;
    
    -- Calculate estimated delivery based on shipping method
    NEW.estimated_delivery := CASE NEW.shipping_method
        WHEN 'express' THEN CURRENT_DATE + INTERVAL '2 days'
        WHEN 'standard' THEN CURRENT_DATE + INTERVAL '5 days'
        WHEN 'economy' THEN CURRENT_DATE + INTERVAL '10 days'
        ELSE CURRENT_DATE + INTERVAL '7 days'
    END;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_initialize_order
    BEFORE INSERT ON orders
    FOR EACH ROW
    EXECUTE FUNCTION initialize_order();

insert_trigger_validation.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
-- INSERT Trigger Example 2: Complex Validation
-- PostgreSQL syntax
 
CREATE OR REPLACE FUNCTION validate_new_employee()
RETURNS TRIGGER AS $$
DECLARE
    v_dept_budget DECIMAL(12,2);
    v_dept_spent DECIMAL(12,2);
    v_dept_headcount INTEGER;
    v_max_headcount INTEGER;
BEGIN
    -- Rule 1: Check department budget capacity
    SELECT budget_amount, 
           COALESCE(SUM(e.salary), 0),
           COUNT(e.employee_id),
           max_employees
    INTO v_dept_budget, v_dept_spent, v_dept_headcount, v_max_headcount
    FROM departments d
    LEFT JOIN employees e ON d.dept_id = e.dept_id
    WHERE d.dept_id = NEW.dept_id
    GROUP BY d.dept_id, d.budget_amount, d.max_employees;
    
    IF v_dept_spent + NEW.salary > v_dept_budget THEN
        RAISE EXCEPTION 'Adding employee would exceed department budget. Budget: %, Current: %, New Salary: %',
            v_dept_budget, v_dept_spent, NEW.salary
            USING HINT = 'Request budget increase or reassign to different department';
    END IF;
    
    -- Rule 2: Check department headcount limit
    IF v_dept_headcount >= v_max_headcount THEN
        RAISE EXCEPTION 'Department % has reached maximum headcount of %',
            NEW.dept_id, v_max_headcount;
    END IF;
    
    -- Rule 3: Manager must exist and be in same department
    IF NEW.manager_id IS NOT NULL THEN
        IF NOT EXISTS (
            SELECT 1 FROM employees 
            WHERE employee_id = NEW.manager_id 
            AND dept_id = NEW.dept_id
        ) THEN
            RAISE EXCEPTION 'Manager must be in the same department';
        END IF;
    END IF;
    
    -- Rule 4: Email domain must match company
    IF NEW.email NOT LIKE '%@company.com' THEN
        RAISE EXCEPTION 'Employee email must use company.com domain';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_validate_new_employee
    BEFORE INSERT ON employees
    FOR EACH ROW
    EXECUTE FUNCTION validate_new_employee();

insert_trigger_cascade.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
-- INSERT Trigger Example 3: Cascading Inserts
-- PostgreSQL syntax
 
CREATE OR REPLACE FUNCTION create_user_profile()
RETURNS TRIGGER AS $$
BEGIN
    -- Create default user profile when user account is created
    INSERT INTO user_profiles (
        user_id,
        display_name,
        avatar_url,
        timezone,
        locale,
        notification_preferences
    ) VALUES (
        NEW.user_id,
        COALESCE(NEW.display_name, NEW.username),
        '/avatars/default.png',
        'UTC',
        'en-US',
        JSONB_BUILD_OBJECT(
            'email', TRUE,
            'push', TRUE,
            'sms', FALSE
        )
    );
    
    -- Create default user settings
    INSERT INTO user_settings (
        user_id,
        theme,
        items_per_page,
        privacy_level
    ) VALUES (
        NEW.user_id,
        'system',
        25,
        'friends'
    );
    
    -- Initialize user statistics
    INSERT INTO user_statistics (
        user_id,
        posts_count,
        followers_count,
        following_count,
        joined_at
    ) VALUES (
        NEW.user_id,
        0,
        0,
        0,
        NEW.created_at
    );
    
    -- Note: Using AFTER trigger ensures NEW.user_id is available
    -- (auto-generated by the INSERT)
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_create_user_profile
    AFTER INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION create_user_profile();

BEFORE vs. AFTER for INSERT

Use BEFORE INSERT when you need to modify column values or validate before constraints run. Use AFTER INSERT when you need the final row values (including auto-generated IDs) or when creating related records in other tables that reference this row.

UPDATE Triggers: Tracking Changes

UPDATE triggers fire when existing rows are modified. This is the only event type where both OLD and NEW rows are available, enabling powerful change-tracking and validation patterns.

Data Availability for UPDATE:

Correlation	BEFORE UPDATE	AFTER UPDATE
OLD row	Available, read-only	Available, read-only
NEW row	Available, modifiable	Available, read-only

Common Use Cases for UPDATE Triggers:

Change tracking — Comparing OLD and NEW to detect what changed
Audit logging — Recording before/after values for compliance
Temporal tables — Maintaining history of all previous versions
Cascading updates — Propagating changes to related tables
Update validation — Enforcing rules about allowed transitions
Automatic timestamps — Setting updated_at on every modification

update_trigger_audit.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
-- UPDATE Trigger Example 1: Comprehensive Audit Trail
-- PostgreSQL syntax
 
CREATE TABLE product_audit (
    audit_id SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL,
    field_name VARCHAR(64) NOT NULL,
    old_value TEXT,
    new_value TEXT,
    changed_by VARCHAR(128) DEFAULT CURRENT_USER,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    change_reason VARCHAR(255)
);
 
CREATE OR REPLACE FUNCTION audit_product_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Track each changed field individually
    IF OLD.product_name IS DISTINCT FROM NEW.product_name THEN
        INSERT INTO product_audit (product_id, field_name, old_value, new_value)
        VALUES (NEW.product_id, 'product_name', OLD.product_name, NEW.product_name);
    END IF;
    
    IF OLD.price IS DISTINCT FROM NEW.price THEN
        INSERT INTO product_audit (product_id, field_name, old_value, new_value)
        VALUES (NEW.product_id, 'price', OLD.price::TEXT, NEW.price::TEXT);
    END IF;
    
    IF OLD.quantity_in_stock IS DISTINCT FROM NEW.quantity_in_stock THEN
        INSERT INTO product_audit (product_id, field_name, old_value, new_value)
        VALUES (NEW.product_id, 'quantity_in_stock', 
                OLD.quantity_in_stock::TEXT, NEW.quantity_in_stock::TEXT);
    END IF;
    
    IF OLD.category_id IS DISTINCT FROM NEW.category_id THEN
        INSERT INTO product_audit (product_id, field_name, old_value, new_value)
        VALUES (NEW.product_id, 'category_id', 
                OLD.category_id::TEXT, NEW.category_id::TEXT);
    END IF;
    
    IF OLD.is_active IS DISTINCT FROM NEW.is_active THEN
        INSERT INTO product_audit (product_id, field_name, old_value, new_value)
        VALUES (NEW.product_id, 'is_active', 
                OLD.is_active::TEXT, NEW.is_active::TEXT);
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_audit_product_changes
    AFTER UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION audit_product_changes();

IS DISTINCT FROM vs. <> (Not Equals)

Use IS DISTINCT FROM when comparing columns that might contain NULL. The expression NULL <> NULL returns NULL (not TRUE), so changes from NULL to a value (or vice versa) won't be detected. NULL IS DISTINCT FROM 'value' returns TRUE, correctly detecting the change.

update_trigger_temporal.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
-- UPDATE Trigger Example 2: Temporal Table (History Tracking)
-- PostgreSQL syntax
 
-- Main table with current data
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    title VARCHAR(100),
    department_id INTEGER,
    salary DECIMAL(10,2),
    valid_from TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- Other columns...
    CONSTRAINT employees_unique_id UNIQUE (employee_id)
);
 
-- History table for all previous versions
CREATE TABLE employees_history (
    history_id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL,
    employee_name VARCHAR(100) NOT NULL,
    title VARCHAR(100),
    department_id INTEGER,
    salary DECIMAL(10,2),
    valid_from TIMESTAMP NOT NULL,
    valid_to TIMESTAMP NOT NULL,
    modified_by VARCHAR(128),
    modification_type VARCHAR(20)
);
 
CREATE OR REPLACE FUNCTION maintain_employee_history()
RETURNS TRIGGER AS $$
BEGIN
    -- Archive the OLD version to history
    INSERT INTO employees_history (
        employee_id,
        employee_name,
        title,
        department_id,
        salary,
        valid_from,
        valid_to,
        modified_by,
        modification_type
    ) VALUES (
        OLD.employee_id,
        OLD.employee_name,
        OLD.title,
        OLD.department_id,
        OLD.salary,
        OLD.valid_from,
        CURRENT_TIMESTAMP,
        CURRENT_USER,
        'UPDATE'
    );
    
    -- Update the valid_from timestamp for the new version
    NEW.valid_from := CURRENT_TIMESTAMP;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_maintain_employee_history
    BEFORE UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION maintain_employee_history();
 
-- Query to see an employee's history
-- SELECT * FROM employees_history 
-- WHERE employee_id = 123 
-- ORDER BY valid_from DESC;

update_trigger_validation.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- UPDATE Trigger Example 3: State Transition Validation
-- PostgreSQL syntax
 
-- Order status can only transition in specific ways:
-- pending -> confirmed -> processing -> shipped -> delivered
-- pending -> cancelled
-- confirmed -> cancelled
-- Any state -> 'on_hold' (reversible)
 
CREATE OR REPLACE FUNCTION validate_order_status_transition()
RETURNS TRIGGER AS $$
DECLARE
    v_valid_transitions TEXT[][];
    v_is_valid BOOLEAN := FALSE;
    i INTEGER;
BEGIN
    -- If status not changing, allow
    IF OLD.status = NEW.status THEN
        RETURN NEW;
    END IF;
    
    -- Define valid state transitions
    v_valid_transitions := ARRAY[
        ARRAY['pending', 'confirmed'],
        ARRAY['pending', 'cancelled'],
        ARRAY['confirmed', 'processing'],
        ARRAY['confirmed', 'cancelled'],
        ARRAY['processing', 'shipped'],
        ARRAY['processing', 'on_hold'],
        ARRAY['shipped', 'delivered'],
        ARRAY['shipped', 'on_hold'],
        ARRAY['on_hold', 'processing'],
        ARRAY['on_hold', 'shipped']
    ];
    
    -- Check if transition is valid
    FOR i IN 1..ARRAY_LENGTH(v_valid_transitions, 1) LOOP
        IF v_valid_transitions[i][1] = OLD.status 
           AND v_valid_transitions[i][2] = NEW.status THEN
            v_is_valid := TRUE;
            EXIT;
        END IF;
    END LOOP;
    
    IF NOT v_is_valid THEN
        RAISE EXCEPTION 'Invalid order status transition from "%" to "%"',
            OLD.status, NEW.status
            USING HINT = 'Check the allowed status transition rules';
    END IF;
    
    -- Record status change timestamp
    NEW.status_changed_at := CURRENT_TIMESTAMP;
    NEW.status_changed_by := CURRENT_USER;
    
    -- Specific actions for certain transitions
    IF NEW.status = 'shipped' THEN
        NEW.shipped_at := CURRENT_TIMESTAMP;
    ELSIF NEW.status = 'delivered' THEN
        NEW.delivered_at := CURRENT_TIMESTAMP;
    ELSIF NEW.status = 'cancelled' THEN
        NEW.cancelled_at := CURRENT_TIMESTAMP;
        -- Could trigger refund process here
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_validate_order_status
    BEFORE UPDATE OF status ON orders
    FOR EACH ROW
    EXECUTE FUNCTION validate_order_status_transition();

Column-Specific UPDATE Triggers

A powerful optimization is defining triggers that fire only when specific columns are modified. This reduces overhead for updates that don't affect the columns you're interested in.

Syntax:

-- PostgreSQL / Oracle syntax
CREATE TRIGGER trigger_name
    BEFORE UPDATE OF column1, column2, column3 ON table_name
    FOR EACH ROW
    EXECUTE FUNCTION trigger_function();

The trigger fires only if at least one of the specified columns is included in the UPDATE SET clause. Note that the trigger fires based on the SET clause, not whether the value actually changed (the value might be set to its existing value).

column_specific_trigger.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
-- Column-Specific Trigger: React only to salary changes
-- PostgreSQL syntax
 
CREATE OR REPLACE FUNCTION process_salary_change()
RETURNS TRIGGER AS $$
DECLARE
    v_change_pct DECIMAL(5,2);
BEGIN
    -- Calculate percentage change
    v_change_pct := ((NEW.salary - OLD.salary) / OLD.salary) * 100;
    
    -- Log salary change
    INSERT INTO salary_changes (
        employee_id,
        old_salary,
        new_salary,
        change_percentage,
        effective_date,
        approved_by
    ) VALUES (
        NEW.employee_id,
        OLD.salary,
        NEW.salary,
        v_change_pct,
        CURRENT_DATE,
        CURRENT_USER
    );
    
    -- If significant increase, notify HR
    IF v_change_pct > 15 THEN
        PERFORM pg_notify('salary_alerts', 
            JSON_BUILD_OBJECT(
                'employee_id', NEW.employee_id,
                'change_pct', v_change_pct,
                'new_salary', NEW.salary
            )::TEXT
        );
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- This trigger ONLY fires when salary is in the UPDATE SET clause
CREATE TRIGGER trg_salary_change
    AFTER UPDATE OF salary ON employees
    FOR EACH ROW
    EXECUTE FUNCTION process_salary_change();
 
-- These statements DO fire the trigger:
-- UPDATE employees SET salary = 75000 WHERE employee_id = 1;
-- UPDATE employees SET name = 'John', salary = 75000 WHERE employee_id = 1;
 
-- This statement does NOT fire the trigger:
-- UPDATE employees SET name = 'John Doe' WHERE employee_id = 1;

Column-Specific vs. Value Change Detection

Column-specific triggers fire when the column is mentioned in the SET clause, even if the value doesn't change (e.g., SET salary = salary). To react only to actual value changes, add a WHEN clause or check OLD.column IS DISTINCT FROM NEW.column in the trigger body.

column_specific_with_when.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Combining column-specific with WHEN clause for actual changes only
-- PostgreSQL syntax
 
CREATE TRIGGER trg_salary_change_actual
    AFTER UPDATE OF salary ON employees
    FOR EACH ROW
    WHEN (OLD.salary IS DISTINCT FROM NEW.salary)  -- Only if value actually changed
    EXECUTE FUNCTION process_salary_change();
 
-- PostgreSQL WHEN clause limitations:
-- - Cannot call user-defined functions
-- - Can only reference OLD and NEW
-- - For complex conditions, use trigger body instead
 
-- Alternative: Check in trigger body
CREATE OR REPLACE FUNCTION process_salary_change_v2()
RETURNS TRIGGER AS $$
BEGIN
    -- Skip if salary didn't actually change
    IF OLD.salary IS NOT DISTINCT FROM NEW.salary THEN
        RETURN NEW;
    END IF;
    
    -- Proceed with actual change processing
    -- ...
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DELETE Triggers: Preserving Departing Data

DELETE triggers fire when rows are removed from a table. Since the row is being deleted, only the OLD row is available—there is no NEW row.

Data Availability for DELETE:

Correlation	BEFORE DELETE	AFTER DELETE
OLD row	Available	Available
NEW row	Not available	Not available

Common Use Cases for DELETE Triggers:

Archival — Copying deleted rows to an archive table before removal
Soft delete — Converting delete to update (mark as deleted)
Cascade cleanup — Removing related data not covered by FK cascades
Audit logging — Recording that a row was deleted with its final values
Prevention — Blocking deletions that violate business rules
External notification — Alerting external systems of removals

delete_trigger_archive.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
-- DELETE Trigger Example 1: Comprehensive Archival
-- PostgreSQL syntax
 
-- Archive table mirrors the structure with metadata
CREATE TABLE customers_archive (
    archive_id SERIAL PRIMARY KEY,
    -- Original columns
    customer_id INTEGER NOT NULL,
    customer_name VARCHAR(100),
    email VARCHAR(255),
    created_at TIMESTAMP,
    -- ... all other customer columns
    
    -- Archive metadata
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    archived_by VARCHAR(128) DEFAULT CURRENT_USER,
    deletion_reason VARCHAR(255),
    original_data JSONB  -- Complete snapshot
);
 
CREATE OR REPLACE FUNCTION archive_deleted_customer()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO customers_archive (
        customer_id,
        customer_name,
        email,
        created_at,
        original_data,
        deletion_reason
    ) VALUES (
        OLD.customer_id,
        OLD.customer_name,
        OLD.email,
        OLD.created_at,
        TO_JSONB(OLD),  -- Store complete row as JSON
        COALESCE(
            CURRENT_SETTING('app.deletion_reason', TRUE),
            'Not specified'
        )
    );
    
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_archive_customer
    BEFORE DELETE ON customers
    FOR EACH ROW
    EXECUTE FUNCTION archive_deleted_customer();
 
-- Usage: Set reason before delete
-- SET app.deletion_reason = 'Customer requested account closure';
-- DELETE FROM customers WHERE customer_id = 123;

delete_trigger_cascade.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
-- DELETE Trigger Example 2: Custom Cascade Cleanup
-- PostgreSQL syntax
 
-- Scenario: When deleting a project, we need to:
-- 1. Archive all project files to cold storage (not just delete)
-- 2. Remove time entries but aggregate totals first
-- 3. Notify team members
-- These can't be done with simple FK ON DELETE CASCADE
 
CREATE OR REPLACE FUNCTION cleanup_project_deletion()
RETURNS TRIGGER AS $$
DECLARE
    v_total_hours DECIMAL(10,2);
    v_member_ids INTEGER[];
BEGIN
    -- 1. Archive project files to cold storage
    INSERT INTO archived_files (
        original_project_id,
        file_name,
        file_path,
        file_size,
        archived_at
    )
    SELECT 
        project_id,
        file_name,
        file_path,
        file_size,
        CURRENT_TIMESTAMP
    FROM project_files
    WHERE project_id = OLD.project_id;
    
    DELETE FROM project_files WHERE project_id = OLD.project_id;
    
    -- 2. Aggregate time entries before removal
    SELECT SUM(hours), ARRAY_AGG(DISTINCT user_id)
    INTO v_total_hours, v_member_ids
    FROM time_entries
    WHERE project_id = OLD.project_id;
    
    -- Store aggregated data
    INSERT INTO project_summaries (
        project_id,
        project_name,
        total_hours_logged,
        team_member_count,
        summarized_at
    ) VALUES (
        OLD.project_id,
        OLD.project_name,
        COALESCE(v_total_hours, 0),
        COALESCE(ARRAY_LENGTH(v_member_ids, 1), 0),
        CURRENT_TIMESTAMP
    );
    
    DELETE FROM time_entries WHERE project_id = OLD.project_id;
    
    -- 3. Queue notifications for team members
    IF v_member_ids IS NOT NULL THEN
        INSERT INTO notification_queue (user_id, notification_type, payload)
        SELECT 
            UNNEST(v_member_ids),
            'project_deleted',
            JSONB_BUILD_OBJECT(
                'project_id', OLD.project_id,
                'project_name', OLD.project_name,
                'deleted_at', CURRENT_TIMESTAMP,
                'deleted_by', CURRENT_USER
            );
    END IF;
    
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_cleanup_project
    BEFORE DELETE ON projects
    FOR EACH ROW
    EXECUTE FUNCTION cleanup_project_deletion();

delete_trigger_prevention.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- DELETE Trigger Example 3: Deletion Prevention
-- PostgreSQL syntax
 
CREATE OR REPLACE FUNCTION prevent_critical_deletion()
RETURNS TRIGGER AS $$
BEGIN
    -- Rule 1: Cannot delete system accounts
    IF OLD.account_type = 'system' THEN
        RAISE EXCEPTION 'Cannot delete system accounts. Account: %', OLD.username
            USING HINT = 'System accounts must be disabled, not deleted';
    END IF;
    
    -- Rule 2: Cannot delete accounts with recent activity
    IF EXISTS (
        SELECT 1 FROM activity_log 
        WHERE user_id = OLD.user_id 
        AND activity_time > CURRENT_TIMESTAMP - INTERVAL '90 days'
    ) THEN
        RAISE EXCEPTION 'Cannot delete account with activity in last 90 days'
            USING HINT = 'Disable the account instead, or wait for 90-day inactivity';
    END IF;
    
    -- Rule 3: Cannot delete accounts with outstanding balance
    IF OLD.balance <> 0 THEN
        RAISE EXCEPTION 'Cannot delete account with non-zero balance: %', OLD.balance
            USING HINT = 'Clear the balance before deletion';
    END IF;
    
    -- Rule 4: Admin accounts require super-admin permission
    IF OLD.role = 'admin' THEN
        IF CURRENT_USER NOT IN (
            SELECT username FROM users WHERE role = 'super_admin'
        ) THEN
            RAISE EXCEPTION 'Only super-admins can delete admin accounts';
        END IF;
    END IF;
    
    -- All checks passed
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_prevent_critical_user_deletion
    BEFORE DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION prevent_critical_deletion();

BEFORE DELETE for Control, AFTER DELETE for Cleanup

Use BEFORE DELETE when you need to prevent deletion, modify behavior, or access data before it's gone. Use AFTER DELETE for logging that the deletion occurred or for cleanup that should only happen after successful deletion.

Combined Event Triggers

Often, you need similar logic for multiple events. Rather than creating separate triggers, you can create a single trigger that handles INSERT, UPDATE, and DELETE—using conditionals to distinguish which event occurred.

Benefits of Combined Triggers:

Code consolidation — Single function for related logic
Consistent handling — Same patterns applied across events
Easier maintenance — One place to update logic
Audit efficiency — Single audit function for all operations

Event Detection by DBMS:

DBMS	How to Detect Event
PostgreSQL	TG_OP variable ('INSERT', 'UPDATE', 'DELETE')
Oracle	INSERTING, UPDATING, DELETING predicates (Boolean)
SQL Server	Check inserted and deleted pseudo-tables
MySQL	Separate triggers required (no combined syntax)

combined_trigger_postgresql.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
-- Combined Event Trigger: Comprehensive Audit
-- PostgreSQL syntax
 
CREATE OR REPLACE FUNCTION audit_all_changes()
RETURNS TRIGGER AS $$
DECLARE
    v_old_data JSONB;
    v_new_data JSONB;
    v_changed_fields TEXT[] := '{}';
    v_key TEXT;
BEGIN
    -- Prepare data based on operation type
    CASE TG_OP
        WHEN 'INSERT' THEN
            v_new_data := TO_JSONB(NEW);
            v_old_data := NULL;
            
        WHEN 'UPDATE' THEN
            v_old_data := TO_JSONB(OLD);
            v_new_data := TO_JSONB(NEW);
            
            -- Build list of changed fields
            FOR v_key IN SELECT JSONB_OBJECT_KEYS(v_old_data)
            LOOP
                IF v_old_data->v_key IS DISTINCT FROM v_new_data->v_key THEN
                    v_changed_fields := ARRAY_APPEND(v_changed_fields, v_key);
                END IF;
            END LOOP;
            
            -- Skip audit if nothing actually changed
            IF ARRAY_LENGTH(v_changed_fields, 1) IS NULL THEN
                RETURN NEW;
            END IF;
            
        WHEN 'DELETE' THEN
            v_old_data := TO_JSONB(OLD);
            v_new_data := NULL;
    END CASE;
    
    -- Insert audit record
    INSERT INTO audit_log (
        table_name,
        operation,
        record_id,
        old_data,
        new_data,
        changed_fields,
        session_user,
        client_ip,
        application_name,
        executed_at
    ) VALUES (
        TG_TABLE_NAME,
        TG_OP,
        COALESCE((NEW).id, (OLD).id),  -- Assumes 'id' column exists
        v_old_data,
        v_new_data,
        v_changed_fields,
        SESSION_USER,
        INET_CLIENT_ADDR(),
        CURRENT_SETTING('application_name', TRUE),
        CURRENT_TIMESTAMP
    );
    
    -- Return appropriate value
    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;
 
-- Single trigger handles all events
CREATE TRIGGER trg_audit_customers
    AFTER INSERT OR UPDATE OR DELETE ON customers
    FOR EACH ROW
    EXECUTE FUNCTION audit_all_changes();
 
-- Same function can be used for multiple tables!
CREATE TRIGGER trg_audit_orders
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION audit_all_changes();
    
CREATE TRIGGER trg_audit_products
    AFTER INSERT OR UPDATE OR DELETE ON products
    FOR EACH ROW
    EXECUTE FUNCTION audit_all_changes();

combined_trigger_oracle.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- Combined Event Trigger: Oracle syntax
CREATE OR REPLACE TRIGGER audit_employee_changes
    AFTER INSERT OR UPDATE OR DELETE ON employees
    FOR EACH ROW
DECLARE
    v_operation VARCHAR2(10);
    v_old_json CLOB;
    v_new_json CLOB;
BEGIN
    -- Determine operation type using predicates
    IF INSERTING THEN
        v_operation := 'INSERT';
        v_new_json := row_to_json_employee(:NEW);
    ELSIF UPDATING THEN
        v_operation := 'UPDATE';
        v_old_json := row_to_json_employee(:OLD);
        v_new_json := row_to_json_employee(:NEW);
    ELSIF DELETING THEN
        v_operation := 'DELETE';
        v_old_json := row_to_json_employee(:OLD);
    END IF;
    
    -- Can also check for specific column updates
    IF UPDATING('salary') THEN
        -- Special handling for salary changes
        INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_at)
        VALUES (:NEW.employee_id, :OLD.salary, :NEW.salary, SYSTIMESTAMP);
    END IF;
    
    -- General audit
    INSERT INTO audit_log (table_name, operation, old_data, new_data, changed_at)
    VALUES ('EMPLOYEES', v_operation, v_old_json, v_new_json, SYSTIMESTAMP);
END;
/

MySQL Limitation

MySQL does not support combined event triggers. You must create separate triggers for INSERT, UPDATE, and DELETE. To share logic, create a common stored procedure and call it from each trigger, passing parameters to indicate the operation type.

SQL Server's Set-Based Approach

SQL Server takes a fundamentally different approach to trigger data access. Instead of OLD and NEW row variables, it provides inserted and deleted pseudo-tables that contain all affected rows.

Key Differences:

Aspect	PostgreSQL/Oracle/MySQL	SQL Server
Access method	OLD.column, NEW.column	inserted table, deleted table
Granularity	Row-level or statement-level	Statement-level only (all triggers)
Data type	Single row record	Table (possibly multiple rows)
Event detection	TG_OP / INSERTING etc.	Check which pseudo-tables have data

Event Detection Logic:

Event Type	inserted table	deleted table
INSERT	Has new rows	Empty
DELETE	Empty	Has deleted rows
UPDATE	Has new values	Has old values

sqlserver_trigger_pattern.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- SQL Server: Set-Based Trigger Pattern
CREATE TRIGGER trg_audit_employee_changes
ON employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @operation VARCHAR(10);
    
    -- Determine operation type by checking pseudo-tables
    IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
        SET @operation = 'UPDATE';
    ELSE IF EXISTS(SELECT 1 FROM inserted)
        SET @operation = 'INSERT';
    ELSE IF EXISTS(SELECT 1 FROM deleted)
        SET @operation = 'DELETE';
    ELSE
        RETURN; -- No rows affected
    
    -- Handle INSERT: new rows in inserted table
    IF @operation = 'INSERT'
    BEGIN
        INSERT INTO audit_log (table_name, operation, record_id, new_data, audit_time)
        SELECT 
            'employees',
            'INSERT',
            employee_id,
            (SELECT * FROM inserted i2 WHERE i2.employee_id = i.employee_id FOR JSON AUTO),
            GETDATE()
        FROM inserted i;
    END
    
    -- Handle UPDATE: old values in deleted, new values in inserted
    IF @operation = 'UPDATE'
    BEGIN
        INSERT INTO audit_log (table_name, operation, record_id, old_data, new_data, 
                               changed_columns, audit_time)
        SELECT 
            'employees',
            'UPDATE',
            i.employee_id,
            (SELECT * FROM deleted d2 WHERE d2.employee_id = d.employee_id FOR JSON AUTO),
            (SELECT * FROM inserted i2 WHERE i2.employee_id = i.employee_id FOR JSON AUTO),
            -- Build list of changed columns
            STUFF((
                SELECT ',' + column_name
                FROM INFORMATION_SCHEMA.COLUMNS c
                WHERE c.TABLE_NAME = 'employees'
                  AND c.COLUMN_NAME NOT IN ('updated_at')  -- Exclude auto-updated
                  AND NOT (i.employee_id = i.employee_id)  -- Placeholder for actual comparison
                FOR XML PATH('')
            ), 1, 1, ''),
            GETDATE()
        FROM inserted i
        INNER JOIN deleted d ON i.employee_id = d.employee_id;
    END
    
    -- Handle DELETE: old rows in deleted table
    IF @operation = 'DELETE'
    BEGIN
        INSERT INTO audit_log (table_name, operation, record_id, old_data, audit_time)
        SELECT 
            'employees',
            'DELETE',
            employee_id,
            (SELECT * FROM deleted d2 WHERE d2.employee_id = d.employee_id FOR JSON AUTO),
            GETDATE()
        FROM deleted d;
    END
END;
GO

Set-Based Thinking

SQL Server triggers process all affected rows at once, not row-by-row. This is more efficient for bulk operations but requires set-based thinking. Use JOINs between inserted/deleted tables to correlate old and new values. Avoid assuming a single row—always handle multiple rows correctly.

Best Practices by Event Type

Each event type has patterns that work well and anti-patterns to avoid:

INSERT Best Practices

•Use BEFORE for computed defaults — Set timestamps, generate codes before the row is written
•Use AFTER for related records — Create child/sibling records after the parent ID is assigned
•Avoid querying newly inserted row — It's not committed yet; use NEW values directly
•Be cautious with sequences — BEFORE triggers may see different sequence values than AFTER
•Handle bulk inserts — Test trigger behavior with multi-row INSERT statements

INSERT Anti-Patterns

•Complex defaults in triggers — Use column DEFAULTs or generated columns instead when possible
•Accessing OLD in INSERT — OLD is not available; this will error or return NULL
•Cascading inserts across many tables — Consider stored procedures for complex multi-table operations

Summary: Event Types Mastered

Understanding how INSERT, UPDATE, and DELETE triggers differ is essential for effective trigger design. Let's consolidate:

Key Takeaways

•INSERT triggers have only NEW — Use for initialization, validation, and cascading record creation
•UPDATE triggers have both OLD and NEW — Use for change tracking, auditing, and state transition validation
•DELETE triggers have only OLD — Use for archiving, prevention, and cleanup
•Column-specific UPDATE triggers reduce overhead — Fire only when specified columns are in the SET clause
•Combined event triggers consolidate logic — Use TG_OP (or equivalents) to detect which event occurred
•SQL Server is set-based — inserted/deleted tables contain all affected rows, not one row at a time
•Use IS DISTINCT FROM — Correctly detect changes when NULL values are involved

What's Next:

With event types understood, the next page explores row-level versus statement-level triggers—the granularity at which triggers fire, and when to choose each approach.

Event Types Mastered

You now understand how INSERT, UPDATE, and DELETE triggers differ in data availability, typical use cases, and implementation patterns. This knowledge enables you to write triggers that correctly handle each event type and avoid common mistakes.