• Browse categories
• Random entry
• PostgreSQL releases
  • PostgreSQL 16 (dev)
  • PostgreSQL 15 (latest)
  • PostgreSQL 14
  • PostgreSQL 13
  • PostgreSQL 12
  • PostgreSQL 11
  • PostgreSQL 10 (EOL)

Version charts

Useful overviews

Useful links

CREATE TRIGGER

CREATE TRIGGER is a DDL command for creating triggers.

CREATE TRIGGER was added in PostgreSQL 6.2.

Change history

• PostgreSQL 14
  • OR REPLACE syntax added (commit 92bf7e2d)
• PostgreSQL 11
  • EXECUTE FUNCTION syntax added (commit 0a63f996)
• PostgreSQL 10
  • REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } syntax added (initial commit 8c48375e)
• PostgreSQL 9.1
  • CREATE TRIGGER ... INSTEAD OF ... syntax added (commit 2ec993a7)
• PostgreSQL 9.0
  • WHEN clause added (commit 7fc0f062)
  • individual columns can be specified for update triggers with the UPDATE OF syntax (commit b2734a0d)
• PostgreSQL 6.2
  • added (commit 502f6f38)

Examples

postgres=# CREATE TABLE foo (id INT);
CREATE TABLE

postgres=# CREATE FUNCTION foo_trigger()
             RETURNS TRIGGER
             LANGUAGE plpgsql
           AS $$
             BEGIN
               RAISE NOTICE 'executing trigger';
               IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
                 RETURN NEW;
               END IF;
           
               RETURN OLD;
             END;
           $$;
CREATE FUNCTION

postgres=# CREATE TRIGGER trg_foo_iud_01
             AFTER INSERT OR UPDATE OR DELETE
             ON foo
             FOR EACH ROW
             EXECUTE FUNCTION foo_trigger();
CREATE TRIGGER

postgres=# INSERT INTO foo VALUES (1);
NOTICE:  executing trigger
INSERT 0 1

References

Categories

See also