Help with trigger: counting rows...

From: Murray <murrayccole_at_hotmail.com>
Date: Wed, 5 Jun 2002 01:22:16 +1000
Message-ID: <adilv9$dj0$1@bunyip.cc.uq.edu.au>

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 Received on Tue Jun 04 2002 - 10:22:16 CDT