本文介绍了检查触发器是否存在的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在架构公共中的所有表上都有以下查询要触发:
I have the following query to triggers on all tables in schema public:
SELECT 'CREATE TRIGGER ' || tab_name|| '_if_modified_trg INSERT OR UPDATE OR DELETE ON ' || tab_name|| ' FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ' AS trigger_creation_query
FROM (
SELECT quote_ident(table_schema) || '.' || quote_ident(table_name) as tab_name
FROM information_schema.tables
WHERE table_schema='public'
) AS foo;
我知道如何检查触发器是否存在:
And I know how to check if a trigger exists:
SELECT tgname
from pg_trigger
where not tgisinternal AND tgname='randomname'
但是如何在第一个查询中检查是否已经存在具有相同名称的触发器-跳过创建该触发器并继续操作?这是我的解决方案,但不起作用:
But how can I check in the first query whether a trigger with the same name already exists - and skip creating it and just continue? Here is my solution but it doesn't work:
SELECT 'CREATE TRIGGER ' || tab_name|| '_if_modified_trg INSERT OR UPDATE OR DELETE ON ' || tab_name|| ' FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ' AS trigger_creation_query
FROM (
SELECT quote_ident(table_schema) || '.' || quote_ident(table_name) as tab_name
FROM information_schema.tables
WHERE table_schema='public'
) AS foo
WHERE tab_name||'if_modified_trg' NOT IN (
SELECT tgname
from pg_trigger
where not tgisinternal );
推荐答案
使用此方法,您可以检查触发器是否存在,如果不存在则创建它.不要忘记最后一个;".
Using this you can check if the trigger exists and create it if not. Don't forget the last ";".
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'randomname') THEN
CREATE TRIGGER randomname
AFTER INSERT OR UPDATE OR DELETE ON randomtable
FOR EACH ROW EXECUTE PROCEDURE randomfunction();
END IF;
END
$$;
我希望这可以为您提供帮助.
I hope this can help you.
这篇关于检查触发器是否存在的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!