You will not encounter the mutating table (ora 4091) in the statement level
trigger. So you can just select the count from the whole table, just like
you probably were in the beginning. Just remove the for each row clause
from your initial trigger script, and you should be there.
"Renato Fabiano" <renatofabiano_at_hotmail.com> wrote in message
news:2358ca76.0110301111.bd1d7b4_at_posting.google.com...
> Hi,
>
> I want to know how enforce a table to have at maximum <n> lines.
>
> I had tried to use the count(*) value from the after insert trigger
> of the desired table, but it is not permitted (error ORA-04091
'mutating').
>
> Now, I have created an auxiliary table as:
> create tb_limits
> (table_name varchar2(30),
> max_lines number,
> act_lines number).
>
> For example, the table tb_limits has a line
> ('TB_B', 1000, <n>), used to control the actual number of lines of
> table 'TB_B' (or desired table).
>
> I am using this table from 'after insert' trigger of desired tables
> to compare act_lines to act_lines; if act_lines is equal or
> greater than max_lines I raise a error; otherwise, I process
> the insert (and increment the value of act_lines, too).
>
> I created a 'after delete' trigger in order to decrement the
> values of 'act_lines'.
>
> Can you suggest a better solution?
>
> Thanks in advance,
>
> Renato Fabiano
>
Received on Fri Nov 02 2001 - 23:49:03 CST
