2

It is only possible to call ALTER TRIGGER ... from a PSQL statement, which is not always easy to initialise. Also, it does not look nice to write dozens of lines for each trigger, both:

if (:act = 1) ALTER TRIGGER ... ACTIVE;
else          ALTER TRIGGER ... INACTIVE;
if (:act = 1) ...

It would be much better, if I could simply call: (with 0/1 as parameter)

update RDB$TRIGGERS set RDB$TRIGGER_INACTIVE=1
 where RDB$TRIGGER_NAME in ('TRG_AUI_DETAILS','TRG_AU_INV','...');

But is this safe to do in Firebird 2.5? Or does the official ACTIVE / INACTIVATE command do anything else in the background? (I found the idea: here)

Addendum, I settled on using:

EXECUTE BLOCK 
...
  FOR SELECT ...
    S = 'ALTER TRIGGER ' || :trigger_name ||' ACTIVE';
    execute statement :S;

So basically, creating a dynamic stings for each statement.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
SzakiLaci
  • 347
  • 1
  • 16

1 Answers1

2

This is no longer possible in Firebird 3.0 and higher, so I recommend not relying on this hack as it will make migrating to newer versions harder. However, as far as I'm aware from a cursory look at the sources, in Firebird 2.5, it would do basically the same thing (though, to be clear, I am not 100% sure about this, because it looks like the trigger will be recompiled, which doesn't happen when you perform direct table modifications).

In Firebird 3.0, DDL triggers were introduced, and direct system table modification would not fire those triggers, so direct system table modification is now prohibited.

As an aside, I hope you are aware that Firebird 2.5 is no longer supported. I recommend upgrading to a supported version.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Thanks for the answer! A firebird 3.0 installation is making the 2.5 databases unable to read. So mass scale auto-upgrade deployment is impossible. – SzakiLaci Nov 03 '22 at 17:58
  • @SzakiLaci Yes, migrating to 3.0 or higher requires migrating using backup and restore. – Mark Rotteveel Nov 04 '22 at 07:34