How can I limit the number of lines for a table?

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:11:06 CST