We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningFoundation · Database Fundamentals
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.
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.
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.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Database Trigger across a run of lessons, not one page. These 4 alone are about 151 minutes of step-by-step reading, 1 of them with code you run in the browser, all with a quiz.
Three triggers created in one order fired in another, three rows were inserted and two exist, and a TRUNCATE emptied an audited table without firing the audit trigger once. None of it raised an error.
Three thousand updates took 117 ms sent one at a time and 17 inside one CALL. A function that tries to COMMIT fails outright, and an EXCEPTION block inside a loop took 9 ms to 134 while catching nothing.
Two writers update the same Delta Lake table at once: who wins, and what does the loser see? I ran real writer processes against 3,475,226 NYC taxi trips, 5 runs per case. Two appends both committed every time. Two updates of different rows clashed every time, because they shared the same 9 files: one committed, the other got CommitFailedError. Splitting the table by the filter column let both commit. A check-then-append job started twice stored 317,150 trips for a day of 158,575 in 5 of 5 runs. In delta-rs 1.6.6, a WriteSerializable setting took effect only when spelled writeSerializable.
At seventy percent deleted the table with its indexes was 161.9 MB against 33.5 for the same live rows. A unique constraint refused an address a long-deleted row still holds.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with Database Triggerspay once,
yours for life
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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).
This page explains the idea. The "Database Triggers" lesson builds it up step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
Open the Database Triggers lessoncourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Database Trigger in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
