How to Create a Trigger in SQL

Use a trigger when database logic should run automatically in response to table changes like inserts, updates, or deletes. Triggers are useful for auditing, denormalized counters, and enforcing automatic side effects close to the data.

What you’ll build or solve

You’ll learn how to create a trigger in SQL and connect it to insert or update events. You’ll also know when a trigger is better than application-layer logic.

When this approach works best

This approach is the right choice when logic must always run no matter which application writes to the database.

Common real-world scenarios include:

• Audit log entries
• Updated timestamp columns
• Inventory count adjustments
• Change history tables
• Security rule enforcement

This is a bad idea when hidden database-side side effects would make debugging harder than explicit application logic.

Prerequisites

You only need:

• Permission to create triggers
• A target table
• Basic procedural SQL knowledge for your database

Step-by-step instructions

Step 1: Create the trigger on a table event

The syntax varies by database. Here is a PostgreSQL-style example.

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

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

This automatically refreshes updated_at before every update.

What to look for:

• Triggers run automatically
• Tied to insert, update, or delete events
• Great for auditing and timestamps
• Hidden side effects need documentation
• Syntax differs by database

Examples you can copy

Audit inserts

AFTER INSERT ON orders

Auto-update timestamp

BEFORE UPDATE ON users

Inventory counter sync

AFTER DELETE ON order_items

Common mistakes and how to fix them

Mistake 1: Hiding critical business logic in triggers

What the reader might do:

Place checkout payment logic inside triggers.

Why it breaks: debugging becomes harder because side effects are invisible to application developers.

Corrected approach:

Keep critical workflows explicit in the app layer.

Mistake 2: Creating recursive trigger loops

What the reader might do:

A trigger updates the same table again.

Why it breaks: this can loop indefinitely.

Corrected approach:

Guard recursive updates carefully.

Mistake 3: Ignoring write amplification

What the reader might do:

Add many triggers on hot write tables.

Why it breaks: every write becomes slower.

Corrected approach:

Use only essential automatic logic.

Troubleshooting

If updates become slow, audit trigger execution cost.

If rows update repeatedly, check for recursion.

If debugging is hard, document trigger side effects clearly.

If the app already owns the logic, consider removing the trigger.

Quick recap

• Triggers run automatically on table events
• Great for timestamps and auditing
• Keep side effects documented
• Avoid recursive loops
• Use only essential automatic logic