Database trigger

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

• BEFORE CREATE
• AFTER CREATE
• BEFORE ALTER
• AFTER ALTER
• BEFORE DROP
• AFTER DROP

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 FOR EACH ROW 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 FOR EACH STATEMENT). It fires even if zero rows are affected by the operation.

By event filtering

• Table-level: Activates on any standard INSERT, UPDATE, or DELETE operation targeting the table as a whole.
• Column-level: A specialized filter applied to an UPDATE trigger that restricts execution so it only fires if a specific, named column is modified (defined using the UPDATE OF column_name syntax).

Schema-level and system-level triggers

• Schema-level triggers (DDL): Fire when schema objects are altered via commands such as AFTER, CREATE, BEFORE ALTER, or AFTER DROP.
• System-level triggers: Fire in response to operational database events, including user LOGON, user LOGOFF, database STARTUP, and database SHUTDOWN.

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 CREATE TRIGGER...ON DATABASE or the entire SQL Server instance CREATE TRIGGER...ON ALL SERVER. 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, inserted and deleted 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 CREATE TABLE—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 INSERT, UPDATE, and DELETE 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 POSITION clause. Triggers may also be defined on views as INSTEAD OF 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 NEW and OLD context variables to access row states during data modifications. It also provides the boolean context flags INSERTING, UPDATING, and DELETING 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 CONNECT and DISCONNECT triggers, and transactional events, via TRANSACTION START, TRANSACTION COMMIT, and TRANSACTION ROLLBACK triggers. If an exception is raised during a CONNECT trigger, the connection sequence is aborted. Similarly, exceptions raised within a TRANSACTION COMMIT 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 TRANSACTION COMMIT 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 COMMIT 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 CREATE TRIGGER, DROP TRIGGER, and FOR EACH ROW for row-level actions, with bodies starting with SET or BEGIN.

IBM DB2 LUW

IBM Db2 for Linux, Unix, and Windows (Db2 for LUW) supports three primary trigger types classified by activation time: BEFORE, AFTER, and INSTEAD OF. 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 BEFORE trigger is used to validate incoming data and determine whether a data modification event should be permitted. If an exception is raised within a BEFORE trigger, the operation is aborted and the database changes are rolled back. While BEFORE 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, AFTER triggers are executed following the completion of the requested data modification. Unlike BEFORE triggers, AFTER triggers are fully capable of executing subsequent DML write operations across any database table, including the active table that initiated the trigger execution. INSTEAD OF 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 INSTEAD OF 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 person 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 (INSERT, UPDATE, REPLACE, or DELETE) using Change Streams. 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 store.orders 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 INSERT, UPDATE, or DELETE statement. For example, if an UPDATE 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 EACH ROW 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

The timing options, specified by using the BEFORE and AFTER keywords, determine when the trigger runs relative to the data modification:

• BEFORE triggers execute their code before the data changes are permanently saved to the table. A common use for a BEFORE trigger is input data validation or security checks. For example, a BEFORE trigger can check an incoming record and either fix incorrect values or stop the operation entirely before any data is written to the disk.
• AFTER triggers execute their code after the data changes have already been safely written to the table. These triggers are typically used for post-processing tasks, like logging historical information or creating audit trails. While BEFORE triggers are best for checking and cleaning incoming data, AFTER triggers are ideal for copying confirmed changes over to separate history or logging tables.

Example implementation (Oracle PL/SQL)

The following example shows an Oracle PL/SQL row-level trigger configured to run after an UPDATE operation on a table named phone_book. This trigger automatically records historical changes by writing a log entry into a separate tracking table named phone_book_audit. It also uses a database sequence named audit_id_sequence to generate a unique ID number for each new log entry:

CREATE OR REPLACE TRIGGER phone_book_audit
  AFTER UPDATE ON phone_book FOR EACH ROW
BEGIN
  INSERT INTO phone_book_audit 
    (audit_id,audit_change, audit_l_name, audit_f_name, audit_old_phone_number, audit_new_phone_number, audit_date) 
    VALUES
    (audit_id_sequence.nextVal,'Update', :OLD.last_name, :OLD.first_name, :OLD.phone_number, :NEW.phone_number, SYSDATE);
END;

An UPDATE command executed against the phone_book table modifies records where the last name is "Jones":

