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

Look into Oracle's sequences( see the docs) - a trigger could then be used to check the next value - when that value is greater then the max you want, deny the insert..

renatofabiano_at_hotmail.com (Renato Fabiano) wrote:

>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).
>> >

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World!  Check out our new Unlimited Server. No Download or Time Limits! -----== Over 80,000 Newsgroups - 19 Different Servers! ==----- Received on Wed Oct 31 2001 - 09:22:25 CST