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

On 30 Oct 2001 11:11:06 -0800, renatofabiano_at_hotmail.com (Renato Fabiano) wrote:

>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

Throw on a UNIQUE (or PRIMARY) KEY. Then add another CONSTRAINT using BETWEEN. CREATE TABLE Moo (

	Id NUMBER PRIMARY KEY,
	CONSTRAINT Cow	CHECK (Id BETWEEN 1 AND 30)

HTH,
Brian Received on Tue Oct 30 2001 - 15:02:49 CST