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

On 31 Oct 2001 06:22:26 -0800, 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?
>

For that you can use a BEFORE trigger.

The trigger can do a LOOP on all the numbers availible until it finds one not in the table.

As an alternative, (and I don't know if this works, it just looks cool).

SELECT
        RN
INTO
        Unused_Id
FROM

	(
	  SELECT ROWNUM RN, Id FROM 
                  	(SELECT Id FROM MyTable ORDER BY Id)
	)
WHERE
	RN <> Id
AND	ROWNUM = 1;

HTH,
Brian Received on Wed Oct 31 2001 - 09:47:08 CST