检查触发器是否存在 [英] Check if trigger exists

问题描述

我在架构公共中的所有表上都有以下查询要触发:

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.

这篇关于检查触发器是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！