CREATE TRIGGER

This page documents the preview version (v2.23). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

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