Subject Re: [firebird-support] Maintain Summary Table with Database Trigger
Author unordained
---------- Original Message -----------
From: "JackR" <jack@...>
> Assuming that it is not a non-issue, is there a way to configure a
> database trigger to do this update?
------- End of Original Message -------


create trigger detail_changed for detail after insert or update or delete as
-- in case you change master_id (move detail from one master to another)
-- use both values. probably more efficient ways to do that.
-- avoid updating records that have already been marked, fewer disk writes(?)
update master set fix_me = 1 where id in (new.master_id, old.master_id) and
fix_me is distinct from 1;

create trigger fix_master on transaction commit as
for select ... from master where fix_me = 1 into ... as cursor cur do
update master set ... fix_me = 0 where current of cur;

This will result in record locks, but if you're updating the master no matter
what, using a temp table (rather than a "fix_me" field) won't change that.

Look into the isql options for disabling these triggers first though; if you mess
up fix_master such that it likes to throw exceptions, you could make it difficult
to fix, as you won't be able to commit the new trigger definition!