CREATE TRIGGER

Synopsis

Use the CREATE TRIGGER statement to define a new trigger.

Syntax

  1. create_trigger ::= CREATE TRIGGER name { BEFORE | AFTER | INSTEAD OF } 
  2.                    { event [ OR ... ] } ON table_name 
  3.                    [ FROM table_name ]  [ NOT DEFERRABLE ] 
  4.                    [ FOR [ EACH ] { ROW | STATEMENT } ] 
  5.                    [ WHEN ( condition ) ]  EXECUTE 
  6.                    { FUNCTION | PROCEDURE } function_name ( 
  7.                    function_arguments )
  9. event ::= INSERT
  10.           | UPDATE [ OF column_name [ , ... ] ]
  11.           | DELETE
  12.           | TRUNCATE

create_trigger

CREATE TRIGGER - 图1

event

CREATE TRIGGER - 图2

Semantics

  • the WHEN condition can be used to specify whether the trigger should be fired. For low-level triggers it can reference the old and/or new values of the row’s columns.
  • multiple triggers can be defined for the same event. In that case, they will be fired in alphabetical order by name.

Examples

  • Set up a table with triggers for tracking modification time and user (role).Use the pre-installed extensions insert_username and moddatetime.
  1. CREATE EXTENSION insert_username;
  2. CREATE EXTENSION moddatetime;
  4. CREATE TABLE posts (
  5.   id int primary key,
  6.   content text,
  7.   username text not null,
  8.   moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
  9. );
  11. CREATE TRIGGER insert_usernames
  12.    BEFORE INSERT OR UPDATE ON posts
  13.    FOR EACH ROW
  14.    EXECUTE PROCEDURE insert_username (username);
  16. CREATE TRIGGER update_moddatetime
  17.    BEFORE UPDATE ON posts
  18.    FOR EACH ROW
  19.    EXECUTE PROCEDURE moddatetime (moddate);
  • Insert some rows.For each insert, the triggers should set the current role as username and the current timestamp as moddate.
  1. SET ROLE yugabyte;
  2. INSERT INTO posts VALUES(1, 'desc1');
  4. SET ROLE postgres;
  5. INSERT INTO posts VALUES(2, 'desc2');
  6. INSERT INTO posts VALUES(3, 'desc3');
  8. SET ROLE yugabyte;
  9. INSERT INTO posts VALUES(4, 'desc4');
  11. SELECT * FROM posts ORDER BY id;
  1.  id | content | username |          moddate
  2. ----+---------+----------+----------------------------
  3.   1 | desc1   | yugabyte | 2019-09-13 16:55:53.969907
  4.   2 | desc2   | postgres | 2019-09-13 16:55:53.983306
  5.   3 | desc3   | postgres | 2019-09-13 16:55:53.98658
  6.   4 | desc4   | yugabyte | 2019-09-13 16:55:53.991315

NoteYSQL should have users yugabyte and (for compatibility) postgres created by default.

  • Update some rows.For each update the triggers should set both username and moddate accordingly.
  1. UPDATE posts SET content = 'desc1_updated' WHERE id = 1;
  2. UPDATE posts SET content = 'desc3_updated' WHERE id = 3;
  4. SELECT * FROM posts ORDER BY id;
  1.  id |    content    | username |          moddate
  2. ----+---------------+----------+----------------------------
  3.   1 | desc1_updated | yugabyte | 2019-09-13 16:56:27.623513
  4.   2 | desc2         | postgres | 2019-09-13 16:55:53.983306
  5.   3 | desc3_updated | yugabyte | 2019-09-13 16:56:27.634099
  6.   4 | desc4         | yugabyte | 2019-09-13 16:55:53.991315

See also