标簽
PostgreSQL , 觸發器 , enable , disable
https://github.com/digoal/blog/blob/master/201804/20180408_02.md#%E8%83%8C%E6%99%AF 背景
Oracle 臨時關閉或啟用觸發器的文法如下
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4001.htmALTER TRIGGER [ schema. ]trigger
{ ENABLE
| DISABLE
| RENAME TO new_name
| COMPILE [ DEBUG ]
[ compiler_parameters_clause
[ compiler_parameters_clause ] ... ]
[ REUSE SETTINGS ]
} ;
https://github.com/digoal/blog/blob/master/201804/20180408_02.md#postgresql-%E8%A7%A6%E5%8F%91%E5%99%A8%E5%BC%80%E5%85%B3%E8%AF%AD%E6%B3%95%E6%9B%B4%E4%B8%B0%E5%AF%8C%E4%B8%80%E4%BA%9B PostgreSQL 觸發器開關文法更豐富一些
PostgreSQL中對應的文法在alter table中，因為觸發器與表相關，這樣設計文法也符合邏輯的。
https://www.postgresql.org/docs/devel/static/sql-altertable.htmlALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
action [, ... ]
DISABLE TRIGGER [ trigger_name | ALL | USER ]
ENABLE TRIGGER [ trigger_name | ALL | USER ]
ENABLE REPLICA TRIGGER trigger_name
ENABLE ALWAYS TRIGGER trigger_name
細節（注意觸發器被觸發還受制于session_replication_role參數與replica|always的配置。）
These forms configure the firing of trigger(s) belonging to the table. A disabled trigger is still known to the system, but is not executed when its triggering event occurs. For a deferred trigger, the enable status is checked when the event occurs, not when the trigger function is actually executed. One can disable or enable a single trigger specified by name, or all triggers on the table, or only user triggers (this option excludes internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints). Disabling or enabling internally generated constraint triggers requires superuser privileges; it should be done with caution since of course the integrity of the constraint cannot be guaranteed if the triggers are not executed.
The trigger firing mechanism is also affected by the configuration variable session_replication_role. Simply enabled triggers (the default) will fire when the replication role is “origin” (the default) or “local”. Triggers configured as ENABLE REPLICA will only fire if the session is in “replica” mode, and triggers configured as ENABLE ALWAYS will fire regardless of the current replication role.
The effect of this mechanism is that in the default configuration, triggers do not fire on replicas. This is useful because if a trigger is used on the origin to propagate data between tables, then the replication system will also replicate the propagated data, and the trigger should not fire a second time on the replica, because that would lead to duplication. However, if a trigger is used for another purpose such as creating external alerts, then it might be appropriate to set it to ENABLE ALWAYS so that it is also fired on replicas.
This command acquires a SHARE ROW EXCLUSIVE lock.
trigger_name
Name of a single trigger to disable or enable.
ALL
Disable or enable all triggers belonging to the table. (This requires superuser privilege if any of the triggers are internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints.)
USER
Disable or enable all triggers belonging to the table except for internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints.
(内部用于foreign key, unique, 排他 等限制的觸發器除外)
關閉或開啟指定或所有表，或者某個使用者，某個schema下的所有表的觸發器。
使用函數，參考：
https://stackoverflow.com/questions/3942258/how-do-i-temporarily-disable-triggers-in-postgresqlFor example if you have tables in a particular namespace it could be:
create or replace function disable_triggers(a boolean, nsp character varying) returns void as
$$
declare
act character varying;
r record;
begin
if(a is true) then
act = 'disable';
else
act = 'enable';
end if;
for r in select c.relname from pg_namespace n
join pg_class c on c.relnamespace = n.oid and c.relhastriggers = true
where n.nspname = nsp
loop
execute format('alter table %I %s trigger all', r.relname, act);
end loop;
end;
$$
language plpgsql;
If you want to disable all triggers with certain trigger function it could be:
create or replace function disable_trigger_func(a boolean, f character varying) returns void as
$$
declare
act character varying;
r record;
begin
if(a is true) then
act = 'disable';
else
act = 'enable';
end if;
for r in select c.relname from pg_proc p
join pg_trigger t on t.tgfoid = p.oid
join pg_class c on c.oid = t.tgrelid
where p.proname = f
loop
execute format('alter table %I %s trigger all', r.relname, act);
end loop;
end;
$$
language plpgsql;
also dosent work for me in Postgres 9.1. i use the two function described by bartolo-otrit with some modification. I modified the first function to make it work for me because the namespace or the schema must be present to identify the table correctly. The new code is :
CREATE OR REPLACE FUNCTION disable_triggers(a boolean, nsp character varying)
RETURNS void AS
$BODY$
declare
act character varying;
r record;
begin
if(a is true) then
act = 'disable';
else
act = 'enable';
end if;
for r in select c.relname from pg_namespace n
join pg_class c on c.relnamespace = n.oid and c.relhastriggers = true
where n.nspname = nsp
loop
execute format('alter table %I.%I %s trigger all', nsp,r.relname, act);
end loop;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION disable_triggers(boolean, character varying)
OWNER TO postgres;
then i simply do a select query for every schema :
SELECT disable_triggers(true,'public');
SELECT disable_triggers(true,'Adempiere');
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)