Database Trigger
A piece of SQL that automatically executes in response to INSERT, UPDATE, or DELETE events on a table. Useful for audit logs and enforcing complex constraints.
What is Database Trigger?
In short
A database trigger is a block of code stored inside the database that runs automatically when a specific event happens on a table, such as an INSERT, UPDATE, or DELETE. It lets the database itself enforce rules, write audit records, or keep derived data in sync without the application asking for it.
What a database trigger actually is
A trigger is a named piece of logic attached to a table and bound to one or more events. You tell the database something like: every time a row is inserted into orders, run this code first. From then on the database fires that code on its own. The application does not call it and usually does not know it exists.
Every trigger has three parts. The timing says whether it runs BEFORE or AFTER the event. The event says what it reacts to: INSERT, UPDATE, or DELETE. The scope says whether it runs once per affected row (a row-level trigger) or once per statement (a statement-level trigger). A single UPDATE that touches 500 rows fires a row-level trigger 500 times but a statement-level trigger once.
Inside the trigger body you get access to the data involved. Postgres exposes the values as NEW and OLD records, MySQL uses NEW and OLD too, and SQL Server gives you inserted and deleted pseudo-tables. NEW holds the row as it will be after the change, OLD holds the row as it was before, so an UPDATE trigger can compare them.
How it works under the hood
Triggers run inside the same transaction as the statement that fired them. If your trigger raises an error, the whole statement rolls back, including the original write. That is what makes triggers reliable for enforcing rules: there is no window where the table is updated but the trigger has not run yet.
A BEFORE trigger can change the data on its way in. Set NEW.updated_at to the current time, lowercase an email, or reject the row by raising an exception. An AFTER trigger cannot change the row anymore because it has already been written, so it is used for side effects: copying the change into an audit table, updating a running total elsewhere, or sending an event to a queue table.
In Postgres the common pattern is a trigger function written in PL/pgSQL plus a CREATE TRIGGER statement that wires the function to the table. MySQL inlines the body directly in the CREATE TRIGGER. Either way the code is compiled and stored in the database, so it survives application restarts and runs no matter which client made the change: your API, a migration script, or someone typing SQL by hand.
When to use them and the trade-offs
Triggers shine for audit logging and for invariants that must hold no matter who writes the data. If five different services and a nightly batch job all touch the accounts table, a trigger guarantees every change gets logged, where application-level logging only covers the code paths someone remembered to instrument.
The cost is that logic hidden in the database is easy to forget. A developer reading the application code sees an INSERT and has no idea a trigger also fired, wrote three audit rows, and bumped a counter. Debugging gets harder, and the surprise often surfaces as a mysterious performance problem because every write now does extra work.
Performance is the main trade-off. A row-level trigger on a bulk load can turn one fast COPY into millions of function calls. Triggers also make it tempting to put business logic in two places at once, the app and the database, which drifts apart over time. A common rule of thumb: use triggers for cross-cutting data integrity and auditing, keep real business workflows in the application.
A concrete example
Say you run an e-commerce site and need an audit trail of every price change on the products table for compliance. You create an AFTER UPDATE row-level trigger. When a price changes, the trigger compares OLD.price and NEW.price, and if they differ it inserts a row into price_history with the old value, the new value, the product id, and now().
Now it does not matter whether the price was changed by the admin dashboard, a bulk import, or a developer running a one-off UPDATE in psql at 2 AM. The history table fills itself, the original UPDATE and the history INSERT either both commit or both roll back, and no application code had to remember to log anything.
A second classic use is enforcing a constraint a plain CHECK cannot express, like preventing an order from being marked shipped before it is paid. A BEFORE UPDATE trigger inspects the row and raises an exception if NEW.status is shipped while NEW.paid_at is null, blocking the bad write at the source.
Where it is used in production
PostgreSQL
Triggers call PL/pgSQL functions and power features like full-text search index updates and logical replication change capture.
MySQL
Supports BEFORE and AFTER row-level triggers on INSERT, UPDATE, and DELETE; widely used for audit tables and maintaining denormalized counts.
Supabase
Builds its realtime and auth features on Postgres triggers, including a trigger that copies new auth.users rows into a public profiles table.
Oracle Database
Enterprise systems lean on Oracle triggers for compliance auditing and to maintain materialized aggregate tables across many writing applications.
Frequently asked questions
- What is the difference between a BEFORE and an AFTER trigger?
- A BEFORE trigger runs before the row is written and can modify or reject the incoming data using the NEW values. An AFTER trigger runs once the change is committed to the table and is used for side effects like writing to an audit table, since it can no longer alter the row.
- Do triggers slow down the database?
- They add work to every matching write, so yes, they can. A row-level trigger on a bulk insert of a million rows fires a million times. For high-throughput writes or large loads, check whether a statement-level trigger or application logic is cheaper.
- What are NEW and OLD in a trigger?
- They are references to the row data involved in the event. OLD holds the row before the change and NEW holds it after. INSERT triggers only have NEW, DELETE triggers only have OLD, and UPDATE triggers have both so you can compare them. SQL Server uses inserted and deleted pseudo-tables instead.
- When should I avoid using triggers?
- Avoid them for core business workflows or anything a developer would expect to find in the application code, because hidden logic is hard to debug and easy to forget. Keep triggers for cross-cutting concerns like auditing and data integrity that must apply to every writer.
- Are triggers part of the same transaction as the original statement?
- Yes. A trigger runs inside the transaction that fired it. If the trigger raises an error, the original statement and any other changes in that transaction roll back together, so the table is never left in a half-updated state.
Learn Database Trigger hands-on
This page explains the idea. The full lesson lets you step through the ring as servers join and leave, read the implementation, and check yourself with a quiz. It is one of 760+ lessons in the System Design Masterclass, from your first API call to distributed consensus. Eleven Foundation lessons are free, no signup. Lifetime access is ₹499 in India or $7.99 worldwide, one payment, no subscription.
Related lessons
Lessons that touch on Database Trigger as part of a larger topic.
See also
Related glossary terms you might want to look up next.
Stored Procedure
Pre-compiled SQL code stored in the database that can be called by name. Reduces network round trips and keeps business logic close to the data.
SQL
Structured Query Language for managing relational databases. Tables, rows, columns, and powerful joins to query related data.
Event-Driven Architecture
A design pattern where services communicate by producing and consuming events rather than making direct calls. Promotes loose coupling and asynchronous processing.
Database
An organized collection of data that can be easily accessed, managed, and updated. The backbone of almost every application.
NoSQL
Databases that don't use traditional table-based relational models. Includes document stores, key-value, graph, and column-family databases.
ACID
Four guarantees for database transactions: Atomicity (all or nothing), Consistency (valid states only), Isolation (no interference), Durability (changes persist).