Search code examples
sqlpostgresqlddldatabase-trigger

Create or replace trigger postgres

I want to "create or replace" a trigger for a postgres table. However, there is not such sql expression.

I see that I can do a "DROP TRIGGER IF EXISTS" first (http://www.postgresql.org/docs/9.5/static/sql-droptrigger.html).

My question are:

  1. Is there a recommended/better option than (DROP + CREATE trigger)
  2. Is there a reason why there is not such "create or replace trigger" (which might imply that I should not be wanting to do it)

Note that there is a "Create or Replace Trigger" in oracle (https://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm). Then,

  1. Is such command planned for Postgres at all?
Solution

  • For older versions of PostgreSQL, using the transaction DDL BEGIN > DROP > CREATE > COMMIT is the equivalent of CREATE OR REPLACE

    This is a nice write-up of how postgre's transactional DDL compares to other systems (such as oracle)

    However, as of PostgreSQL 14 introduces CREATE OR REPLACE TRIGGER