A database trigger is procedural code that is automatically executed in response to specific events on a table or view in a database. Triggers are commonly used to maintain data integrity within a database. For example, inserting a new employee record can automatically generate related entries in the tax, vacation, and salary tables. Similarly, triggers enable historical data logging, such as tracking charges to employee salaries over time.
Triggers in database management systems (DBMS) Below is an overview of how several common DBMSs support triggers.
Oracle In addition to standard data manipulation language (DML) triggers that execute PL/SQL code when data is modified, Oracle supports triggers that fire on schema-level changes and system-wide database events. Both schema-level data definition language (DDL) triggers and system-event triggers were introduced together in Oracle 8i.
Schema-level triggers
The execution of standard Oracle DML triggers is defined by granularity (how often the trigger executes) and event filtering (what specific change activates it):
By granularity
Row-level: Executes once for each individual row affected by an INSERT, UPDATE, or DELETE statement (defined using the clause). If a statement modifies fifty rows, the trigger executes fifty times. Statement-level: Executes exactly once for the entire SQL statement, regardless of how many rows are modified (the default behavior or defined using ). It fires even if zero rows are affected by the operation.
By event filtering
Table-level: Activates on any standard , , or operation targeting the table as a whole. Column-level: A specialized filter applied to an trigger that restricts execution so it only fires if a specific, named column is modified (defined using the syntax).
Schema-level and system-level triggers
Schema-level triggers (DDL): Fire when schema objects are altered via commands such as , , , or . System-level triggers: Fire in response to operational database events, including user LOGON, user , database , and database .
Microsoft SQL Server
Microsoft SQL Server supports triggers for DML and DDL statements, as well as a special "logon" trigger.
The scope of a DDL trigger can be a single database or the entire SQL Server instance . When configured for the entire instance, the trigger captures both server-level and database-level events executed within that instance.
A complete list of available DDL trigger firing events is available in the official Microsoft documentation.
SQL Server DDL triggers can span a single database or an entire instance. For DML operations, and pseudo-tables are used, and while triggers are statement-level, row-level logic can be achieved via a cursor.
PostgreSQL PostgreSQL added trigger support in 1997, later including column-specific triggers in version 9.0 in full compliance with the SQL:2003 standard. While standard DML triggers are tired to specific tables, non-DML schema modifications—such as —are captured globally at the database level by Event Triggers, a feature introduced in PostgreSQL 9.3.
The basic SQL syntax for defining a trigger in PostgreSQL follows this structure:
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } ON table_name [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE funcname ( arguments )
Firebird Firebird supports multiple row-level triggers per table for , , and operations, as well as any combination of these actions. These execute either before or after data modifications and function in addition to default table changes. Users can specify the execution order of multiple triggers relative to one another using the clause. Triggers may also be defined on views as triggers, which replace the default updatable view logic. Prior to version 2.1, triggers on updatable views executed in addition to the default engine logic rather than replacing it.
The database engine allows triggers to nest and recurse by default, and it does not raise mutating table exceptions. Firebird utilizes the and context variables to access row states during data modifications. It also provides the boolean context flags , , and flags to programmatically identify the specific DML event that initiated the trigger execution.
{CREATE | RECREATE | CREATE OR ALTER} TRIGGER name FOR {table name | view name} [ACTIVE | INACTIVE] {BEFORE | AFTER} {INSERT [OR UPDATE] [OR DELETE] | UPDATE [OR INSERT] [OR DELETE] | DELETE [OR UPDATE] [OR INSERT] } [POSITION n] AS BEGIN .... END
Starting with version 2.1, Firebird introduced support for database-level triggers, which execute during specific session and transaction events. These include connection events, via and triggers, and transactional events, via , , and triggers. If an exception is raised during a trigger, the connection sequence is aborted. Similarly, exceptions raised within a trigger block the completion of the transaction, including the preparation phase during a two-phase commit.
Database-level triggers can be utilized to enforce multi-table constraints or to emulate materialized views. When an exception is raised inside a trigger, the transactional modifications made by the trigger prior to the error are rolled back and the client application is notified. However, the transaction itself remains active as if the operation had never been initiated, allowing the application to perform further data modifications and request the commit sequence.
The general syntax for creating or altering database-level triggers in Firebird uses the following structure:
{CREATE | RECREATE | CREATE OR ALTER} TRIGGER name [ACTIVE | INACTIVE] ON {CONNECT | DISCONNECT | TRANSACTION START | TRANSACTION COMMIT | TRANSACTION ROLLBACK} [POSITION n] AS BEGIN ..... END
MySQL/MariaDB MySQL/MariaDB triggers, introduced in 2005 (v5.0), support DML events, with MySQL 8.0 enabling multiple triggers of the same type, though DDL triggers are not supported. The syntax, compatible with both systems, utilizes , , and for row-level actions, with bodies starting with or .
IBM DB2 LUW IBM Db2 for Linux, Unix, and Windows (Db2 for LUW) supports three primary trigger types classified by activation time: , , and . The engine supports both row-level and statement-level granularities. If multiple triggers are defined for the same operation on a single table, their execution order is determined sequentially by their creation timestamp. Starting with version 9.7, Db2 also supports autonomous transactions, allowing a trigger to execute independent transactional work that can be committed or rolled back without affecting the triggering statement.
A trigger is used to validate incoming data and determine whether a data modification event should be permitted. If an exception is raised within a trigger, the operation is aborted and the database changes are rolled back. While triggers cannot execute DML statements to modify database tables directly, they are not completely read-only; they can programmatically modify incoming data states by altering the target values within the row's transition variables before they are written to disk.
Conversely, triggers are executed following the completion of the requested data modification. Unlike triggers, triggers are fully capable of executing subsequent DML write operations across any database table, including the active table that initiated the trigger execution. triggers are used exclusively on views to override the default processing logic and make non-updatable views writable. Procedural logic within Db2 triggers is typically written using the SQL PL procedural language extension.
SQLite
CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] [database_name .] trigger_name [BEFORE | AFTER | INSTEAD OF] {DELETE | INSERT | UPDATE [OF column_name [, column_name]...]} ON {table_name | view_name} [FOR EACH ROW] [WHEN condition] BEGIN ... END
SQLite supports only row-level triggers and does not provide native support for statement-level triggers.
Updatable views, which are also unsupported by default within the engine, can be emulated by implementing triggers over target view structures.
XML databases Triggers are implemented within non-relational database management systems. For example, the XML database Sedna supports triggers utilizing XQuery. Triggers in Sedna are designed to mirror the structural behavior of standard SQL:2003 triggers, but they are natively optimized around XML query and update paradigms, including XPath, XQuery, and XML update extensions.
A trigger in Sedna can be assigned to arbitrary nodes within an XML document stored in the database. When these targeted nodes undergo modification, the trigger automatically executes the corresponding XQuery queries or update scripts defined within its action body. For example, the following trigger blocks the deletion of a node if any open auctions continue to reference that specific profile: CREATE TRIGGER "trigger3" BEFORE DELETE ON doc("auction")/site//person FOR EACH NODE DO
if (exists($WHERE//open_auction/bidder/personref/@person=$OLD/@id)) then ( ) else $OLD;
MongoDB database triggers In MongoDB Atlas, triggers are implemented as part of a serverless, event-driven architecture to execute automated server-side logic when data changes occur. Unlike traditional database triggers, which run directly on the database server, MongoDB Atlas Database Triggers operate on an independently scaling compute layer. They monitor real-time data modifications (, , , or ) using . When an event is detected, the trigger automatically executes a custom Atlas Function (written in JavaScript) or sends the event to an external service. This functionality is primarily used to enforce data consistency and data integrity, and to implement auditing processes automatically across the data ecosystem.
An online store tracking package deliveries may use a database trigger to notify customers whenever their shipments change locations. The trigger monitors data changes within the collection, where each active order is stored as a document structured like the following example:
{ _id: ObjectId("59cf1860a95168b8f685e378"), customerId: ObjectId("59cf17e1a95168b8f685e377"), orderDate: ISODate("2018-06-26T16:20:42.313Z"), shipDate: ISODate("2018-06-27T08:20:23.311Z"), orderContents: [ { qty: 1, name: "Earl Grey Tea Bags - 100ct", price: NumberDecimal("10.99") } ], shippingLocation: [ { location: "Memphis", time: ISODate("2018-06-27T18:22:33.243Z") }, ] }
Architecture and execution
Granularity and execution timing To understand how database triggers work, it is important to look at two key concepts: trigger granularity (row-level versus statement-level) and execution timing (BEFORE versus AFTER). These settings determine how many times a trigger runs and exactly when its code executes.
Row-level versus statement-level The main difference between these two trigger types is how often they run when data is modified:
Row-level triggers execute once for every single row affected by a database command, such as an , , or statement. For example, if an command changes 50 rows in a table, a row-level trigger will run 50 separate times. If the command does not match any rows and changes nothing, a row-level trigger will not run at all. Statement-level triggers execute exactly once for the entire SQL statement, regardless of how many rows are changed. Even if the database command affects zero rows, a statement-level trigger will still run its code one time.
Trigger execution granularity varies by engine, with systems like PostgreSQL using for row-level triggers, while Microsoft SQL server defaults to statement-level execution and requires handling rows via pseudo-tables. Developers must avoid trigger recursion, where a trigger updates a table that triggers itself, causing infinite loops and potential database crashes.
BEFORE versus AFTER