Subject Re: [firebird-support] Re: altering triggers from sp
Author Martijn Tonies

> > > On the other hand maybe there is a better solution using
> > > transactions or triggers sequences. I have the trigger the do
> update
> > > on the table after some insert/update actions. What I want is to
> > > fire the trigger after a sequence of individual inserts/updates
> > > complete. The first thing I came up with is to deactivate
> trigger,
> > > and activate it before the last in the sequence.
> >
> > This idea isn't multi-user safe anyway.
> > What are these actions?
> I have a table of objects that is linked to itself via
> ID - primary key
> ID_parent - foreign key
> value - let say that it is integer
> the parent object contain the sum of all integers below (the tree is
> multilevel with varying depth from 1 to 10 levels)
> I built the trigger that make an update everytime (the update fire
> the same trigger at level higher). It has to be done that way
> because recalculation 'on fly' is not an option.

Right - what about a trigger on the Parent table that responds
to a column "SHOULD_UPDATE" which you can update
after you've updated all child records?

Or, if you know you will be updating multiple records
in the child table, why not create an SHOULD_UPDATE
column that you set to "TRUE" only for the last update/insert?

> The exact agregate operation may vary and could be quite complex,
> therefore I need it to execute the trigger once after a group of
> insert will be done instead of the current recalculation of all
> levels after every change.
> The whole thing should be multiuser safe - I believe commiting all
> changes in a single transaction should ensure that.

But changing metadata doesn't.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Upscene Productions