Re: Limit number of Rows in a table

hello marco,

one way to do it would be to use raise_application_error statement in the trigger and then handle that error in the application. so your trigger would become:

create or replace trigger trg_limit_Customers before INSERT ON tbl_Customers
 declare

 	numRows integer;
 begin           
 	SELECT Count(*) into numRows from tbl_Customers;
 	if (numRows > 25) then
 		raise_application_error
                 (-20000, 'no more than 25 rows in this table.'); 	
        end if;

this would raise an exception with description 'no more than 25 rows in this table.' and you can then handle this exception the wya you like.

hope that would help.

regards,

ATTA
Marco Wolfgarten <mwolfgarten_at_web.de> wrote in message news:<3b41d911$1_at_netnews.web.de>...
> Hi there!
>
>
> I'm trying to limit the number of rows in a table -> No INSERT must be possible.
> I tried to do this using a trigger, but I have no idea to forbid the
> transaction:
>
>
> create or replace trigger trg_limit_Customers before INSERT ON tbl_Customers
> declare
> numRows integer;
> begin
> SELECT Count(*) into numRows from tbl_Customers;
> if numRows>25 then
> ????? --Do not allow to insert more rows!
> end if;
> end;
>
>
> Does anybody know which command I have to use, or which other solution may fit
> this problem?
>
>
> Thanks for your help in advance!
>
>
> Marco
Received on Sat Jul 21 2001 - 16:26:22 CDT