Change all triggers

Have you ever tried to change all existing triggers, because they need to ignore the operation of a specific user?

This is extremely easy using the IBEBlock scripting language and Firebird 2.x as you can see in this example, which will simply add another line of code if (current_user<>'R$') then begin after the first begin and an additional end at the end of the sourcecode. This is used in our replication system to ignore operations initiated by the replication user R$.

 execute ibeblock 
 as
 begin
 DB = ibec_CreateConnection(__ctInterBase,
                            'DBName="LOCALHOST/3050:C:\db\db.FDB";
                            ClientLib=C:\Program Files (x86)\HK-Software\IBExpert\IBEUDB\fbembed.dll;
                            User=SYSDBA; Password=masterke; Names=NONE; SqlDialect=3;');
 use db;
 for
   select rdb$triggers.rdb$trigger_name,rdb$triggers.rdb$trigger_source
   from rdb$triggers
   where rdb$triggers.rdb$trigger_name not containing '$'
   and rdb$triggers.rdb$relation_name not containing '$'
   and rdb$triggers.rdb$trigger_source not containing 'R$'
   into trgn,trgsrc
 do
 begin
   trgsrc=ibec_StringReplace(trgsrc,'begin','begin if current_user<>''R$'' then begin',__rfIgnoreCase);
   trgsrc=trgsrc||'
   end';
   update rdb$triggers set rdb$triggers.rdb$trigger_source =:trgsrc where rdb$triggers.rdb$trigger_name=:trgn;
 end
 commit;
 ibec_RecompileTrigger(db,'');
 end

Important: IBEBlock is a set of DDL-Data Definition Language, DML-Data Manipulation Language and other statements that are executed on the server and on the client side, and which include some specific constructions applicable only in IBExpert or the IBExpert command-line tool IBEScript (excluding the free versions of these products), independent of the database server version.

Do you need our help? Here you can find our offers for hotline or onsite help.

back to top of page
<< REST example | IBEBlock | IBExpert file system inventory tips and tricks >>