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

Hi,

I have experimented your suggestion
(using BEFORE trigger instead of AFTER trigger) and it's really possible to execute a 'SELECT COUNT(*) FROM TB_A' from 'BEFORE INSERT' trigger of TB_A,
if your insert statement affects only 1 row of the desired table.

However, if you try to insert more than a single row (eg.: insert using select) at once, you will yet receive the mutating table error.

The solution suggested by Brian (UNIQUE + CHECK) is interesting because I wouldn't need a trigger.

Although, I have to know which is the lowest free 'Id' available every time I insert a row;
and I can't use 'MAX()' or 'COUNT()' to do it.

Do you know an easy way to accomplish that?

Any other suggestion to limit the rows of a table to a specific number?

Thank you,
regards,

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

...
> From: Scott Mattes (ScottMattes_at_yahoo.com)
> Date: 2001-10-30 11:28:28 PST
> Isn't there a before insert trigger? Then you should be able to do the
> count(*).
>

...
> >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).
> >
Received on Wed Oct 31 2001 - 08:22:26 CST