创建或替换PostgreSQL触发器。

Question

我希望为一个 Postgres 表创建或替换触发器。然而，没有这样的 SQL 表达式。

我看到可以先使用 "DROP TRIGGER IF EXISTS"（http://www.postgresql.org/docs/9.5/static/sql-droptrigger.html）。

我的问题是：

1. 是否有比 (DROP + CREATE 触发器) 更好的推荐选项？
2. 为什么没有 "create or replace trigger"，这是否意味着我不应该想要这样做?

请注意，在 Oracle 中有一个 "Create or Replace Trigger"（https://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm）。那么，

3. Postgres 是否计划添加此命令?

- jbarrameda

---

我来自未来 :D 自从14版本以来，CREATE TRIGGER有了OR REPLACE子句 -> https://www.postgresql.org/docs/current/sql-createtrigger.html - morb1d

Answer 1

无法创建或替换触发器，但可以通过以下方式进行操作

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

- X-Coder

---

然后跟着执行 CREATE TRIGGER... 查询。 - Fortune

Answer 2

PostgreSQL拥有事务DDL，因此BEGIN > DROP > CREATE > COMMIT 相当于 CREATE OR REPLACE

这篇文章很好地介绍了PostgreSQL的事务DDL如何与其他系统（如oracle）进行比较。

关于触发器方面的当前计划功能不包括添加REPLACE语法。

- Krut

---

为什么PostgreSQL允许CREATE OR REPLACE FUNCTION却不允许CREATE OR REPLACE TRIGGER？如果它遵循一个概念，那它也应该对函数强制执行相同的规则，是吗？还是我错了？ - Crystal Paladin

现在对我来说是一百万美元的问题，追求真相！ - M A Hossain Tonu

我认为其他对象可能会依赖于触发器，如果你删除了触发器，它也会删除其他对象（在级联的情况下）。 - István Döbrentei

这是不正确的。我无法删除视图，因为出现了错误：无法删除视图currency_rate，因为其他对象依赖于它 详细信息：复合类型consume_info列currency_rate依赖于类型currency_rate。 - Eugen Konkov

@CrystalPaladin，您不能删除另一个函数正在使用的函数。这种“DROP”和“CREATE”的技术对于函数是行不通的，这就是为什么存在“CREATE OR REPLACE”的原因。 - Simon Farshid

Answer 3

你应该使用两个语句：一个用于删除触发器，另一个用于创建触发器。

示例：

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();

- Vlad Bezden

---

在那里加上 BEGIN; 和 COMMIT; 就可以了。 - Henry Woody

Answer 4

从PostgreSQL 14开始，CREATE TRIGGER现在也支持"OR REPLACE"。

现在可以使用CREATE OR REPLACE TRIGGER ...（而不是首先使用DROP TRIGGER IF EXISTS）。

这似乎也合理处理了分区表的情况：

在分区表上创建行级触发器将导致在其所有现有分区上创建一个相同的“克隆”触发器；以及任何后来创建或附加的分区也都有一个相同的触发器。如果子分区上已经存在具有冲突名称的触发器，则会出现错误，除非使用CREATE OR REPLACE TRIGGER，否则该触发器将被替换为克隆触发器。当分区从其父项分离时，它的克隆触发器将被删除。

还值得注意的是：

当前，约束触发器不支持OR REPLACE选项。

- Bruno

Answer 5

您可以将CREATE OR REPLACE FUNCTION trigger_function与以下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;
$$

- adriaan

Answer 6

您可以使用以下代码。

DO $$ BEGIN

CREATE (trigger, type , ...);

EXCEPTION
  WHEN others THEN null;
END $$;

示例：

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 $$;

- Ali Bagheri

Answer 7

这是一个Python脚本，用于从postgresql转储文件中提取所有触发器以进行重建。我使用许多堆叠视图，这在QGIS中非常有效；这有助于大大简化相关视图的维护。

基于Ali Bagheri的出色回答。

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)

- SzieberthAdam