Help Center/ GaussDB(DWS)/ SQL Syntax Reference/ SQL Syntax Reference (9.1.0.x)/ DDL Syntax/ CREATE TRIGGER

Updated on 2025-07-22 GMT+08:00

View PDF

CREATE TRIGGER

Function

Creates a trigger. The trigger will be associated with a specified table or view, and will execute a specified function when certain events occur.

Precautions

Currently, triggers can be created only on ordinary row-store tables, instead of on column-store tables, temporary tables, or unlogged tables.
If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name.
A trigger works only on one table. There is no limit on the number of triggers that can be created. However, more triggers on a table consume more performance.
Triggers are usually used for data association and synchronization between multiple tables. SQL execution performance is greatly affected. Therefore, you are advised not to use this statement when a large amount of data needs to be synchronized and performance requirements are high.
When a trigger meets the following conditions, the trigger statement and trigger itself can be pushed together down to a DN for execution, improving the trigger execution performance:
- enable_trigger_shipping and enable_fast_query_shipping are both enabled. (This is the default configuration.)
- The trigger function used by the source table is a PL/pgSQL function (recommended).
- The source and target tables have the same type and number of distribution keys, are both row-store tables, and belong to the same Node Group.
- The INSERT, UPDATE, or DELETE statement on the source table contains an expression about equality comparison between all the distribution keys and the NEW or OLD variable.
- The INSERT, UPDATE, or DELETE statement on the source table can be pushed down without a trigger.
- There are only six types of triggers, specified by INSERT/UPDATE/DELETE, AFTER/BEFORE, and FOR EACH ROW, on the source table, and all the triggers can be pushed down.
The following details trigger types:
- INSTEAD OF triggers must be marked as FOR EACH ROW and can be defined only on views.
- BEFORE and AFTER triggers on a view must be marked as FOR EACH STATEMENT.
- TRUNCATE triggers must be marked as FOR EACH STATEMENT.

Syntax

     
          CREATE [ CONSTRAINT ] TRIGGER trigger_name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE PROCEDURE function_name ( arguments );

Events include:

     
              INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

Parameter Description

Table 1 CREATE TRIGGER parameters

Parameter

Description

Value Range

CONSTRAINT

Creates a constraint trigger, that is, a trigger is used as a constraint. Such a trigger is similar to a regular trigger except that the timing of the trigger firing can be adjusted using SET CONSTRAINTS. Constraint triggers must be AFTER ROW triggers.

trigger_name

Specifies the name of the trigger to be created.

The name cannot be schema-qualified because the trigger inherits the schema of its table. In addition, triggers on the same table cannot be named the same. For a constraint trigger, this is also the name to use when you modify the trigger's behavior using SET CONSTRAINTS.

A string, which must comply with the naming convention.

BEFORE

Specifies that a trigger function is called before the trigger event.

AFTER

Specifies that a trigger function is called after the trigger event. A constraint trigger can only be specified as AFTER.

INSTEAD OF

Specifies that a trigger function directly replaces the trigger event.

event

Event for starting a trigger.

Values are INSERT, UPDATE, DELETE, and TRUNCATE. You can also specify multiple trigger events through OR.

For UPDATE events, use the following syntax to specify a list of columns:

          
               UPDATE OF column_name1 [, column_name2 ... ]

The trigger will only fire if at least one of the listed columns is mentioned as a target of the UPDATE statement. INSTEAD OF UPDATE events do not support lists of columns.

table_name

Specifies the name of the table where a trigger needs to be created.

Name of an existing table in the database.

referenced_table_name

Specifies the name of another table referenced by a constraint. This parameter can be specified only for constraint triggers. It does not support foreign key constraints and is not recommended for general use.

Name of an existing table in the database.

DEFERRABLE | NOT DEFERRABLE

Specifies the start time of the trigger. It can only be specified for constraint triggers. Controls whether the constraint can be deferred.

For details, see CREATE TABLE.

INITIALLY IMMEDIATE | INITIALLY DEFERRED

If a constraint is deferrable, the two clauses specify the default time to check the constraint, and can be specified only for constraint triggers.

For details, see CREATE TABLE.

FOR EACH ROW | FOR EACH STATEMENT

Specifies the frequency of firing a trigger.

FOR EACH ROW indicates that the trigger should be fired once for every row affected by the trigger event.
FOR EACH STATEMENT indicates that the trigger should be fired just once per SQL statement.

