Re: Help with trigger: counting rows...

Murray wrote:

> Hi there,
>
> I'm trying to create a trigger to limit the number of rows that can be
> inserted into a table.
>
> Here's the table:
>
> create table item_alloc (
> code char(8),
> year char(4),
> month varchar2(10),
> userid char(8),
> foreign key (code,year,month) references avail_items(code,year,month)
> foreign key (userid) references userprofile(id),
> primary key (code,year,month,userid));
>
> I want to create a trigger that throws an exception if a row is inserted and
> there are already more than 5 rows with the same userid, year and month
> (combined).
>
> The closest I've come to getting this is:
>
> CREATE OR REPLACE TRIGGER checkItemAlloc
> BEFORE INSERT OR UPDATE ON item_alloc
> DECLARE
> counter number;
> toomany EXCEPTION;
> BEGIN
> select count(*)
> into counter
> from item_alloc
> where new.userid = userid and new.month = month and new.year = year;
>
> if (counter >= 5) then
> RAISE toomany;
> end if;
> EXCEPTION
> WHEN toomany THEN
> raise_application_error(-20006,'You can only have 5 items per
> month');
> END;
>
> Can anybody his a newbie with this??
>
> Thanks in advance,
>
> Murray

You are likely going to run into mutating trigger errors querying the table containing the trigger.

Write a small stored procedure, or function, using PRAGMA AUTONOMOUS_TRANSACTION to perform the query and return an integer value 0-5 or a Boolean value True/False.

Daniel Morgan Received on Tue Jun 04 2002 - 10:55:16 CDT