CREATE TRIGGER

Description

CREATE TRIGGER is used to create a trigger. A trigger is a special type of stored procedure, and is used for complex service rules and requirements and help ensure reference integrity. A trigger is associated with a specified table or view and executes a specified function under specific conditions, this allows you to retain the relationships defined in the table when you add, update, or delete rows in the table.

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.
• 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:
  • The GUC parameters enable_trigger_shipping and enable_fast_query_shipping are enabled.
  • 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 used to query shipping without a trigger.
  • There are only six types of triggers (INSERT BEFORE FOR EACH ROW, INSERT AFTER FOR EACH ROW, UPDATE BEFORE FOR EACH ROW, UPDATE AFTER FOR EACH ROW, DELETE BEFORE FOR EACH ROW, and DELETE AFTER FOR EACH ROW) on the source table, and all the triggers can be pushed down.
• When a trigger statement is executed, the permission is determined by the trigger creator.
• 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. The return value of the trigger function triggered by AFTER is not affected.
• For a BEFORE DELETE trigger, the return value NEW of the trigger function indicates NULL. For a BEFORE INSERT trigger, the return value OLD indicates NULL. For a BEFORE UPDATE trigger, the return value NULL indicates NULL.
• 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.

• INSTEAD OF triggers can only work on views. Their trigger functions can also return NULL, indicating that subsequent triggers will not be executed.

Syntax

1
2
3
4
5
6
7

CREATE [OR REPLACE] [ 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 ) | AnonyBlock ];

Events include:

1

    INSERT

1

    UPDATE [ OF column_name [, ... ] ]

1

    DELETE

1

    TRUNCATE

Parameters

• OR REPLACE

  (Optional) If this parameter is specified, the existing trigger will be modified.

  Constraint triggers and internal triggers cannot be created or modified using the OR REPLACE syntax. A constraint trigger is a trigger created using CREATE CONSTRAINT TRIGGER. An internal trigger is a trigger implicitly created by some SQL statements. For example, if a foreign key constraint is added to a table, four triggers are implicitly created. The four triggers are internal triggers.

• 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 allow a list 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. Because foreign keys are not supported currently, this option is not recommended for general use.

  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. They determine whether the constraint is deferrable.

  For details, see CREATE TABLE.

• INITIALLY IMMEDIATE | INITIALLY DEFERRED

  If the constraint is deferrable, the two clauses specify 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
  	TRUNCATE	Not supported.	Tables
  AFTER	INSERT/UPDATE/DELETE	Tables	Tables and views
  	TRUNCATE	Not supported.	Tables
  INSTEAD OF	INSERT/UPDATE/DELETE	Views	Not supported.
  	TRUNCATE	Not supported.	Not supported.

  Table 2 Special variables in PL/pgSQL functions

  Variable	Description
  NEW	New tuple for INSERT and UPDATE operations. This variable is NULL for DELETE operations and statement-level triggers.
  OLD	Old tuple for UPDATE and DELETE operations. This variable is NULL for INSERT operations and statement-level triggers.
  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);

Using the INSERT trigger

-- Create an INSERT 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;

-- Create an INSERT trigger.
gaussdb=# CREATE TRIGGER insert_trigger
           BEFORE INSERT ON test_trigger_src_tbl
           FOR EACH ROW
           EXECUTE PROCEDURE tri_insert_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;
 id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 300
(1 row)

gaussdb=# SELECT * FROM test_trigger_des_tbl; // Check whether the trigger operation takes effect.
 id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 300
(1 row)

Using the UPDATE trigger

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

-- Create an UPDATE trigger function.
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;

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

-- 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;
 id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 400
(1 row)

gaussdb=# SELECT * FROM test_trigger_des_tbl; // Check whether the trigger operation takes effect.
 id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 400
(1 row)

Using the DELETE trigger

-- Create a DELETE trigger function.
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 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 DELETE event and check the trigger results.
gaussdb=# DELETE FROM test_trigger_src_tbl WHERE id1=100;

gaussdb=# SELECT * FROM test_trigger_src_tbl;
 id1 | id2 | id3 
-----+-----+-----
(0 rows)

gaussdb=# SELECT * FROM test_trigger_des_tbl; // Check whether the trigger operation takes effect.
 id1 | id2 | id3 
-----+-----+-----
(0 rows)

Renaming a trigger

1
2

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

Disabling a trigger

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

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

gaussdb=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);

gaussdb=# SELECT * FROM test_trigger_src_tbl;
 id1 | id2 | id3 
-----+-----+-----
 100 | 200 | 300
(1 row)

gaussdb=# SELECT * FROM test_trigger_des_tbl; // View that the trigger does not take effect.
 id1 | id2 | id3 
-----+-----+-----
(0 rows)

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

Deleting a trigger

1
2
3
4
5

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;

Deleting a function

1
2
3
4
5

gaussdb=# DROP FUNCTION tri_insert_func;

gaussdb=# DROP FUNCTION tri_update_func;

gaussdb=# DROP FUNCTION tri_delete_func;

1
2
3

-- Delete the source table and destination table.
gaussdb=# DROP TABLE test_trigger_src_tbl;
gaussdb=# DROP TABLE test_trigger_des_tbl;

Helpful Links

ALTER TRIGGER, DROP TRIGGER, and ALTER TABLE