CREATE TRIGGER Statement

Search Shortcut cmd + k | ctrl + k

Documentation / SQL / Statements

New The CREATE TRIGGER feature was introduced in DuckDB 2.0.

The CREATE TRIGGER statement registers a trigger: a set of SQL statements that run automatically in response to INSERT, UPDATE, or DELETE events on a table.

Examples

Record every statement that modifies a table in an audit table using a statement-level AFTER trigger. The REFERENCING OLD TABLE and NEW TABLE clauses expose the rows before and after the change as transition tables:

CREATE TABLE target (id INTEGER, val INTEGER);
CREATE TABLE audit (id INTEGER, old_val INTEGER, new_val INTEGER);

CREATE TRIGGER trg_audit
    AFTER UPDATE ON target
    REFERENCING
        OLD TABLE AS o
        NEW TABLE AS n
    FOR EACH STATEMENT
        INSERT INTO audit
        SELECT n.id, o.val, n.val
        FROM o
        JOIN n ON o.id = n.id;

INSERT INTO target VALUES (1, 10), (2, 20);
UPDATE target SET val = val * 10 WHERE id <= 2;
SELECT * FROM audit;

id	old_val	new_val
1	10	100
2	20	200

Fire a trigger once per affected row with a row-level AFTER trigger. Row-level triggers refer to the affected row through the NEW and OLD qualifiers:

CREATE TABLE t (id INTEGER);
CREATE TABLE log (id INTEGER);

CREATE TRIGGER trg_log
    AFTER INSERT ON t
    FOR EACH ROW
        INSERT INTO log VALUES (NEW.id);

INSERT INTO t VALUES (1), (2);
SELECT * FROM log;

Restrict a trigger to changes of specific columns with UPDATE OF:

CREATE TRIGGER trg_price
    AFTER UPDATE OF price ON products
    FOR EACH STATEMENT
        INSERT INTO price_changes DEFAULT VALUES;

Drop a trigger:

DROP TRIGGER trg_audit ON target;

Drop a trigger if it exists:

DROP TRIGGER IF EXISTS trg_audit ON target;

Syntax

The triggered statement is a single SQL statement, typically a data modification statement such as INSERT, UPDATE, or DELETE.

Timing

BEFORE triggers run before the triggering statement is applied.
AFTER triggers run after the triggering statement is applied.

Granularity

FOR EACH ROW fires the trigger once for every row affected by the statement. The triggered statement can refer to the affected row through NEW (for INSERT) and OLD (for DELETE). Row-level UPDATE triggers are not yet supported; see the limitations.
FOR EACH STATEMENT fires the trigger once per statement, regardless of how many rows are affected. Use REFERENCING OLD TABLE and NEW TABLE to access the affected rows as transition tables.

Transition Tables

Statement-level triggers can expose the rows involved in the change as transition tables:

NEW TABLE contains the inserted or post-update rows and is available for INSERT and UPDATE.
OLD TABLE contains the deleted or pre-update rows and is available for UPDATE and DELETE.

`OR REPLACE` and `IF NOT EXISTS`

CREATE OR REPLACE TRIGGER replaces an existing trigger with the same name. CREATE TRIGGER IF NOT EXISTS does nothing if a trigger with the same name already exists on the table. Without either clause, creating a trigger with a name that already exists results in an error. The two clauses cannot be combined.

Multiple Triggers

A table can have multiple triggers for the same event. They fire in the order in which they were created.

Returning Rows

A data modification statement on a table with FOR EACH STATEMENT triggers can still include a RETURNING clause. The trigger fires as part of the statement, and the RETURNING clause reports the rows affected by the statement itself:

CREATE TABLE t (id INTEGER);
CREATE TABLE log (row_count BIGINT);

CREATE TRIGGER trg_log
    AFTER INSERT ON t
    FOR EACH STATEMENT
        INSERT INTO log SELECT count(*) FROM t;

INSERT INTO t VALUES (1), (2) RETURNING id;

Inspecting Triggers

The triggers defined in the catalog can be listed with the duckdb_triggers() function:

SELECT trigger_name, table_name, action_timing, event_manipulation, for_each
FROM duckdb_triggers();

Dropping Triggers

A trigger is dropped with DROP TRIGGER. The table the trigger is attached to must be specified:

For example:

DROP TRIGGER trg_audit ON target;

Dropping a trigger that does not exist results in an error. Use IF EXISTS to skip the statement if the trigger does not exist:

DROP TRIGGER IF EXISTS trg_audit ON target;

The table must exist even when IF EXISTS is specified. The table name can be qualified with a schema name, e.g., ON my_schema.target.

Dropping a table also drops all triggers defined on it.

Limitations

DuckDB 2.0 has the following limitations:

BEFORE ... FOR EACH ROW triggers are not yet supported.
UPDATE ... FOR EACH ROW triggers are not yet supported.
RETURNING is not yet supported on tables with FOR EACH ROW triggers.
INSTEAD OF triggers and triggers on views are not supported; a trigger requires a base table.
Temporary triggers are not supported.

DuckDB Home Code of Conduct Trademark Use Blog

In this article