CREATE TRIGGER
Function
CREATE TRIGGER 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 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.
- 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.
Syntax
- O-compatible syntax for creating a trigger
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 );
- MySQL-compatible syntax for creating a trigger
CREATE [ CONSTRAINT ] [ DEFINER=user ] TRIGGER [ IF NOT EXISTS ] 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 ) ]
[ trigger_order ]
trigger_body
Events include:
INSERT
UPDATE [ OF column_name [, ... ] ]
DELETE
TRUNCATE
Where trigger_order is:
{ FOLLOWS|PRECEDES } other_trigger_name
Parameter Description
CONSTRAINT
(Optional) Creates a constraint trigger. That is, the 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.
DEFINER
(Optional) Affects the permission control of the referenced object within the trigger.
IF NOT EXISTS
(Optional) Prevents errors if triggers have the same name, table, or table in the same schema.
trigger_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 identifier naming convention and contains a maximum of 63 characters.
BEFORE
Specifies that the function is called before the 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 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.
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 a constraint. This parameter can be specified only 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. 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 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 data of 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.
trigger_order
(Optional) {FOLLOWS|PRECEDES} in the trigger_order feature controls the triggering sequence. In MySQL-compatible mode, multiple triggers can be defined for the same triggered event in the same table. The trigger that is created first is triggered first. You can use {FOLLOWS|PRECEDES} to adjust the priority. If FOLLOWS is used, the trigger used for the last time has the highest priority. If PRECEDES is used, the trigger used for the last time has the lowest priority.
trigger_body
Define the work to be done after the trigger by writing a code block between BEGIN and END.
It can also be a single SQL statement. Currently, the following statements are supported: INSERT, UPDATE, DELETE, SET, and CALL.
NOTE: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
Table 2 Special variables in PL/pgSQL functions
Examples
--Create a source table and a destination table.
openGauss=# CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);
openGauss=# CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);
--Create a trigger function.
openGauss=# 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;
openGauss=# 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;
openGauss=# 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.
openGauss=# CREATE TRIGGER insert_trigger
BEFORE INSERT ON test_trigger_src_tbl
FOR EACH ROW
EXECUTE PROCEDURE tri_insert_func();
--Create an UPDATE trigger.
openGauss=# CREATE TRIGGER update_trigger
AFTER UPDATE ON test_trigger_src_tbl
FOR EACH ROW
EXECUTE PROCEDURE tri_update_func();
--Create a DELETE trigger.
openGauss=# 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.
openGauss=# INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
openGauss=# SELECT * FROM test_trigger_src_tbl;
openGauss=# SELECT * FROM test_trigger_des_tbl; // Check whether the trigger operation takes effect.
--Execute the UPDATE event and check the trigger results.
openGauss=# UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100;
openGauss=# SELECT * FROM test_trigger_src_tbl;
openGauss=# SELECT * FROM test_trigger_des_tbl; // Check whether the trigger operation takes effect.
--Execute the DELETE event and check the trigger results.
openGauss=# DELETE FROM test_trigger_src_tbl WHERE id1=100;
openGauss=# SELECT * FROM test_trigger_src_tbl;
openGauss=# SELECT * FROM test_trigger_des_tbl; // Check whether the trigger operation takes effect.
--Modify a trigger.
openGauss=# ALTER TRIGGER delete_trigger ON test_trigger_src_tbl RENAME TO delete_trigger_renamed;
--Disable insert_trigger.
openGauss=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;
--Disable all triggers on the current table.
openGauss=# ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;
--Delete triggers.
openGauss=# DROP TRIGGER insert_trigger ON test_trigger_src_tbl;
openGauss=# DROP TRIGGER update_trigger ON test_trigger_src_tbl;
openGauss=# DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl;
--Create a MySQL-compatible database.
openGauss=# create database db_mysql dbcompatibility 'B';
--Create a trigger definition user.
openGauss=# create user test_user password 'Gauss@123';
--Create a source table and a target table.
db_mysql=# create table test_mysql_trigger_src_tbl (id INT);
db_mysql=# create table test_mysql_trigger_des_tbl (id INT);
db_mysql=# create table animals (id INT, name CHAR(30));
db_mysql=# create table food (id INT, foodtype VARCHAR(32), remark VARCHAR(32), time_flag TIMESTAMP);
--Create a MySQL trigger that is compatible with the definer syntax.
db_mysql=# create definer=test_user trigger trigger1
after insert on test_mysql_trigger_src_tbl
for each row
begin
insert into test_mysql_trigger_des_tbl values(1);
end;
/
--Create a MySQL trigger that is compatible with the trigger_order syntax.
db_mysql=# create trigger animal_trigger1
after insert on animals
for each row
begin
insert into food(id, foodtype, remark, time_flag) values (1,'ice cream', 'sdsdsdsd', now());
end;
/
--Create a MySQL trigger that is compatible with FOLLOWS.
db_mysql=# create trigger animal_trigger2
after insert on animals
for each row
follows animal_trigger1
begin
insert into food(id, foodtype, remark, time_flag) values (2,'chocolate', 'sdsdsdsd', now());
end;
/
db_mysql=# create trigger animal_trigger3
after insert on animals
for each row
follows animal_trigger1
begin
insert into food(id, foodtype, remark, time_flag) values (3,'cake', 'sdsdsdsd', now());
end;
/
db_mysql=# create trigger animal_trigger4
after insert on animals
for each row
follows animal_trigger1
begin
insert into food(id, foodtype, remark, time_flag) values (4,'sausage', 'sdsdsdsd', now());
end;
/
--Execute the INSERT statement to trigger an event and check the trigger result:
db_mysql=# insert into animals (id, name) values(1,'lion');
db_mysql=# select * from animals;
db_mysql=# select id, foodtype, remark from food;
--Create a MySQL trigger that is compatible with PROCEDES.
db_mysql=# create trigger animal_trigger5
after insert on animals
for each row
precedes animal_trigger3
begin
insert into food(id, foodtype, remark, time_flag) values (5,'milk', 'sdsds', now());
end;
/
db_mysql=# create trigger animal_trigger6
after insert on animals
for each row
precedes animal_trigger2
begin
insert into food(id, foodtype, remark, time_flag) values (6,'strawberry', 'sdsds', now());
end;
/
--Execute the INSERT statement to trigger an event and check the trigger result:
db_mysql=# insert into animals (id, name) values(2, 'dog');
db_mysql=# select * from animals;
db_mysql=# select id, foodtype, remark from food;
--Create a MySQL trigger that is compatible with the IF NOT EXISTS syntax.
db_mysql=# create trigger if not exists animal_trigger1
after insert on animals
for each row
begin
insert into food(id, foodtype, remark, time_flag) values (1,'ice cream', 'sdsdsdsd', now());
end;
/