SQL Triggers: Tutorial with Practical Examples (2026)

What Are SQL Triggers?

A SQL trigger is a stored procedure that automatically executes when a specific event occurs on a table. Think of it as an event listener for your database: when a row is inserted, updated, or deleted, the trigger fires and runs the code you defined.

Triggers are part of the SQL standard and supported by every major relational database, including MySQL, PostgreSQL, SQL Server, Oracle, and SQLite. They execute inside the same transaction as the triggering statement, which means if the trigger fails, the entire operation rolls back.

When to use triggers

Triggers are most valuable when you need to enforce a rule at the database level, regardless of which application or user modifies the data. Common use cases include:

• Audit logging. Automatically record who changed what and when, without relying on application code to remember.
• Data validation. Enforce business rules that go beyond simple CHECK constraints, such as cross-table validation.
• Cascading updates. Automatically update related records in other tables when a source record changes.
• Automatic timestamps. Set created_at and updated_at fields without application involvement.
• Denormalization. Keep summary or cached columns in sync with their source data.

The key advantage is consistency. If three different applications write to the same table, a trigger guarantees the rule is always enforced. Application-level logic cannot make that guarantee unless every application implements the same rule correctly.

Types of SQL Triggers

Triggers are classified by when they fire relative to the triggering event and what event they respond to.

BEFORE triggers

A BEFORE trigger runs before the row is modified. This gives you a chance to inspect or change the incoming data before it hits the table. If you raise an error inside a BEFORE trigger, the operation is cancelled entirely.

Use BEFORE triggers for data validation, setting default values, and transforming input data.

AFTER triggers

An AFTER trigger runs after the row has been successfully modified. At this point, the new data is already in the table (within the current transaction). AFTER triggers are ideal for audit logging, notifications, and updating dependent tables.

INSTEAD OF triggers

An INSTEAD OF trigger replaces the triggering operation entirely. Instead of performing the INSERT, UPDATE, or DELETE, the database executes your trigger code. These are primarily used on views to make non-updatable views behave as if they were updatable. SQL Server and PostgreSQL support INSTEAD OF triggers; MySQL does not.

Row-level vs. statement-level triggers

A row-level trigger fires once for each row affected by the statement. A statement-level trigger fires once per statement, regardless of how many rows are modified. PostgreSQL supports both. MySQL only supports row-level triggers. SQL Server triggers are statement-level by default but provide access to the full set of affected rows through the inserted and deleted pseudo-tables.

Creating Triggers: Syntax by Database

The core concept is the same everywhere, but the syntax differs between database engines. Here is how to create a basic AFTER INSERT trigger in each major database.

MySQL

DELIMITER $$

CREATE TRIGGER trg_after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_audit (order_id, action, created_at)
    VALUES (NEW.id, 'INSERT', NOW());
END$$

DELIMITER ;

MySQL uses NEW to reference the incoming row on INSERT and UPDATE, and OLD to reference the existing row on UPDATE and DELETE. The DELIMITER change is required because the trigger body contains semicolons.

PostgreSQL

