Eric Campbell 6 min read Databases

SQL Triggers: Event-Based Database Actions

Database triggers are stored procedures that execute automatically when specific events occur on a table or view. Unlike application code that you explicitly call, triggers respond to data...

Key Insights

  • Triggers are database objects that automatically execute code in response to INSERT, UPDATE, or DELETE events, enabling centralized enforcement of business rules and data integrity constraints at the database layer.
  • While triggers excel at audit logging, automatic timestamps, and maintaining denormalized data, they introduce hidden complexity that can make debugging difficult and impact performance when overused.
  • Row-level triggers execute once per affected row and provide OLD/NEW references for comparing values, while statement-level triggers fire once per SQL statement regardless of rows affected.

Introduction to Database Triggers

Database triggers are stored procedures that execute automatically when specific events occur on a table or view. Unlike application code that you explicitly call, triggers respond to data modification events—INSERT, UPDATE, or DELETE operations—making them a cornerstone of event-driven database architecture.

Triggers solve several critical problems. They enforce business rules at the database layer, ensuring consistency regardless of which application or user modifies the data. They maintain audit trails without requiring every application to implement logging logic. They keep denormalized data synchronized, automatically updating summary tables when detail records change.

The key advantage is centralization. When you implement validation or logging in a trigger, every INSERT statement—whether from your web app, a batch job, or a manual SQL query—follows the same rules. The disadvantage is invisibility. Triggers execute silently, making them harder to debug and creating “magic” behavior that surprises developers unfamiliar with the schema.

Trigger Anatomy and Syntax

Every trigger has three essential components: timing, event, and scope.

Timing determines when the trigger fires relative to the data modification. BEFORE triggers execute before the database applies changes, allowing you to modify values or prevent the operation entirely. AFTER triggers run after changes commit, suitable for logging or cascading updates.

Events specify which operations activate the trigger: INSERT, UPDATE, DELETE, or combinations thereof. You can create separate triggers for each event or handle multiple events in one trigger.

Scope defines execution frequency. Row-level triggers (FOR EACH ROW) fire once per affected row, providing access to individual record values. Statement-level triggers (FOR EACH STATEMENT) execute once per SQL statement, regardless of how many rows it affects.

Here’s a basic audit logging trigger in PostgreSQL:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users_audit (
    audit_id SERIAL PRIMARY KEY,
    user_id INTEGER,
    username VARCHAR(50),
    email VARCHAR(100),
    action VARCHAR(10),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION audit_user_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO users_audit (user_id, username, email, action)
    VALUES (NEW.id, NEW.username, NEW.email, 'INSERT');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_insert_audit
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION audit_user_insert();

This trigger captures every new user creation, storing a timestamped record in the audit table without requiring application code changes.

Practical Trigger Patterns

Triggers shine in scenarios requiring guaranteed consistency. Here are battle-tested patterns.

Automatic timestamp management eliminates the need for applications to set updated_at fields:

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

This BEFORE UPDATE trigger ensures updated_at always reflects the modification time, even if the UPDATE statement doesn’t mention it.

Soft delete archiving moves deleted records to an archive table instead of losing data:

CREATE TABLE users_archive (LIKE users INCLUDING ALL);

CREATE OR REPLACE FUNCTION archive_deleted_user()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO users_archive SELECT OLD.*;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER archive_user_on_delete
AFTER DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION archive_deleted_user();

Maintaining denormalized counts keeps aggregate data current without expensive queries:

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    title VARCHAR(200),
    content TEXT
);

ALTER TABLE users ADD COLUMN post_count INTEGER DEFAULT 0;

CREATE OR REPLACE FUNCTION update_user_post_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE users SET post_count = post_count + 1 WHERE id = NEW.user_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE users SET post_count = post_count - 1 WHERE id = OLD.user_id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER maintain_post_count_insert
AFTER INSERT ON posts
FOR EACH ROW
EXECUTE FUNCTION update_user_post_count();

