SQL Triggers Explained: Automate Your Database Logic

Imagine this: a developer runs a bulk UPDATE in production. Prices get changed. No one notices for three days. Now your finance team is asking why revenue reports don't match.

You have no log. No timestamp. No way to roll it back.

SQL triggers could have saved you. They're the silent sentinels of your database — automatically executing logic every time specific data changes, without you having to call anything extra. In this guide, we'll build real, working triggers step by step and learn exactly when (and when not) to use them.

What Is a SQL Trigger?

A trigger is a special database object that automatically fires in response to a data-changing event on a table. That event is one of:

• INSERT — a new row is added
• UPDATE — an existing row is changed
• DELETE — a row is removed

And the trigger can fire either before or after the event:

You define a trigger once. The database then calls it automatically — every single time, for every matching row, with no extra code needed in your application.

Trigger Anatomy

Here's the basic structure of a trigger:

CREATE TRIGGER trigger_name
AFTER UPDATE ON target_table
FOR EACH ROW
WHEN NEW.price != OLD.price   -- optional filter
BEGIN
  -- your SQL logic here
  INSERT INTO audit_log (product_id, old_price, new_price)
  VALUES (OLD.id, OLD.price, NEW.price);
END;

The key pieces:

Keyword	What it does
AFTER / BEFORE	When the trigger fires, relative to the event
INSERT / UPDATE / DELETE	Which event activates the trigger
FOR EACH ROW	Run the trigger once per affected row (standard mode)
NEW.column	The incoming row value (available for INSERT and UPDATE)
OLD.column	The previous row value (available for UPDATE and DELETE)
WHEN condition	Optional filter — skip firing if condition is false

NEW and OLD are your window into the changing data. For a DELETE, only OLD is available. For an INSERT, only NEW is. For an UPDATE, you have both.

Example 1: Building an Audit Log

The most common trigger use case: recording what changed, when it changed, and what it looked like before.

We'll track price changes on a products table using an audit log:

Notice the WHEN NEW.price != OLD.price guard — without it, updating any column on that row (like stock) would also write to the audit log unnecessarily. The WHEN clause is one of the most useful trigger features, and it's easy to forget to add it.

Run the updates a few times. Notice that the audit log fills in automatically — no extra INSERT in your application code.

Example 2: Soft-Archiving Deleted Rows

Instead of letting rows vanish permanently, use a BEFORE DELETE trigger to capture a copy in an archive table before the deletion happens.

This pattern is often called a soft archive. The row is removed from the production table (keeping your queries fast and clean), but a snapshot lands in the archive table before the deletion completes — so nothing is truly lost.

Example 3: Maintaining a Running Total

Triggers can also keep a denormalized (pre-calculated) value synchronized with its source. Here, we maintain an orders table with a total column that automatically updates whenever a new item is added:

Add more items to the order and watch the total update instantly. Compare this to re-querying the entire order_items table on every page load — the trigger approach is much cheaper at read time.

Best Practices

Triggers are powerful and invisible — a dangerous combination if you're not careful. Follow these principles:

• Keep them small: One trigger should do exactly one thing. If your trigger logic is growing past 10–15 lines, consider moving the logic into application code.
• Name them descriptively: log_price_change is self-documenting. trg1 is not. Include the action and the table name.
• Always add a WHEN guard: Filter the trigger down to only the rows that actually need processing. A trigger on a high-write table that fires on every update (even irrelevant ones) adds measurable overhead.
• Leave a comment: Triggers are hidden from most developers who look at a table schema. Document why the trigger exists, not just what it does.
• Never chain triggers carelessly: A trigger that modifies the table it's watching can trigger itself recursively. Protect against this with WHEN conditions or database-level settings.
• Test with real data volumes: A trigger that works fine on 100 rows may become a bottleneck at 10 million rows. Benchmark before deploying to production.

When NOT to Use Triggers

Triggers are the right tool for many problems, but not all of them:

• Avoid triggers for application business rules: Authorization, validation, and workflow logic belong in your application, where they can be tested, version-controlled, and reasoned about by the whole team.
• Avoid triggers for complex multi-table logic: If your trigger needs to join five tables and write to three, that's a sign the logic should live in a service or stored procedure.
• Avoid triggers for high-throughput writes: If you're writing millions of rows per second, the overhead of firing triggers on every row can become a bottleneck. Consider async processing (message queues, CDC streams) instead.

Related Articles

• Mastering SQL Transactions for the all-or-nothing write safety that usually surrounds trigger execution.
• Simulating Materialized Views in SQLite with Triggers for a concrete high-leverage trigger pattern that shifts work from reads to writes.
• Mastering SQL Constraints for the simpler declarative rules you should prefer before reaching for procedural trigger logic.

Conclusion

SQL triggers are one of the most practical database features you're probably not using yet. The three patterns that deliver the most value in real applications are:

• Audit logging — track every change to critical data, automatically
• Soft archiving — preserve deleted rows before they're gone forever
• Denormalized totals — keep pre-calculated columns fresh without extra queries

Start with the audit log trigger. It's simple to build, immediately useful, and gives you visibility into your data that most applications completely lack.

---

Want to learn more about protecting data integrity? Check out our guide on SQL Constraints for the complementary, declarative approach to enforcing rules at the database level.