Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How can I limit the number of lines for a table?
Isn't there a before insert trigger? Then you should be able to do the
count(*).
"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 Tue Oct 30 2001 - 13:28:27 CST