-- Step 1: Create the trigger function
CREATE OR REPLACE FUNCTION fn_after_order_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO order_audit (order_id, action, created_at)
    VALUES (NEW.id, 'INSERT', NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Step 2: Attach the trigger to the table
CREATE TRIGGER trg_after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION fn_after_order_insert();

PostgreSQL separates the trigger definition from the trigger function. The function must return TRIGGER and use RETURN NEW for INSERT/UPDATE or RETURN OLD for DELETE. For BEFORE triggers, modifying and returning NEW changes the data that gets written.

SQL Server

CREATE TRIGGER trg_after_order_insert
ON orders
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO order_audit (order_id, action, created_at)
    SELECT id, 'INSERT', GETDATE()
    FROM inserted;
END;

SQL Server does not use NEW and OLD. Instead, it provides two virtual tables: inserted (containing new rows) and deleted (containing old rows). For an UPDATE, both tables are populated. Triggers in SQL Server are statement-level, so you should always use SELECT FROM inserted instead of assuming a single row.

Practical Examples

1. Audit logging

Track every change to a sensitive table by recording the old values, new values, the user who made the change, and the timestamp. This is the most common trigger use case in production systems.

-- Audit table
CREATE TABLE employee_audit (
    audit_id SERIAL PRIMARY KEY,
    employee_id INT NOT NULL,
    field_name VARCHAR(50) NOT NULL,
    old_value TEXT,
    new_value TEXT,
    changed_by VARCHAR(100),
    changed_at TIMESTAMP DEFAULT NOW()
);

-- PostgreSQL trigger function
CREATE OR REPLACE FUNCTION fn_audit_employee_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.salary IS DISTINCT FROM NEW.salary THEN
        INSERT INTO employee_audit
            (employee_id, field_name, old_value, new_value, changed_by)
        VALUES
            (OLD.id, 'salary', OLD.salary::TEXT, NEW.salary::TEXT, current_user);
    END IF;

    IF OLD.department IS DISTINCT FROM NEW.department THEN
        INSERT INTO employee_audit
            (employee_id, field_name, old_value, new_value, changed_by)
        VALUES
            (OLD.id, 'department', OLD.department, NEW.department, current_user);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_employee
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION fn_audit_employee_changes();

The IS DISTINCT FROM operator handles NULL comparisons correctly, unlike != which returns NULL when either side is NULL. This ensures the audit log only records fields that actually changed.

2. Data validation

Enforce a business rule that cannot be expressed as a simple CHECK constraint. This BEFORE trigger prevents inserting an order with a total that exceeds the customer's credit limit.

-- MySQL BEFORE INSERT trigger
DELIMITER $$

CREATE TRIGGER trg_validate_order_credit
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE customer_credit DECIMAL(10,2);
    DECLARE outstanding_balance DECIMAL(10,2);

    SELECT credit_limit INTO customer_credit
    FROM customers
    WHERE id = NEW.customer_id;

    SELECT COALESCE(SUM(total_amount), 0) INTO outstanding_balance
    FROM orders
    WHERE customer_id = NEW.customer_id
      AND status IN ('pending', 'processing');

    IF (outstanding_balance + NEW.total_amount) > customer_credit THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Order exceeds customer credit limit';
    END IF;
END$$

DELIMITER ;

The SIGNAL statement in MySQL raises a user-defined error that aborts the INSERT and rolls back the transaction. In PostgreSQL, you would use RAISE EXCEPTION instead.

3. Cascading updates

When a product's price changes, automatically update the unit price on all open (unfulfilled) order line items that reference that product.

-- PostgreSQL cascading update trigger
CREATE OR REPLACE FUNCTION fn_cascade_price_update()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.price IS DISTINCT FROM NEW.price THEN
        UPDATE order_items
        SET unit_price = NEW.price,
            line_total = quantity * NEW.price
        WHERE product_id = NEW.id
          AND order_id IN (
              SELECT id FROM orders
              WHERE status IN ('draft', 'pending')
          );
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_cascade_product_price
AFTER UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION fn_cascade_price_update();

This trigger only updates line items on orders that have not been fulfilled yet. Completed orders retain their original pricing, which is the correct business behavior.

4. Automatic timestamps

Automatically set updated_at to the current time on every UPDATE, and ensure created_at is never overwritten.

-- PostgreSQL: auto-set updated_at
CREATE OR REPLACE FUNCTION fn_set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION fn_set_updated_at();

-- MySQL: auto-set updated_at
DELIMITER $$

CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END$$

DELIMITER ;

-- PostgreSQL: prevent created_at from being overwritten
CREATE OR REPLACE FUNCTION fn_protect_created_at()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.created_at IS DISTINCT FROM OLD.created_at THEN
        NEW.created_at = OLD.created_at;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_protect_created_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION fn_protect_created_at();

The updated_at trigger is one of the most reusable patterns in database design. In PostgreSQL, you can attach the same trigger function to multiple tables without duplicating code.

5. Maintaining summary tables

Keep a running count and total in a summary table whenever orders are inserted or deleted. This avoids expensive COUNT and SUM queries on large tables.

-- Summary table
CREATE TABLE customer_stats (
    customer_id INT PRIMARY KEY,
    total_orders INT DEFAULT 0,
    total_spent DECIMAL(12,2) DEFAULT 0
);

-- PostgreSQL: increment on INSERT
CREATE OR REPLACE FUNCTION fn_update_customer_stats_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO customer_stats (customer_id, total_orders, total_spent)
    VALUES (NEW.customer_id, 1, NEW.total_amount)
    ON CONFLICT (customer_id) DO UPDATE
    SET total_orders = customer_stats.total_orders + 1,
        total_spent = customer_stats.total_spent + NEW.total_amount;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_stats_after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION fn_update_customer_stats_insert();

-- PostgreSQL: decrement on DELETE
CREATE OR REPLACE FUNCTION fn_update_customer_stats_delete()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE customer_stats
    SET total_orders = total_orders - 1,
        total_spent = total_spent - OLD.total_amount
    WHERE customer_id = OLD.customer_id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_stats_after_order_delete
AFTER DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION fn_update_customer_stats_delete();

Triggers vs. Constraints vs. Application Logic

Triggers are not the only way to enforce rules. Understanding when to use each approach prevents over-engineering and keeps your system maintainable.

CHECK constraints are the simplest option. They validate a single row against a condition (e.g., price > 0, status IN ('active', 'inactive')). Use them whenever the rule can be expressed as a boolean expression on one row. They are fast, declarative, and self-documenting.

FOREIGN KEY constraints enforce referential integrity between tables. They guarantee that a referenced row exists and can cascade deletes or set nulls automatically. Always prefer foreign keys over triggers for simple reference checking.

UNIQUE constraints and indexes prevent duplicate values. Use them instead of a trigger that checks for duplicates before inserting.

Triggers handle everything else at the database level: cross-table validation, audit logging, complex transformations, and maintaining derived data. They are more powerful than constraints but harder to debug and test.

Application logic is the right choice when the rule requires external context (API calls, user sessions, feature flags) or when the rule is specific to one application and not a universal data integrity concern.

A practical rule of thumb: if the rule protects data integrity and must hold true regardless of how data enters the database, put it in the database (constraint or trigger). If the rule is a business workflow that depends on application context, put it in the application.

Common Pitfalls and Performance Considerations

Triggers are powerful, but they introduce hidden complexity. Here are the most common mistakes and how to avoid them.

1. Cascading trigger chains

Trigger A modifies table B, which fires Trigger B, which modifies table C, which fires Trigger C. These chains are difficult to trace, debug, and predict. MySQL limits recursion depth to prevent infinite loops. PostgreSQL allows it but will error on infinite recursion. SQL Server has a RECURSIVE_TRIGGERS database option.

Best practice: keep trigger logic self-contained. If you find yourself building chains of triggers that fire each other, consider refactoring into a stored procedure that handles the entire workflow explicitly.

2. Performance overhead on bulk operations

A row-level trigger fires once per row. If you INSERT 100,000 rows, the trigger executes 100,000 times. Each execution may run additional queries, multiplying the total work. For bulk data loads, consider temporarily disabling triggers:

-- PostgreSQL: disable a trigger
ALTER TABLE orders DISABLE TRIGGER trg_after_order_insert;

-- Load data
COPY orders FROM '/data/orders.csv' CSV HEADER;

-- Re-enable
ALTER TABLE orders ENABLE TRIGGER trg_after_order_insert;

-- MySQL: disable all triggers (requires SUPER privilege)
SET @DISABLE_TRIGGERS = 1;
-- (Check this variable inside your trigger to skip logic)

-- SQL Server: disable a trigger
DISABLE TRIGGER trg_after_order_insert ON orders;
-- Load data...
ENABLE TRIGGER trg_after_order_insert ON orders;

3. Hidden dependencies

Triggers do not appear in the application code. A developer modifying the orders table may not realize that five different triggers fire on every INSERT. Document your triggers thoroughly and maintain a trigger inventory as part of your schema documentation.

4. Error handling inside triggers

An unhandled error inside a trigger rolls back the entire transaction, including the original statement. This is usually the correct behavior for data integrity, but it can surprise developers who expect their INSERT to succeed even if the audit log table is full or has a schema mismatch.

5. Testing triggers

Triggers are difficult to unit test because they require a live database with the trigger installed. Write integration tests that perform the triggering operation and verify both the primary effect (row inserted) and the trigger side effect (audit row created). Test with NULLs, empty strings, and edge-case values.

6. Modifying the triggering table inside the trigger

MySQL does not allow a trigger to modify the same table that fired it. PostgreSQL allows it but doing so can cause infinite recursion if not handled carefully. SQL Server allows it with the RECURSIVE_TRIGGERS option. As a general rule, avoid modifying the triggering table inside the trigger. Use a BEFORE trigger to modify the incoming row via NEW instead.

Managing Existing Triggers

Once triggers are in production, you need to inspect, modify, and occasionally remove them.

Listing triggers

-- MySQL: list all triggers in the current database
SHOW TRIGGERS;

-- PostgreSQL: list all triggers
SELECT trigger_name, event_manipulation, event_object_table, action_timing
FROM information_schema.triggers
WHERE trigger_schema = 'public';

-- SQL Server: list all triggers
SELECT name, parent_class_desc, type_desc, is_disabled
FROM sys.triggers;

Viewing trigger source code

-- MySQL
SHOW CREATE TRIGGER trg_after_order_insert;

-- PostgreSQL (view the function body)
SELECT pg_get_functiondef(p.oid)
FROM pg_proc p
JOIN pg_trigger t ON t.tgfoid = p.oid
WHERE t.tgname = 'trg_after_order_insert';

-- SQL Server
EXEC sp_helptext 'trg_after_order_insert';

Dropping a trigger

-- MySQL
DROP TRIGGER IF EXISTS trg_after_order_insert;

-- PostgreSQL
DROP TRIGGER IF EXISTS trg_after_order_insert ON orders;

-- SQL Server
DROP TRIGGER IF EXISTS trg_after_order_insert;

In PostgreSQL, dropping a trigger does not drop the associated function. You need to drop the function separately with DROP FUNCTION IF EXISTS fn_after_order_insert().

Frequently Asked Questions

What is a SQL trigger?

A SQL trigger is a stored procedure that automatically executes in response to specific events on a table or view, such as INSERT, UPDATE, or DELETE operations. Triggers run before or after the event and are commonly used for audit logging, data validation, enforcing business rules, and maintaining data integrity without changing application code.

What is the difference between BEFORE and AFTER triggers?

BEFORE triggers execute before the triggering statement modifies the table, allowing you to validate or modify the incoming data before it is written. AFTER triggers execute after the statement completes, making them ideal for audit logging, sending notifications, or updating related tables. BEFORE triggers can prevent the operation by raising an error, while AFTER triggers react to changes that have already been committed.

Can triggers hurt database performance?

Yes. Because triggers run automatically on every qualifying row operation, they add overhead to INSERT, UPDATE, and DELETE statements. Poorly designed triggers that perform heavy queries, call external services, or fire additional triggers (cascading) can significantly degrade performance. Keep trigger logic minimal, avoid nested triggers when possible, and always test with realistic data volumes.

Should I use triggers or handle logic in my application code?

Use triggers when the rule must be enforced regardless of how data enters the database, such as audit trails or referential integrity that spans multiple applications. Use application logic when the rule is specific to one application, requires complex business context, or needs to interact with external services. Many teams use a combination: triggers for data-level guarantees and application code for business workflows.

How do I debug a SQL trigger that is not working?

Start by checking if the trigger exists and is enabled using your database's system catalog (e.g., SHOW TRIGGERS in MySQL or pg_trigger in PostgreSQL). Insert a test row and verify the trigger fires by checking the target table or log table. Use RAISE NOTICE (PostgreSQL) or SELECT into a debug table to inspect variable values inside the trigger. Also check for errors in the database error log and ensure the trigger's event type (INSERT, UPDATE, DELETE) matches your operation.