Re: Limit number of Rows in a table

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Sat, 21 Jul 2001 21:26:08 GMT
Message-ID: <9hujq0$vpc$1@s1.read.news.oleane.net>

"Marco Wolfgarten" <mwolfgarten_at_web.de> a écrit dans le 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;
>

You can use a trigger sonething like:

v815> create table t (col number);
v815> create or replace trigger ai_t
  2 after insert on t
  3 declare
  4 cnt number;
  5 begin

  6     select count(*) into cnt from t;
  7     if cnt > 10 then
  8        raise_application_error (-20100, 'Too much rows');
  9     end if;

 10 end;
 11 /

v815> insert into t values (0);
v815> insert into t values (1);
v815> insert into t values (2);
v815> insert into t values (3);
v815> insert into t values (4);
v815> insert into t values (5);
v815> insert into t values (6);
v815> insert into t values (7);
v815> insert into t values (8);
v815> insert into t values (9);
v815> insert into t values (10);
insert into t values (10)
            *

ERROR at line 1:

ORA-20100: Too much rows
ORA-06512: at "MMA$EP34087.AI_T", line 6
ORA-04088: error during execution of trigger 'MMA$EP34087.AI_T'

v815> select * from t;

       COL

---

         0
         1
         2
         3
         4
         5
         6
         7
         8
         9

10 rows selected.

v815> delete t where col=9;

1 row deleted.

v815> select * from t;

       COL

---

         0
         1
         2
         3
         4
         5
         6
         7
         8

9 rows selected.

v815> insert into t select * from t;
insert into t select * from t

            *
ERROR at line 1:

ORA-20100: Too much rows
ORA-06512: at "MMA$EP34087.AI_T", line 6
ORA-04088: error during execution of trigger 'MMA$EP34087.AI_T'

v815> select * from t;

       COL

---

         0
         1
         2
         3
         4
         5
         6
         7
         8

9 rows selected.

--
Have a nice day
Michel

Received on Sat Jul 21 2001 - 16:26:08 CDT