Subject Changing trigger active/inactive state within a transaction
Author Rich MacDonald
...The answer is no :-)

I treat firebird as a write-through cache for my app and keep all data
in app memory.
I add triggers to the tables to check for changes from external apps.
Any "external" change is then written to an UPDATES table by the
trigger, which I periodically poll, so I can pull down the changes.

Except for the many-to-many join tables, I have a solid technique for
differentiating between internal changes and external changes.
The problem is that my app is very stupid when it changes these
many-to-many join tables. The app firsts deletes all the corresponding
rows, then inserts them all back. That's the way it is and I cannot
change it.

I need to ensure that my app doesn't get stuck in the loop of (1)
persisting some changes, (2) reading them back via the UPDATES
polling, then (3) writing them all back because they have "changed",
then ...

Ok, I confess. I'm using hibernate and that is exactly what will
happen unless I write some complex code to prevent it. And even if I
can prevent it, its inefficient because I have to insert/update data,
then read it all back, then compare it for differences.

Whew. So much background :-)
Then I had the brilliant idea of simply turning off the triggers at
the start of the transaction while my app is updating its changes,
then turning the triggers back on at the end of the transaction. This
would solve everything.

The steps are:

1) Begin transaction.
2) Deactivate triggers. (Applies only to changes within transaction.)
3) Make data updates.
4) Reactivate triggers.
5) Commit transaction.

Note that I cannot deactivate and reactivate the triggers outside the
transaction, since they need to remain active for any external
changes. Deactivating the triggers must only apply to my data updates.

I tried this in firebird 1.5.3rc2 and it did not work. The trigger
changes are only made at the end of the transaction and they remain
active for my changes.

Which is sad because that would have been a beautiful solution.
Of course it was asking a lot.

Have I missed something? Has this ever been discussed in the forum?
Wouldn't it be a great capability? Is it possible to hack the
active/inactive flag by changing a system table value as part of a
transaction? (I'll be trying this next.)

P.S. The usual idea of adding a "ChangedBy" column to identify the
source of the change doesn't help here, because its not applicable
when deleting rows, and as I said, I do a lot of that :-)