UPDATE phone_book SET phone_number = '111-111-1111' WHERE last_name = 'Jones';

Audit_ID	Audit_Change	Audit_L_Name	Audit_F_Name	Audit_Old_Phone_Number	Audit_New_Phone_Number	Audit_Date
1	Update	Jones	Jordan	098-765-4321	111-111-1111	02-MAY-14
2	Update	Jones	Megan	111-222-3456	111-111-1111	02-MAY-14

The resulting phone_book_audit table is populated with two distinct entries because the source database contains two records matching the surname "Jones". Because a row-level trigger executes once for each modified row, the engine invokes the trigger logic twice during the execution of a single update statement.

AFTER statement-level trigger

Conversely, an AFTER statement-level trigger executes exactly once per statement completion, regardless of the total number of rows modified by the command. The following Oracle syntax defines a statement-level trigger that records a single transaction event within the phone_book_edit_history table whenever an UPDATE operation occurs on the phone_book table:

CREATE OR REPLACE TRIGGER phone_book_history
  AFTER UPDATE ON phone_book
BEGIN
  INSERT INTO phone_book_edit_history 
    (audit_history_id, username, modification, edit_date) 
    VALUES
    (audit_history_id_sequence.nextVal, USER,'Update', SYSDATE);
END;

Executing the identical UPDATE statement isolates the behavioral contrast of a statement-level trigger:

UPDATE phone_book SET phone_number = '111-111-1111' WHERE last_name = 'Jones';

Audit_History_ID	Username	Modification	Edit_Date
1	BONJOOR	Update	02-MAY-14

The transaction log indicates that the trigger executed only once, despite the data modification affecting two individual records.

BEFORE row-level trigger with conditional filtering

A BEFORE row-level trigger evaluates and alters incoming data values prior to the physical write operation completing on disk. This variation demonstrates a trigger utilizing a WHEN clause constraint to intercept an INSERT action. When a record features a surname string length that exceeds 10 characters, the engine invokes the SUBSTR function to truncate the input value to its initial character:

CREATE OR REPLACE TRIGGER phone_book_insert
  BEFORE INSERT ON phone_book FOR EACH ROW
  WHEN (LENGTH(new.last_name) > 10)
BEGIN
    :new.last_name := SUBSTR(:new.last_name,0,1);
END;

The mechanism is observed when attempting to insert a record that satisfies the filter criteria:

INSERT INTO phone_book VALUES
(6, 'VeryVeryLongLastName', 'Erin', 'Minneapolis', 'MN', '989 University Drive', '123-222-4456', 55408, TO_DATE('11/21/1991', 'MM/DD/YYYY'));

Person_ID	Last_Name	First_Name	City	State_Abbreviation	Address	Phone_Number	Zip_code	DOB
6	V	Erin	Minneapolis	MN	989 University Drive	123-222-4456	55408	21-NOV-91

The engine commits the row data with the truncated surname variable, confirming the modification occurred before the record was written to the table.

BEFORE statement-level trigger and exception raising

Implementing a BEFORE statement-level trigger is a standard design pattern for enforcing security parameters or business rule restrictions globally across a database resource. The following definition restricts database access on a target table under a specific schema:

CREATE OR REPLACE TRIGGER hauschbc 
  BEFORE INSERT ON SOMEUSER.phone_book
BEGIN
    RAISE_APPLICATION_ERROR (
         num => -20050,
         msg => 'Error message goes here.');
END;

An attempted data insertion targeted at the protected resource causes the database engine to abort the transaction sequence and return an error message to the calling client application interface:

SQL Error: ORA-20050: Error message goes here.

Custom runtime exceptions raised via the RAISE_APPLICATION_ERROR protocol are subject to a restricted numeric range. To avoid conflicts with pre-allocated system error codes, the application developer must define this variable within the numeric boundaries of −20000 to −20999.

External links

• Microsoft SQL Server DROP TRIGGER
• MySQL Database triggers
• MySQL DB Create Triggers
• DB2 CREATE TRIGGER statement
• Oracle CREATE TRIGGER
• PostgreSQL CREATE TRIGGER
• Oracle Mutating Table Problems with DELETE CASCADE
• SQLite Query Language: CREATE TRIGGER
• Oracle's Documentation on Triggers