Subject RE: [firebird-support] Triggers suitability
Author Alan McDonald
> Are triggers a reliable way of maintaining summary tables? I have an
> orders table, where with every insert/delete/update, I need to update a
> summary table, possibly via a trigger e.g. UPDATE summary_table SET
> quantity = quantity + :NEW.quantity WHERE item_id = x
> Are there any possible locking issues, or trigger failure scenarios?
> Thanks for any help.
> Regards
> Ray Mond

there are locking issues under high user loads.
A better way is to INSERT the change into the summary table using -tive and
+tive numbers. The summary query is a SUM(field) type i.e. aggregate and/or
group by query. And this needs housekeeping to clear the table and establish
new baseline values periodically.
If this method is no good, then you will need to handle the possible lock
conflicts in the update calls.