CREATE TRIGGER

Description

Creates a trigger. The trigger will be associated with the specified table or view, and will execute the specified function operations are performed.

Precautions

Currently, triggers can be created only on ordinary row-store tables, instead of on 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.
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.
To create a trigger, you must have the TRIGGER permission on the specified table or have the CREATE ANY TRIGGER permission.
A row-level trigger function triggered by BEFORE can return a NULL value, indicating that operations on the row are ignored. Subsequent triggers will not be executed and no INSERT, UPDATE, or DELETE action will be generated on the row.
For a trigger function whose event is INSERT or UPDATE, the normal return value is NEW. If a non-NULL row is returned, the inserted or updated row is modified. For a trigger function whose event is DELETE, the normal return value is OLD. Note that in the case of a DELETE BEFORE trigger, the return value NEW indicates NULL.
INSTEAD OF triggers can only work on views. Their trigger functions can also return NULL, indicating that subsequent triggers will not be executed.

Syntax

CREATE [ CONSTRAINT ] 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

Parameters

CONSTRAINT
(Optional) Creates a constraint trigger. That is, the trigger is used as a constraint. This is the same as a regular trigger except that the timing of the trigger firing can be adjusted using SET CONSTRAINTS. Constraint triggers must be AFTER ROW triggers.
name
Specifies the name of the trigger to be created. This must be distinct from the name of any other trigger for the same table. The name cannot be schema-qualified — the trigger inherits the schema of its table. For a constraint trigger, this is also the name to use when modifying the trigger's behavior using SET CONSTRAINTS.

Value range: a string, which complies with the naming convention. A value can contain a maximum of 63 characters.
BEFORE
Specifies that the function is called before the event.
AFTER
Specifies that the function is called after the event. A constraint trigger can only be specified as AFTER.
INSTEAD OF
Specifies that the function is called instead of the event.
event
Specifies the event that will fire the trigger. Values are INSERT, UPDATE, DELETE, and TRUNCATE. Multiple events can be specified using OR.

For UPDATE events, it is possible to specify a list of columns using this syntax:
```
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. If the column specified by UPDATE OF contains a generated column, the trigger is also fired when the column on which the generated column depends is the target column of the UPDATE statement.
table_name
Specifies the name of the table for which the trigger is created.

Value range: name of an existing table in the database
referenced_table_name
Specifies the name of another table referenced by the constraint. This option is used for foreign-key constraints. It can only be specified for constraint triggers.

Value range: 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. Determines whether the constraint can be deferred.

For details, see CREATE TABLE.
INITIALLY IMMEDIATE | INITIALLY DEFERRED
If a constraint is deferrable, this clause specifies the default time to check the constraint. It can only be specified for constraint triggers.

For details, see CREATE TABLE.
FOR EACH ROW | FOR EACH STATEMENT
Specifies the frequency of firing the 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 neither is specified, the default is FOR EACH STATEMENT. Constraint triggers can only be marked as FOR EACH ROW.
condition
Specifies whether the 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 refer to columns of the old and/or new row values by writing OLD.column name or NEW.column name respectively. In addition, INSERT triggers cannot refer to OLD, and DELETE triggers cannot refer to NEW.

INSTEAD OF triggers do not support WHEN conditions.

Currently, WHEN expressions cannot contain subqueries.

Note that for constraint triggers, evaluation of the WHEN condition is not deferred, but occurs immediately after the row update operation is performed. If the condition does not evaluate to true, then the trigger is not queued for deferred execution.
function_name
Specifies a user-defined function, which must be declared as taking no parameters and returning type trigger. This is executed when a trigger fires.

arguments

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

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.

**Table 1** Types of triggers supported on tables and views
When	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 2** Special variables of PL/pgSQL trigger functions
Variable	Description
NEW	New tuple for INSERT and UPDATE operations. This variable is NULL for DELETE operations.
OLD	Old tuple for UPDATE and DELETE operations. This variable is NULL for INSERT operations.
TG_NAME	Trigger name.
TG_WHEN	Trigger timing (BEFORE, AFTER, or INSTEAD OF).
TG_LEVEL	Trigger frequency (ROW or STATEMENT).
TG_OP	Trigger event (INSERT, UPDATE, DELETE, or TRUNCATE).
TG_RELID	OID of the table where the trigger resides.
TG_RELNAME	Name of the table where the trigger resides. (This variable has been replaced by TG_TABLE_NAME.)
TG_TABLE_NAME	Name of the table where the trigger resides.
TG_TABLE_SCHEMA	Schema of the table where the trigger resides.
TG_NARGS	Number of parameters for the trigger function.
TG_ARGV[]	List of parameters for the trigger function.

Examples

-- Create a source table and a destination table.
gaussdb=# CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);
gaussdb=# CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);

-- Create a trigger function.
gaussdb=# 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;

gaussdb=# 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;

gaussdb=# 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.
gaussdb=# CREATE TRIGGER insert_trigger
           BEFORE INSERT ON test_trigger_src_tbl
           FOR EACH ROW
           EXECUTE PROCEDURE tri_insert_func();

-- Create an UPDATE trigger.
gaussdb=# CREATE TRIGGER update_trigger
           AFTER UPDATE ON test_trigger_src_tbl  
           FOR EACH ROW
           EXECUTE PROCEDURE tri_update_func();

-- Create a DELETE trigger.
gaussdb=# CREATE TRIGGER delete_trigger
           BEFORE DELETE ON test_trigger_src_tbl
           FOR EACH ROW
           EXECUTE PROCEDURE tri_delete_func();

-- Execute the INSERT event and check the trigger results.
gaussdb=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
gaussdb=# SELECT * FROM test_trigger_src_tbl;
gaussdb=# SELECT * FROM test_trigger_des_tbl;  // Check whether the trigger operation takes effect.

-- Execute the UPDATE event and check the trigger results.
gaussdb=# UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100;
gaussdb=# SELECT * FROM test_trigger_src_tbl;
gaussdb=# SELECT * FROM test_trigger_des_tbl;  // Check whether the trigger operation takes effect.

-- Execute the DELETE event and check the trigger results.
gaussdb=# DELETE FROM test_trigger_src_tbl WHERE id1=100;
gaussdb=# SELECT * FROM test_trigger_src_tbl;
gaussdb=# SELECT * FROM test_trigger_des_tbl;  // Check whether the trigger operation takes effect.

-- Modify a trigger.
gaussdb=# ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;

-- Disable insert_trigger.
gaussdb=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;  

-- Disable all triggers on the current table.
gaussdb=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;  

-- Delete triggers.
gaussdb=# DROP TRIGGER insert_trigger ON test_trigger_src_tbl;
gaussdb=# DROP TRIGGER update_trigger ON test_trigger_src_tbl;
gaussdb=# DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl;