Recommended Free Tools
A SQL trigger is database-defined code that runs automatically when a supported event—such as an insert, update, or delete—occurs. Whether it runs before or after a change, once per row or once per statement, and on which database objects depends on the database engine. Before writing one, check your engine and version, consider whether a constraint can express the rule, and design explicitly for statements that affect multiple rows.
What is a SQL trigger?
A trigger is behavior attached to a database event. When that event occurs, the database invokes the trigger automatically; application code does not need to call it directly. Depending on the engine, events may include data changes to tables or views, schema changes, logons, or other operations. The supported event and object combinations are not universal across SQL databases.
Triggers are useful when a rule must run regardless of which application or process makes a change. They can, for example, record changes in an audit table or enforce a cross-table rule that a native constraint cannot express. Their automatic nature also means the behavior can be less visible to developers tracing a write. Treat each trigger as part of the write path: document it, test it, and account for its effects on transactions, cascades, and performance.
Should you use a trigger?
Start with the rule, then choose the simplest database mechanism that enforces it. If a native constraint can express an integrity requirement, prefer evaluating that first: constraints make the rule explicit in the schema and avoid an additional execution path. A trigger is a candidate when the behavior must happen for every qualifying database event, or when the rule cannot be represented by the available constraints.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →#1 Best Overall
- Good fit: database-wide audit recording or behavior that must accompany writes from multiple clients.
- Consider another mechanism: a straightforward uniqueness, nullability, or referential rule that the engine can enforce with a constraint.
- Review carefully: triggers that update the same table, issue further writes, depend on ordering, or interact with cascading foreign-key actions.
Before implementation, identify the event, target object, timing, scope, old and new values needed, expected number of affected rows, permissions, and possible secondary writes. Then verify the exact syntax and behavior for the deployed engine and version.
BEFORE, AFTER, and INSTEAD OF: when does the trigger run?
| Timing | General meaning | Important qualification |
|---|---|---|
| BEFORE | Runs before the triggering row operation is completed. | Whether the trigger can change values, and when type or constraint checks occur, varies by engine. |
| AFTER | Runs after the relevant operation, subject to engine-specific rules about statement execution and constraints. | Do not infer identical semantics across engines. SQL Server documents AFTER DML triggers as following successful statement execution, including relevant cascade actions and constraint checks. |
| INSTEAD OF | Runs in place of the triggering operation. | Availability depends on engine and object type. PostgreSQL documents row-level INSTEAD OF triggers on views; SQL Server supports INSTEAD OF DML triggers. |
For PostgreSQL, a trigger can use a condition comparing old and new row values to log only actual changes: WHEN (OLD.* IS DISTINCT FROM NEW.*) on an appropriate UPDATE trigger. That is different from UPDATE OF column_name, which tests whether a column was named as an update target, not whether its stored value changed. See PostgreSQL 17’s CREATE TRIGGER reference.
Row-level and statement-level triggers
Scope determines how often trigger logic runs and what data it can inspect. A row-level trigger runs for each affected row. A statement-level trigger runs for the operation as a whole. This distinction is critical for bulk updates and deletes: one SQL statement may affect many rows.
| Engine and documented version | Scope and behavior |
|---|---|
| PostgreSQL 17 | Supports row- and statement-level triggers. Row triggers run once per affected row; statement triggers run once per operation, even if zero rows are affected. INSTEAD OF triggers are row-level; TRUNCATE triggers are statement-level. |
| SQLite | Supports row triggers only for INSERT, UPDATE, and DELETE. A trigger runs once per affected row; there are no statement-level triggers. |
| MySQL 26.7 manual | BEFORE and AFTER triggers run for each affected row. |
| SQL Server 17 documentation | A DML trigger fires once for the statement. The affected rows are available as sets through inserted and, for relevant operations, deleted. |
These are product-specific facts, not interchangeable SQL rules. PostgreSQL’s documentation also describes transition relations for access to sets of changed rows under supported trigger forms. Consult the relevant engine reference before choosing a trigger function or body.
How to handle statements that affect multiple rows
Never assume an UPDATE or DELETE affects one row unless the database operation itself guarantees that. SQL Server is especially important here: its DML trigger runs once for a statement, and inserted and deleted can contain multiple rows. Microsoft recommends rowset-based logic rather than cursors for this work.
For example, a SQL Server trigger should aggregate or join the inserted set, not select a single value from it into a scalar variable and assume that value represents every changed row. The following is a shape-only example: replace the table and column names with actual schema names, and define the audit-table columns to match your requirements.
CREATE TRIGGER dbo.LogWidgetUpdates
ON dbo.Widget
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.WidgetAudit (WidgetId, NewName)
SELECT i.WidgetId, i.Name
FROM inserted AS i;
END;This set-based insert processes all rows in inserted. It intentionally does not show old-versus-new change filtering, because that rule depends on the audit requirement; use a join between inserted and deleted when comparing before and after values. Verify column types, schema, permissions, and SQL Server version before deploying. Microsoft’s guide to handling multiple rows in DML triggers explains the set-based approach.
Engine-specific differences to check
PostgreSQL 17 and 18
PostgreSQL supports BEFORE, AFTER, and INSTEAD OF timing, row and statement scope, conditions, transition relations, and TRUNCATE triggers. Multiple triggers of the same kind are ordered by name rather than creation time. PostgreSQL permits one trigger to cover multiple events using OR, and its TRUNCATE trigger support is an extension beyond the SQL standard. Trigger functions receive event data separately from ordinary function arguments. Check the PostgreSQL 17 trigger syntax and the PostgreSQL 18 overview of trigger behavior for the version you run.
Free tools Windows power users keep installed
One-click scans. No signup required.
SQLite
SQLite supports BEFORE and AFTER row triggers for INSERT, UPDATE, and DELETE, but not statement-level triggers. OLD and NEW are available according to the event. One subtle hazard: misspelled or unknown names in an UPDATE OF column list are silently ignored when the trigger is created. SQLite also warns that modifying or deleting the target row from a BEFORE UPDATE or BEFORE DELETE trigger has undefined results, including uncertainty about whether corresponding AFTER triggers run. Its official language reference says, “programmers are encouraged to prefer AFTER triggers over BEFORE triggers.” Read the SQLite CREATE TRIGGER reference.
MySQL 26.7
The MySQL 26.7 manual documents BEFORE and AFTER row triggers. Multiple triggers can share an event and timing; creation order is the default, and FOLLOWS or PRECEDES can control order. Basic column value checks occur before trigger activation, so a BEFORE trigger cannot turn a value invalid for the column type into a valid one. MySQL stores the sql_mode active when a trigger is created and later executes the body using that mode. If a DEFINER is specified, trigger-time privileges are checked against that account; otherwise, the creator is the default definer. See MySQL 26.7 CREATE TRIGGER.
SQL Server 17
SQL Server DML triggers support AFTER and INSTEAD OF. DDL and logon triggers are also available, but have different purposes and syntax. SQL Server’s reference says TRUNCATE TABLE does not activate a trigger because the operation does not log individual row deletions. Do not transfer this limitation to another engine; PostgreSQL, for example, documents TRUNCATE triggers. See Microsoft’s SQL Server 17 CREATE TRIGGER reference.
Recursion, cascades, and integrity
A trigger is not necessarily an isolated callback. SQL issued by a trigger can fire other triggers, including recursively. PostgreSQL documents no direct limit on cascade depth. Referential cascade actions use ordinary update or delete operations on referencing tables, so a trigger that modifies or blocks those operations can interfere with referential integrity. The exact cascade and recursion behavior is engine-specific; test the complete transaction path, not just a single direct write. PostgreSQL explains these interactions in its trigger behavior documentation.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →- Map every table a trigger reads or writes, including tables with their own triggers.
- Test multirow statements and foreign-key cascade paths.
- Check for cycles, duplicate audit entries, and unintended repeated updates.
- Confirm what happens when trigger work fails: whether the original statement or transaction is rolled back is an important operational outcome to verify for your engine.
Permissions, ordering, and deployment checks
Trigger correctness includes the context in which it runs. Ordering can depend on names, creation order, or explicit ordering clauses. Privileges may be checked against a definer account, and session settings may be captured when the trigger is created. Those details affect reproducibility between development and production, especially in MySQL.
- Record the database product and exact installed version; documentation versions cited here are PostgreSQL 17 and 18, MySQL 26.7, SQLite’s language reference, and SQL Server 17.
- Confirm the event, table or view, timing, and row or statement scope supported by that version.
- Review trigger ordering, execution identity, required permissions, and captured settings.
- Test inserts, updates, deletes, zero-row statements, multirow statements, cascades, and expected failures.
- Deploy schema and trigger changes together, and include trigger behavior in rollback and migration plans.
Troubleshooting common trigger problems
| Symptom | Likely cause or check | Practical fix |
|---|---|---|
| Only one changed record is handled | Logic assumes one row, especially in a SQL Server trigger. | Use set-based logic over inserted/deleted; in row-trigger engines, verify per-row side effects and costs. |
| An UPDATE trigger runs but no value changed | UPDATE OF checks whether a column was targeted, not whether its value differs. | Compare old and new values using engine-appropriate syntax; PostgreSQL supports the documented OLD/NEW distinctness condition. |
| A SQLite trigger accepts a misspelled UPDATE OF column | SQLite silently ignores unknown column names in that list. | Review the trigger definition against the actual table schema and add a test that updates the intended column. |
| A BEFORE trigger produces unpredictable row behavior in SQLite | It changes or deletes the target row during BEFORE UPDATE or BEFORE DELETE. | Prefer an AFTER trigger, consistent with SQLite’s official guidance. |
| A MySQL value correction fails before trigger code runs | The value fails basic column checks before trigger activation. | Validate or transform input before assignment, or choose an appropriate schema and application validation strategy. |
| Trigger behavior differs across environments | Different engine versions, trigger ordering, definer permissions, or MySQL sql_mode captured at creation. | Compare deployed definitions and session/security settings; recreate or migrate deliberately under the intended configuration. |
| A cascade causes unexpected writes or failures | Trigger-issued SQL or trigger logic interacts with referential actions. | Trace the complete cascade path and test integrity outcomes in a transaction before deployment. |
Performance and reliability considerations
Every trigger adds work to the operation that fires it. A row trigger may execute repeatedly for a large statement; statement-level behavior may avoid repeated setup but requires set-oriented handling. There is no universal trigger-performance figure: actual cost depends on the engine, workload, trigger body, indexes, and affected rows. Measure with representative data and production-like query patterns rather than relying on a generic benchmark.
Keep trigger bodies focused, avoid unnecessary writes, and ensure queries inside them have appropriate indexes. Include trigger-generated work in transaction duration and lock analysis. For reliability, make side effects deliberate, document hidden write paths, and ensure monitoring and tests cover the database behavior rather than only application code.
Frequently asked questions
Are SQL triggers the same in MySQL, PostgreSQL, SQLite, and SQL Server?
No. Timing, scope, supported events and objects, ordering, permissions, and edge cases differ. Treat trigger syntax as engine-specific and consult documentation for the deployed version.
Best Value
Can an SQL trigger run when no rows match?
It depends on scope and engine. PostgreSQL statement-level triggers run once per operation even when zero rows are affected; row-level triggers do not run without affected rows.
Does TRUNCATE activate triggers?
Not universally. PostgreSQL supports statement-level TRUNCATE triggers, while SQL Server documents that TRUNCATE TABLE does not activate a trigger. Check the target engine’s reference.
A separate tool for website screenshots
ScreenshotNeo is a website screenshot API and MCP server for developers, made by Yorker Media. It is not a SQL trigger tool; it is relevant only if your application also needs website captures. One GET request can return a PNG, JPEG, WebP, or PDF. Its clean-shot options accept cookie or consent banners and remove more than 60 known consent platforms, newsletter popups, and chat widgets before capture; each step can be disabled. Bot checks, blank pages, timeouts, failed loads, and cache hits are not billed, with verdict and billing information in response headers. It also provides an MCP server with tools for AI agents, including Claude, Cursor, and other MCP clients.
For that separate capture task, see the ScreenshotNeo documentation. Plans include 1,000 shots per month free with no card, and paid plans start at $5 for 3,000 shots.
curl -G "https://api.screenshotneo.com/v1/shot" -d access_key=YOUR_API_KEY --data-urlencode url=https://stripe.com -o shot.webpSign up for ScreenshotNeo’s free plan: 1,000 screenshots a month, no card required.
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.