If this parameter is not specified, the default value FOR EACH STATEMENT will be used. Constraint triggers can only be specified as FOR EACH ROW.

condition

Specifies a Boolean expression that determines whether a trigger function will actually be executed. If WHEN is specified, the function will be called only when condition returns true.

In FOR EACH ROW triggers, the WHEN condition can reference the columns of old or new row values by writing OLD.column_name or NEW.column_name, respectively. Note that INSERT triggers cannot reference OLD and DELETE triggers cannot reference NEW.
INSTEAD OF triggers do not support WHEN conditions.
WHEN expressions cannot contain subqueries.
For constraint triggers, evaluation of the WHEN condition is not deferred, but occurs immediately after the update operation is performed. If the condition does not return true, the trigger will not be queued for deferred execution.

function_name

Specifies a user-defined function, which must be declared as taking no parameters and returning data of the trigger type. This function is executed when a trigger fires.

arguments

Specifies an optional, comma-separated list of parameters to be provided to a function when a trigger is executed. Parameters are literal string constants. Simple names and numeric constants can also be included, but they will all be converted to strings. Check descriptions of the implementation language of a trigger function to find out how these parameters are accessed within the function.

**Table 2** Types of triggers supported on tables and views
Trigger Timing	Trigger Event	Row-level	Statement-level
BEFORE	INSERT/UPDATE/DELETE	Tables	Tables and views
BEFORE	TRUNCATE	Not supported	Tables
AFTER	INSERT/UPDATE/DELETE	Tables	Tables and views
AFTER	TRUNCATE	Not supported	Tables
INSTEAD OF	INSERT/UPDATE/DELETE	Views	Not supported
INSTEAD OF	TRUNCATE	Not supported	Not supported

**Table 3** Special variables in the functions PL/pgSQL triggers
Variable	Description
NEW	New tuple for INSERT/UPDATE operations. This variable is NULL for DELETE operations.
OLD	Old tuple for UPDATE/DELETE operations. This variable is NULL for INSERT operations.
TG_NAME	Trigger name
TG_WHEN	Trigger timing (BEFORE/AFTER/INSTEAD OF)
TG_LEVEL	Trigger frequency (ROW/STATEMENT)
TG_OP	Trigger event (INSERT/UPDATE/DELETE/TRUNCATE)
TG_RELID	OID of the table where a trigger is located
TG_RELNAME	Name of the table where a trigger is located. (This variable is now discarded and is replaced by TG_TABLE_NAME.)
TG_TABLE_NAME	Name of the table where a trigger is located.
TG_TABLE_SCHEMA	Schema information of the table where a trigger is located
TG_NARGS	Number of parameters for a trigger function
TG_ARGV[]	List of parameters for a trigger function

Examples

Create a source table and a trigger table:

     
          CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);
CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);

Create the trigger function tri_insert_func():

     
          CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
           $$
           DECLARE
           BEGIN
                   INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
                   RETURN NEW;
           END
           $$ LANGUAGE PLPGSQL;

Create the trigger function tri_update_func():

     
          CREATE OR REPLACE FUNCTION tri_update_func() RETURNS TRIGGER AS
           $$
           DECLARE
           BEGIN
                   UPDATE test_trigger_des_tbl SET id3 = NEW.id3 WHERE id1=OLD.id1;
                   RETURN OLD;
           END
           $$ LANGUAGE PLPGSQL;

Create the trigger function tri_delete_func():

     
          CREATE OR REPLACE FUNCTION tri_delete_func() RETURNS TRIGGER AS
           $$
           DECLARE
           BEGIN
                   DELETE FROM test_trigger_des_tbl WHERE id1=OLD.id1;
                   RETURN OLD;
           END
           $$ LANGUAGE PLPGSQL;

Create an INSERT trigger:

     
          CREATE TRIGGER insert_trigger
           BEFORE INSERT ON test_trigger_src_tbl
           FOR EACH ROW
           EXECUTE PROCEDURE tri_insert_func();

Create an UPDATE trigger:

     
          CREATE TRIGGER update_trigger
           AFTER UPDATE ON test_trigger_src_tbl  
           FOR EACH ROW
           EXECUTE PROCEDURE tri_update_func();

Create a DELETE trigger:

     
          CREATE TRIGGER delete_trigger
           BEFORE DELETE ON test_trigger_src_tbl
           FOR EACH ROW
           EXECUTE PROCEDURE tri_delete_func();