SQL 创建或替换触发器 postgres
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35927365/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create or replace trigger postgres
提问by jbarrameda
I want to "create or replace" a trigger for a postgres table. However, there is not such sql expression.
我想为 postgres 表“创建或替换”触发器。但是，没有这样的sql表达式。
I see that I can do a "DROP TRIGGER IF EXISTS" first (http://www.postgresql.org/docs/9.5/static/sql-droptrigger.html).
我看到我可以先做一个“ DROP TRIGGER IF EXISTS”（http://www.postgresql.org/docs/9.5/static/sql-droptrigger.html）。
My question are:
我的问题是：
- Is there a recommended/better option than (
DROP+CREATEtrigger) - 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)
- 是否有比（
DROP+CREATE触发器）推荐/更好的选项 - 是否有原因没有这样的“创建或替换触发器”（这可能意味着我不应该想要这样做）
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,
请注意，Create or Replace Triggeroracle中有一个“ ”（https://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm）。然后，
- Is such command planned for Postgres at all?
- 是否为 Postgres 计划了这样的命令？
采纳答案by Krut
Postgresql has transaction DDL so BEGIN > DROP > CREATE > COMMITis the equivalent of CREATE OR REPLACE
Postgresql 有事务 DDL 所以BEGIN > DROP > CREATE > COMMIT相当于CREATE OR REPLACE
Thisis a nice write-up of how postgre's transactional DDL compares to other systems (such as oracle)
这是一篇很好的文章，介绍了 postgre 的事务性 DDL 与其他系统（例如 oracle）的比较
Current postgres planned features regarding triggersdo not include adding the REPLACEsyntax.
当前 postgres 计划的关于触发器的功能不包括添加REPLACE语法。
回答by X-Coder
No way to create or replace a trigger but can do this way
无法创建或替换触发器，但可以这样做
DROP TRIGGER IF EXISTS yourtrigger_name on "yourschemaname"."yourtablename";
回答by Vlad Bezden
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();
回答by Ali Bagheri
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 $$;
回答by adriaan
You can combine CREATE OR REPLACE FUNCTION trigger_functionwith the following script in your SQL:
您可以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;
$$