CREATE TRIGGER maintain_post_count_delete
AFTER DELETE ON posts
FOR EACH ROW
EXECUTE FUNCTION update_user_post_count();

Working with OLD and NEW References

Row-level triggers access record data through special references. NEW contains the incoming row state for INSERT and UPDATE operations. OLD holds the previous state for UPDATE and DELETE operations. INSERT triggers only have NEW; DELETE triggers only have OLD; UPDATE triggers have both.

This trigger tracks specific field changes, logging only when the email address changes:

CREATE TABLE email_change_log (
    log_id SERIAL PRIMARY KEY,
    user_id INTEGER,
    old_email VARCHAR(100),
    new_email VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_email_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.email IS DISTINCT FROM NEW.email THEN
        INSERT INTO email_change_log (user_id, old_email, new_email)
        VALUES (NEW.id, OLD.email, NEW.email);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER track_email_updates
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION log_email_changes();

The IS DISTINCT FROM operator handles NULL values correctly, ensuring the log captures changes even when email transitions from NULL to a value or vice versa.

Triggers vs. Alternatives

Triggers aren’t always the right choice. Application-level logic offers better visibility and easier testing. Stored procedures provide explicit control over when business logic executes. CHECK constraints and foreign keys enforce simple rules more efficiently.

Use triggers when you need database-level guarantees that apply regardless of the client. Avoid triggers for complex business logic better suited to application code where you can unit test, debug, and version control more effectively.

Performance matters. Triggers add overhead to every data modification. A trigger that performs expensive calculations or queries other tables can slow INSERT/UPDATE/DELETE operations significantly. Profile your triggers and consider whether batch processing or materialized views might serve better.

Debugging trigger issues is painful. When an INSERT fails, is it a constraint violation or trigger logic? Application logs won’t show trigger execution paths. Use database logging and careful error handling in trigger functions.

Best Practices and Pitfalls

The most dangerous pitfall is recursive triggers—triggers that modify the same table they’re attached to, potentially creating infinite loops:

-- PROBLEMATIC: Can cause infinite recursion
CREATE OR REPLACE FUNCTION update_user_status()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE users SET status = 'modified' WHERE id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Prevent recursion with conditional logic:

-- SAFE: Checks before updating
CREATE OR REPLACE FUNCTION update_user_status()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status != 'modified' THEN
        NEW.status := 'modified';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Additional best practices:

  • Keep triggers simple. Complex logic belongs in stored procedures that triggers can call.
  • Document trigger behavior. Future developers need to know why triggers exist and what they do.
  • Handle errors explicitly. Use RAISE EXCEPTION to provide clear error messages.
  • Avoid trigger chains. When trigger A causes trigger B which causes trigger C, maintainability suffers.
  • Test thoroughly. Include trigger behavior in integration tests, not just unit tests.

Cross-Database Considerations

Trigger syntax varies across database systems. PostgreSQL requires separate functions, while MySQL embeds logic directly:

PostgreSQL:

CREATE OR REPLACE FUNCTION set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_timestamp();

MySQL:

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
SET NEW.updated_at = NOW();

SQL Server:

CREATE TRIGGER update_timestamp
ON users
AFTER UPDATE
AS
BEGIN
    UPDATE users
    SET updated_at = GETDATE()
    FROM users u
    INNER JOIN inserted i ON u.id = i.id;
END;

SQL Server uses special inserted and deleted tables instead of NEW/OLD references, and doesn’t support BEFORE triggers—only AFTER and INSTEAD OF.

Oracle supports compound triggers that combine multiple timing points in one object, reducing overhead. MySQL historically had limitations on trigger capabilities that newer versions have addressed.

When writing portable database code, abstract trigger logic into stored procedures and keep trigger definitions minimal. This makes cross-database migration easier and consolidates business logic in one place.

Triggers are powerful tools for maintaining data integrity and automating database-level operations. Use them judiciously, document them thoroughly, and always consider whether application-level alternatives might serve your needs better. When used appropriately, triggers provide bulletproof enforcement of critical business rules that transcend any single application.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.