Firebird Sql Tips… Enable or Disable all trigger via sql

per Ivan Revelli
in Articoli, databases, Firebird SQL
on Marzo 9, 2016

To enable and disable all triggers of a database i do two procedure :

Enabling all database trigger:

create procedure SP_ENABLE_ALL_TRIGGER
as
begin
  /* Procedure Text */
   update RDB$TRIGGERS set RDB$TRIGGER_INACTIVE=0
    where RDB$TRIGGER_NAME
    in(
    select
    A.RDB$TRIGGER_NAME
    from RDB$TRIGGERS A left join RDB$CHECK_CONSTRAINTS B ON
    B.RDB$TRIGGER_NAME = A.RDB$TRIGGER_NAME where ((A.RDB$SYSTEM_FLAG = 0)
    or (A.RDB$SYSTEM_FLAG is null)) and (b.rdb$trigger_name is null)
    AND (NOT(A.RDB$TRIGGER_NAME LIKE 'RDB$%'))
    );
  suspend;
end

create procedure SP_ENABLE_ALL_TRIGGER

begin

/* Procedure Text */

update RDB$TRIGGERS set RDB$TRIGGER_INACTIVE=0

where RDB$TRIGGER_NAME

in(

select

A.RDB$TRIGGER_NAME

from RDB$TRIGGERS A left join RDB$CHECK_CONSTRAINTS B ON

B.RDB$TRIGGER_NAME = A.RDB$TRIGGER_NAME where ((A.RDB$SYSTEM_FLAG = 0)

or (A.RDB$SYSTEM_FLAG is null)) and (b.rdb$trigger_name is null)

AND (NOT(A.RDB$TRIGGER_NAME LIKE 'RDB$%'))

);

suspend;

end

Disabling all database trigger:

create procedure SP_DISABLE_ALL_TRIGGER
as
begin
  
   update RDB$TRIGGERS set RDB$TRIGGER_INACTIVE=1
    where RDB$TRIGGER_NAME
    in(
    select
    A.RDB$TRIGGER_NAME
    from RDB$TRIGGERS A left join RDB$CHECK_CONSTRAINTS B ON
    B.RDB$TRIGGER_NAME = A.RDB$TRIGGER_NAME where ((A.RDB$SYSTEM_FLAG = 0)
    or (A.RDB$SYSTEM_FLAG is null)) and (b.rdb$trigger_name is null)
    AND (NOT(A.RDB$TRIGGER_NAME LIKE 'RDB$%'))
    );
  suspend;
end

create procedure SP_DISABLE_ALL_TRIGGER