The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →A SQL trigger is database-defined code that runs automatically when a supported event occurs, such as inserting, updating or deleting rows. Triggers can enforce rules, maintain audit tables and synchronize related data, but they also create hidden execution paths. Use them when the rule must apply regardless of which application writes to the database; prefer a native constraint when the constraint can express it. Trigger syntax and behavior vary by engine and version, so verify your deployed release before copying any example.
What is a SQL trigger?
A trigger is attached to a database object and fired by an event. The event might be INSERT, UPDATE or DELETE on a table, an operation on a view, TRUNCATE in PostgreSQL, or a DDL or logon event in SQL Server. The trigger body can validate data, write an audit row, update a summary table or block the operation.
Unlike a procedure, application code does not call a trigger directly. The database invokes it as part of the statement that caused the event. That makes a trigger useful for cross-application rules, but it also means every writer, migration and maintenance script must account for its side effects.
When should you use a database trigger?
Good fits
- Audit data must be recorded for every writer, including scripts and administrative sessions.
- A related table must be maintained whenever a row changes and cannot be left to individual applications.
- A cross-table rule is more reliably enforced at the database boundary than in duplicated application code.
- A view needs custom behavior for writes; PostgreSQL and SQL Server support
INSTEAD OFtriggers for suitable views.
Check constraints first
Use a PRIMARY KEY, UNIQUE, NOT NULL, CHECK or foreign-key constraint when it expresses the rule. Constraints are visible in schema metadata and are normally easier for tools and future maintainers to reason about. A trigger is appropriate when the rule needs procedural work, another table, an audit record or behavior unavailable through a declarative constraint.
#1 Best Overall
Costs to account for
- Execution is implicit: an ordinary write can issue more SQL and fire additional triggers.
- Extra work affects write latency and may create locking or contention.
- Changes to trigger code require deployment, permissions and regression tests just like application code.
- Cascades and trigger-issued statements can invoke further triggers, producing recursion or unexpected ordering.
BEFORE, AFTER and INSTEAD OF: what changes?
| Timing | Typical purpose | Important qualification |
|---|---|---|
BEFORE |
Validate or transform values before the row operation completes. | Capabilities differ. SQLite warns that modifying or deleting the target row in a BEFORE UPDATE or BEFORE DELETE trigger has undefined results and recommends preferring AFTER triggers. |
AFTER |
Audit or maintain related data after successful work. | In SQL Server, AFTER follows statement execution, constraint checks and relevant cascade actions. In SQLite, it is generally the safer choice for row-side effects. |
INSTEAD OF |
Replace the requested operation, commonly for a view. | PostgreSQL documents INSTEAD OF as row-level and view-specific; SQL Server also supports it. It is not a portable table-trigger rule. |
Do not assume that a BEFORE trigger can repair every invalid value. MySQL performs basic column type checks before trigger activation, so a BEFORE trigger cannot turn a value invalid for the column type into a valid one. MySQL also stores the sql_mode active when the trigger is created and uses that mode when the trigger runs later.
Row-level versus statement-level triggers
A row-level trigger runs once for each affected row. A statement-level trigger runs once for the operation, even if the operation affects zero rows. PostgreSQL supports both scopes; its row triggers can inspect row values, while statement triggers can use transition relations where supported.
SQLite supports only row triggers for INSERT, UPDATE and DELETE. A statement affecting 10,000 rows therefore invokes the trigger 10,000 times. MySQL triggers are also defined for each affected row.
SQL Server DML triggers are statement-level. One statement can affect many rows, and the trigger sees them as the set in the inserted and deleted pseudo-tables. Never select a scalar variable from inserted as though only one row existed; write set-based logic instead.
Free tools Windows power users keep installed
One-click scans. No signup required.
How to write a trigger that handles multiple rows
SQL Server: aggregate the affected set
This example maintains a per-account order total. It handles one-row and multirow inserts because it groups the complete inserted set.
CREATE OR ALTER TRIGGER dbo.trg_OrderItems_UpdateAccountTotals
ON dbo.OrderItems
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
;WITH affected AS (
SELECT AccountId FROM inserted
UNION
SELECT AccountId FROM deleted
), totals AS (
SELECT oi.AccountId, SUM(oi.Amount) AS TotalAmount
FROM dbo.OrderItems AS oi
JOIN affected AS a ON a.AccountId = oi.AccountId
GROUP BY oi.AccountId
)
UPDATE a
SET a.OrderTotal = COALESCE(t.TotalAmount, 0)
FROM dbo.Accounts AS a
JOIN affected AS x ON x.AccountId = a.AccountId
LEFT JOIN totals AS t ON t.AccountId = a.AccountId;
END;The SQL Server documentation specifically recommends rowset-based logic instead of cursors for multirow DML triggers. Microsoft’s multirow trigger guidance shows why single-row assumptions fail.
PostgreSQL: choose row or statement scope deliberately
PostgreSQL trigger functions receive event data separately from ordinary function arguments. A typical audit trigger is row-level and AFTER UPDATE:
CREATE FUNCTION audit_customer_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO customer_audit(customer_id, old_name, new_name, changed_at)
VALUES (OLD.customer_id, OLD.name, NEW.name, clock_timestamp());
RETURN NEW;
END;
$$;
CREATE TRIGGER customer_audit_update
AFTER UPDATE ON customers
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION audit_customer_change();The WHEN condition compares stored row values. That is different from UPDATE OF name, which tests whether name appeared in the update command, not whether its value actually changed. PostgreSQL also supports statement triggers, transition relations and TRUNCATE triggers; these are PostgreSQL features, not portable SQL.
Are triggers the same in MySQL, PostgreSQL, SQLite and SQL Server?
No. The following differences are documented for the cited global software documentation; check the exact release installed in your environment.
| Engine and documentation | Scope and timing | Distinct behavior to verify |
|---|---|---|
| PostgreSQL 17/18 | Row and statement; BEFORE, AFTER, INSTEAD OF. |
Row triggers run per row; statement triggers run once, including zero-row operations. Supports transition relations, TRUNCATE triggers and multiple events with OR. Multiple triggers execute in name order, not creation order. CREATE TRIGGER reference and trigger behavior. |
| SQLite | Row-only; BEFORE or AFTER for INSERT, UPDATE and DELETE. |
No statement triggers. Unknown names in UPDATE OF column are silently ignored at creation. The language reference says, “programmers are encouraged to prefer AFTER triggers over BEFORE triggers.” SQLite CREATE TRIGGER. |
| MySQL 26.7 | BEFORE or AFTER, for each affected row. |
Multiple triggers may share event and timing; creation order is default, with FOLLOWS/PRECEDES. Definer privileges and creation-time sql_mode affect execution. MySQL CREATE TRIGGER. |
| SQL Server 17 | DML AFTER and INSTEAD OF; also DDL and logon triggers. |
DML trigger fires once per statement and uses inserted/deleted sets. TRUNCATE TABLE does not activate a trigger because it does not log individual row deletions. CREATE TRIGGER reference. |
Change detection: command intent versus stored values
Some syntaxes let you name target columns, but that does not necessarily mean a value changed. PostgreSQL’s UPDATE OF price fires when price is listed in the statement, even if the new value equals the old value. Use an old/new comparison such as OLD.price IS DISTINCT FROM NEW.price when the business rule concerns an actual value change. Equivalent null-safe comparisons differ by engine, so consult its expression syntax.
Cascades, recursion and referential integrity
Trigger-issued SQL can fire other triggers, including recursively. PostgreSQL documents no direct limit on cascade depth. Foreign-key cascade actions use ordinary updates or deletes on referencing tables; a trigger that blocks or modifies those operations can break referential integrity. Test a complete transaction containing the original write, cascades and all trigger side effects. Include rollback tests and verify behavior when zero rows are affected.
Permissions, ordering and deployment checks
- List every trigger on the target table or view before changing schema. Record timing, event, scope and execution order.
- For PostgreSQL, do not rely on creation order; same-kind triggers execute by name order.
- For MySQL, document the definer. If omitted, the creator becomes the default definer, and trigger-time privileges are checked against that account.
- Pin the database version in migrations and test with the same compatibility settings, including MySQL’s creation-time
sql_mode. - Grant only the privileges required by the trigger function or definer, and review ownership changes before production deployment.
Testing and troubleshooting
Trigger never fires
- Confirm the event and timing match the statement; a PostgreSQL
TRUNCATEtrigger is distinct from a rowDELETEtrigger. - Check scope: SQLite has no statement triggers, while SQL Server DML triggers do not run once per row.
- Verify the statement actually targets the table or view where the trigger is attached.
Only one row is processed
In SQL Server, replace scalar reads from inserted or deleted with joins, grouping or set-based updates. Test inserts, updates and deletes that affect multiple rows in one statement.
Recommended Free Tools
Unexpected recursion or constraint failure
Trace every SQL statement issued by the trigger and any nested trigger. Test foreign-key cascades and define an explicit recursion policy. If a trigger modifies the same table or a related table, ensure the operation converges and that rollback leaves no partial audit or summary data.
SQLite behavior is surprising
Inspect every name in an UPDATE OF list because unknown names are accepted silently. Avoid modifying or deleting the target row inside a BEFORE trigger; SQLite documents the result as undefined.
MySQL works differently after deployment
Compare the trigger’s definer and the sql_mode captured at creation with the account and settings expected in production. Recreate the trigger deliberately when those settings must change.
Or skip the browser setup
When you need a clean screenshot of SQL documentation, a schema diagram page or an admin runbook, ScreenshotNeo can return an image or PDF from one request. Its cleanup accepts cookie banners and removes more than 60 known consent platforms, newsletter popups and chat widgets before capture. Bot checks, blank pages, timeouts, failed loads and cache hits are not billed, and response headers identify the page verdict and billing status. An MCP server provides take_screenshot, get_page_info and capture_pdf tools for Claude, Cursor and other MCP clients. The free plan includes 1,000 screenshots per month with no card; paid plans start at $5 for 3,000 shots.
See the ScreenshotNeo API documentation for options such as full-page capture, CSS selectors, device presets, custom headers and cookies, PDF ranges, signed links, caching and bulk capture.
curl -G "https://api.screenshotneo.com/v1/shot" -d access_key=YOUR_API_KEY --data-urlencode url=https://www.postgresql.org/docs/17/sql-createtrigger.html -o shot.webpCreate a free ScreenshotNeo account to get 1,000 screenshots a month with no card.
Frequently Asked Questions
Can a trigger return a result set to the application?
Usually no: triggers participate in the modifying statement. Their supported return behavior and error signaling are engine-specific, so use the version’s trigger-function documentation.
Does TRUNCATE fire triggers?
It depends on the engine. PostgreSQL supports statement-level TRUNCATE triggers; SQL Server documents that TRUNCATE TABLE does not activate a trigger. SQLite and MySQL behavior must be checked in their respective references.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Should audit logic be a trigger or application code?
Use a trigger when every database writer must be covered. Use application logic when the event depends on context unavailable to the database or when explicit, visible control is more important.
The Bottom Line
Choose triggers for database-wide behavior that must run automatically, but design for the engine you actually deploy. Confirm timing, scope, multirow semantics, ordering, privileges, cascades and recursion in version-matched documentation and tests.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
