A trigger is database logic that runs automatically when a specified event occurs. Updating a product's price might also write an audit record. The application issues one update, while the database performs the attached action as part of handling that event.
This can be useful when a rule must apply to every writer, including scripts and administrative tools. It also makes behavior less visible to someone reading only the application query. A good trigger therefore has a narrow purpose, documented effects and tests for multi-row operations. This reference expands the original triggers lesson with use cases and a complete PostgreSQL audit example.
Choose a rule that belongs near the data
Useful trigger cases include recording selected changes, maintaining carefully designed derived data and enforcing rules that cannot be expressed with simpler constraints. Before writing one, check whether a CHECK, UNIQUE, foreign key or default already expresses the requirement. Declarative rules are often easier to inspect and maintain.
An audit trigger can record which row changed and the before-and-after values. It should not be presented as a complete security audit system by itself. Privileged users may be able to modify the trigger or audit table, and the database identity may be an application service account rather than the human who initiated the action.
Avoid sending email or calling external services directly from an ordinary data-change trigger. Those actions introduce failure, latency and rollback problems beyond the transaction. Writing a transactional outbox row for a separate worker can provide a clearer coordination boundary when an external notification is required.
Document the triggering event, timing, granularity, changed tables and failure behavior. A reviewer should be able to understand the extra work an update causes without searching through an undocumented collection of routines.
Timing and granularity matter
A BEFORE trigger can inspect or, where supported, adjust a row before the change is applied. An AFTER trigger observes the applied change and can perform related work. Some engines support INSTEAD OF triggers for certain objects, commonly views. The available combinations are engine-specific.
A row-level trigger runs for each affected row. A statement-level trigger runs for an event statement and must account for the affected set. PostgreSQL supports both forms. MySQL data-change triggers are row-level. SQL Server triggers use inserted and deleted sets that can contain many rows, so writing scalar single-row assumptions is a common bug. SQLite has its own supported trigger forms.
An update that changes twenty products needs correct behavior for twenty products. Tests that update only one row cannot establish that. Even a PostgreSQL row trigger should be tested with multi-row statements because its repeated work and interactions may reveal performance or consistency issues.
Trigger order, recursion and cascading changes also vary by engine. A trigger that updates another table may activate another trigger. Keep that chain understandable and verify how the database limits or configures recursive behavior.
Practice: verify changes and nonchanges
After running the example, update the product from 12.00 to 12.00. The comparison should prevent an extra audit record because the price did not change. Then update to 15.00 inside a transaction and roll it back. The product price and audit count should return to the committed state.
Add two more products and change all three prices in one statement. Verify that each actual change receives an audit record and that a product whose price stays the same does not. Compare the recorded old and new values against the source table's expected history.
For a fuller audit design, decide which actor identity the application supplies and how it is protected from forgery. Define whether administrators can modify audit rows, how long records remain and how the system detects missing coverage. These choices are part of the audit requirement, not details that a trigger automatically resolves.
SQL Triggers and Their Use Cases Examples
Each example shows the code, the output it produces and what each part does.
Audit a PostgreSQL price change
This example uses PostgreSQL trigger functions and row-level AFTER UPDATE syntax. It is not MySQL or SQL Server trigger syntax. Run it in a test database with appropriate creation privileges.
OLD represents the row before the update and NEW represents its new form. The NULL-aware comparison avoids a fragile ordinary inequality if the schema later permits missing prices. The audit table intentionally remains small for this exercise; a production design needs event time, actor context, retention and access rules.
CREATE TABLE trigger_products (
product_id INTEGER PRIMARY KEY,
price numeric(10,2) NOT NULL CHECK (price >= 0)
);
CREATE TABLE price_audit (
product_id INTEGER NOT NULL,
old_price numeric(10,2),
new_price numeric(10,2)
);
CREATE FUNCTION record_price_change()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
IF OLD.price IS DISTINCT FROM NEW.price THEN
INSERT INTO price_audit(product_id, old_price, new_price)
VALUES (NEW.product_id, OLD.price, NEW.price);
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER audit_product_price
AFTER UPDATE ON trigger_products
FOR EACH ROW EXECUTE FUNCTION record_price_change();
INSERT INTO trigger_products VALUES (1, 10.00);
UPDATE trigger_products SET price = 12.00 WHERE product_id = 1;
SELECT product_id, old_price, new_price FROM price_audit;product_id | old_price | new_price
1 | 10.00 | 12.00SQL Triggers and Their Use Cases FAQ
Should triggers replace application validation?
No. User-friendly validation in the application gives clear messages before data is sent, while database integrity rules, constraints first and triggers where needed, protect the data from every writer. They serve complementary purposes.
Does an AFTER trigger run after the transaction commits?
Not simply because it says AFTER. Its timing is relative to the triggering event; transaction semantics still apply.
Can a trigger handle multiple rows?
It must handle the engine's event granularity correctly. In SQL Server, assume the affected sets can contain multiple rows.
What is the difference between a row-level and a statement-level trigger?
A row-level trigger, FOR EACH ROW, runs once for every affected row and can read OLD and NEW. A statement-level trigger runs once per statement, however many rows changed, which suits summary actions. SQL Server only has statement-level triggers, using the inserted and deleted tables.
How do I disable a trigger temporarily?
PostgreSQL uses ALTER TABLE sales DISABLE TRIGGER audit_sales;, and SQL Server uses DISABLE TRIGGER. MySQL and SQLite can't disable triggers, so you drop and recreate them. Disabling skips the trigger's checks or audit records, so do it only in controlled maintenance.
SQL Triggers and Their Use Cases Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
An audit trigger should record actual price changes only. Which test is essential?
SQL Triggers and Their Use Cases Quiz
2 questions to check what you learned.
What activates a database trigger?
- A reader opening any HTML page
- A configured database event such as an insert, update or delete
- Only an explicit CALL from the application
Why is a SQL Server trigger written for one scalar inserted row risky?
- SQL Server forbids every multi-row update
- Triggers run only when a table is empty
- One statement can populate inserted and deleted with multiple rows