Create or replace trigger postgres

Question

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,

3. Is such command planned for Postgres at all?

Bartholomeo

I'm coming from the future :D since version 14 there is OR REPLACE clause for CREATE TRIGGER -> postgresql.org/docs/current/sql-createtrigger.html – Tribulation

Answer 1

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

Voroshilovgrad

why does postgreql allow CREATE OR REPLACE FUNCTION but not CREATE OR REPLACE TRIGGER... If its going to follow a concept, it should've enforced it for functions too right? or am I wrong? – Bos

million dollar question to me right now, seeking the truth! – Sharolynsharon

I think maybe other object can depend on a trigger and If you delete the trigger it deletes other objects too (in case of cascade). – Stavropol

This is not true. I can not drop view because of ERROR: cannot drop view currency_rate because other objects depend on it DETAIL: composite type consume_info column currency_rate depends on type currency_rate – Tenorrhaphy

@CrystalPaladin You cannot drop a function that is in use by another function. This DROP and CREATE technique wouldn't work for functions and that's why CREATE OR REPLACE exists. – Curium

Answer 2

No way to create or replace a trigger but can do this way

DROP TRIGGER IF EXISTS yourtrigger_name on "yourschemaname"."yourtablename";

Forthright

..and then follow it up with the CREATE TRIGGER... query – Insectile

Answer 3

You should use two statements: one for drop trigger and another for creating a trigger.

Example:

DROP TRIGGER IF EXISTS my_trigger
  ON my_schema.my_table;
CREATE TRIGGER my_trigger
  BEFORE INSERT OR UPDATE
  ON my_schema.my_table
  FOR EACH ROW EXECUTE PROCEDURE my_schema.my_function();

Fairhaired

Slap BEGIN; and COMMIT; on there and you should be good. – Sepulture

Answer 4

As of PostgreSQL 14, CREATE TRIGGER now also supports "OR REPLACE".

You can now use CREATE OR REPLACE TRIGGER ... (instead of using DROP TRIGGER IF EXISTS first).

This also seems to handle the case of partitioned tables sensibly:

Creating a row-level trigger on a partitioned table will cause an identical “clone” trigger to be created on each of its existing partitions; and any partitions created or attached later will have an identical trigger, too. If there is a conflictingly-named trigger on a child partition already, an error occurs unless CREATE OR REPLACE TRIGGER is used, in which case that trigger is replaced with a clone trigger. When a partition is detached from its parent, its clone triggers are removed.

Also noteworthy:

Currently, the OR REPLACE option is not supported for constraint triggers.

Caligula

Answer 5

You can combine CREATE OR REPLACE FUNCTION trigger_function with the following script in your SQL:

DO $$
BEGIN
  IF NOT EXISTS(SELECT *
    FROM information_schema.triggers
    WHERE event_object_table = 'table_name'
    AND trigger_name = 'trigger_name'
  )
  THEN
    CREATE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW EXECUTE PROCEDURE trigger_function();
  END IF;
END;
$$

Guernsey

Answer 6

you can use below code.

DO $$ BEGIN

CREATE (trigger, type , ...);

EXCEPTION
  WHEN others THEN null;
END $$;

sample:

DO $$ BEGIN

CREATE TRIGGER trigger_workIDExist
  BEFORE INSERT OR UPDATE ON "GalleryModel"
  FOR EACH ROW EXECUTE PROCEDURE check_workIDExist();

EXCEPTION
  WHEN others THEN null;
END $$;

Lailalain

Answer 7

This is a Python script which extracts all triggers from a postgresql dump file for a rebuild. I use many stacked views which works nicely with QGIS; this helped maintenance of the dependent views a lot.

Based on Ali Bagheri's great answer.

import pathlib
import re
import sys

re_pat_str = r'^\s*CREATE TRIGGER.*?;\s*$'

sql_wrapper_str = """
DO $$ BEGIN
{trigger_str}
EXCEPTION WHEN others THEN null;
END $$;
"""

if __name__ == "__main__":
  sql_file = pathlib.Path(sys.argv[1])
  with sql_file.open("r", encoding="utf8") as f:
    sql_str = f.read()

  re_pat = re.compile(re_pat_str, re.MULTILINE | re.DOTALL)

  parts = []
  for i, m in enumerate(re_pat.finditer(sql_str)):
    parts.append(sql_wrapper_str.format(trigger_str=m[0].strip()))

  new_sql_str = "\n".join(parts)
  new_sql_file = sql_file.parent / f'{sql_file.stem}.trigger{sql_file.suffix}'
  with new_sql_file.open("w", encoding="utf8") as f:
    f.write(new_sql_str)

Dystopia