SELECT in DELETE trigger

Hello,

I have a table (TABLEA) with groups of rows and a TABLEB containing the MAX per group of a column in TABLEA (can't use a view for TABLEB for reasons not important here). I try to keep TABLEB in sync with TABLEA by using triggers. When adding a row to TABLEA, the trigger checks if current MAX for the group to which the new row belongs and updates TABLEB when the new row is larger. This is done using a BEFORE INSERT trigger. When I do the same kind of thing (determine the new MAX) in a BEFORE DELETE trigger by issuing a SELECT MAX(col) FROM TABLEA WHERE group_cond AND key_is_not_key_to_be_deleted, I get an ORA4091 error: "table TABLEA is mutating, trigger/function may not see it". I'm puzzeled why Oracle allows the same kind of SELECT on an INSERT trigger but doesn't allow it on a DELETE trigger. More importantly, how do I keep the tables in sync when I can't do a SELECT on TABLEA in the trigger?

Ge van Geldorp. Received on Tue Dec 19 1995 - 00:00:00 CET