如何在PostgreSQL中暂时禁用触发器?

或者,如果要禁用所有触发器,而不仅仅是用户表上的触发器,则可以使用:

SET session_replication_role = replica;

这将禁用当前会话的触发器。

SET session_replication_role = DEFAULT;

资料来源: http://koo.fi/blog/2013/01/08/disable-postgresql-triggers-temporarily/

PostgreSQL知道 ALTER TABLE tblname DISABLE TRIGGER USER 命令,它似乎能满足我的需要。见 ALTER TABLE .

用于禁用触发器

ALTER TABLE table_name DISABLE TRIGGER trigger_name

用于启用触发器

ALTER TABLE table_name ENABLE TRIGGER trigger_name

您还可以在pgAdmin(III)中禁用触发器:

2. 扩展+
3. 在触发器中查找触发器
4. 右键单击,取消选中“触发器已启用?”

SET session_replication_role = replica; 

postgresql.nabble.com ENABLE / DISABLE ALL TRIGGERS IN DATABASE 作品。当你需要调整时,它可能会很有用。

例如,如果在特定命名空间中有表,则可能是:

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;

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;

PostgreSQL文档 system catalogs

触发触发过程还有其他控制选项:

更改表。。。启用副本触发器。。。-触发器将仅在复制模式下触发。

更改表。。。始终启用触发器。。。-扳机总是会开火(显然)

SET session_replication_role = replica;  

也为我在9.1后工作。 我对bartolo otrit描述的两个函数进行了一些修改。 我修改了第一个函数使其对我有效,因为必须存在命名空间或架构才能正确标识表。 新代码是:

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;

然后简单地对每个模式执行一个select查询:

SELECT disable_triggers(true,'public');
SELECT disable_triggers(true,'Adempiere');