Re: One interesting question on Mutating table problem.......

Joe wrote:
>
> Hi all,
> After I read all the responses on this mutating table subject, it
> brings up one interesting question. The question is how can you
> write a database trigger to prevent inserting or deleting records from
> a table which has a fix number records allowed only.
>
> For instance, if I have a table, GROUP, and it can only have
> 5 groups(records). How do you write a trigger to make sure that
> the record been inserted or deleted still meet the 5 records requirement.
> Thank you for all the helps,
> e-mail joe.kau_at_lmco.com

Hi!

The answer is: you can query mutating table in statement trigger. Example is:

create table test (n number);

create or replace trigger BI_TEST
before insert on TEST
declare
  rec_amount Number;
begin
  select count (*)
    into rec_amount
    from test;
  if rec_amount >= 5 then

     raise_application_error (-20001, 'Limit of records reached!');   end if;
end;
/

-- 
Regards. Alexey Neckrasow. Leaves Inc. Russia.
e-mail: nec_at_leaves.spb.su