v2.25 Preview Preview release - not for production v2024.2 LTS Stable release with long-term support 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 [ OR REPLACE ] 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

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

OR replace

The OR REPLACE option allows you to replace an existing trigger with a new one without first having to drop the old trigger. By automatically replacing the existing trigger, this option reduces the risk of errors that might arise from accidentally forgetting to drop a trigger before creating a new one.

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

Partitioned tables

Creating a row-level trigger on a partitioned table automatically results in an identical trigger being created on all of its existing partitions. Additionally, any partitions that are created or attached to the table later will also receive the same trigger. If a partition already has a trigger with the same name, an error will occur unless you use the CREATE OR REPLACE TRIGGER command, which replaces the existing trigger with the new one. When a partition is detached from its parent table, the triggers associated with it are removed automatically.

BEFORE

For a BEFORE trigger, the WHEN condition is evaluated immediately before the trigger function is executed, or would be executed if the condition evaluates to TRUE. This makes using the WHEN clause functionally similar to performing the same check at the start of the trigger function. The NEW row seen by the condition reflects any modifications made by earlier triggers in the same operation.

BEFORE ROW triggers on INSERT cannot change which partition is the final destination for a new row.

AFTER

In the case of an AFTER trigger, the WHEN condition is evaluated right after the row update is completed. The result of this evaluation determines whether an event is queued to fire the trigger at the end of the statement. If the WHEN condition evaluates to FALSE, no event is queued, and the trigger does not execute. This behavior can lead to significant performance improvements in statements that modify many rows, as the trigger is only fired for the rows that meet the condition.

See also