v2.23 Preview Preview release - not for production v2024.1 STS Stable release with standard-term support v2.20 LTS Stable release with long-term support v2.18 STS Stable release with standard-term support v2.14 LTS Stable release with long-term support Unsupported versions

CREATE TRIGGER

Synopsis

Use the CREATE TRIGGER statement to create a trigger.

Syntax

create_trigger ::= CREATE TRIGGER name { BEFORE | AFTER | INSTEAD OF } 
                   { event [ OR ... ] } ON table_name 
                   [ FROM table_name ]  [ NOT DEFERRABLE ] 
                   [ FOR [ EACH ] { ROW | STATEMENT } ] 
                   [ WHEN ( boolean_expression ) ]  EXECUTE 
                   { FUNCTION | PROCEDURE } subprogram_name ( 
                   [ subprogram_signature ] )

event ::= INSERT
          | UPDATE [ OF column_name [ , ... ] ]
          | DELETE
          | TRUNCATE

create_trigger

CREATETRIGGERnameBEFOREAFTERINSTEADOFOReventONtable_nameFROMtable_nameNOTDEFERRABLEFOREACHROWSTATEMENTWHEN(boolean_expression)EXECUTEFUNCTIONPROCEDUREsubprogram_name(subprogram_signature)

event

INSERTUPDATEOF,column_nameDELETETRUNCATE

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.

    CREATE EXTENSION insert_username;
CREATE EXTENSION moddatetime;

CREATE TABLE posts (
  id int primary key,
  content text,
  username text not null,
  moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TRIGGER insert_usernames
   BEFORE INSERT OR UPDATE ON posts
   FOR EACH ROW
   EXECUTE PROCEDURE insert_username (username);

CREATE TRIGGER update_moddatetime
   BEFORE UPDATE ON posts
   FOR EACH ROW
   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.

    SET ROLE yugabyte;
INSERT INTO posts VALUES(1, 'desc1');

SET ROLE postgres;
INSERT INTO posts VALUES(2, 'desc2');
INSERT INTO posts VALUES(3, 'desc3');

SET ROLE yugabyte;
INSERT INTO posts VALUES(4, 'desc4');

SELECT * FROM posts ORDER BY id;

     id | content | username |          moddate
----+---------+----------+----------------------------
  1 | desc1   | yugabyte | 2019-09-13 16:55:53.969907
  2 | desc2   | postgres | 2019-09-13 16:55:53.983306
  3 | desc3   | postgres | 2019-09-13 16:55:53.98658
  4 | desc4   | yugabyte | 2019-09-13 16:55:53.991315

    Note

    YSQL 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.

    UPDATE posts SET content = 'desc1_updated' WHERE id = 1;
UPDATE posts SET content = 'desc3_updated' WHERE id = 3;

SELECT * FROM posts ORDER BY id;

     id |    content    | username |          moddate
----+---------------+----------+----------------------------
  1 | desc1_updated | yugabyte | 2019-09-13 16:56:27.623513
  2 | desc2         | postgres | 2019-09-13 16:55:53.983306
  3 | desc3_updated | yugabyte | 2019-09-13 16:56:27.634099
  4 | desc4         | yugabyte | 2019-09-13 16:55:53.991315

See also