Re: Help! Trouble with Triggers

I'd wouldn't rely on a trigger alone to enforce your constraint about max number of rows in your table, as one session cannot see what another session has done until the other session has performed a COMMIT.

However, there is a way around this: Create a primary key constraint on your table, and combine that with a check constraint on the primary key:

Create table Max_4_rows (

    mykey number(1) not null,
    constraint Max_4_rows_pk

        primary key(mykey),
    Constraint Max_4_rows_Check_Key

        check (mykey between 1 and 4)
);

This combination will ensure that at any time no more than 4 rows are present in the table - any attempt at inserting a new one would either be met by "duplicate key in index" or violation of the check constraint

Hope this helps

--
Karl
Oracle Certified DBA

Ron Bakowski wrote in message <349AEC28.5C2B4B82_at_sympatico.ca>...


>I created a table:

> create table test (id integer);

>

>then changed your trigger to BEFORE INSERT

>and count(0) and it works fine!

>

>Hope this helps!

>Ron

>

>

>Matt Larson wrote:

>>

>> I have a table that can only have a certain number of rows (let's say the

>> max is two rows).  I set up a trigger to count the entire table after

 each


>> insert.  The table is initially empty.  I issue the same statements in

 TWO


>> sessions.

>>

>> insert into test values (1);

>> insert into test values (2);

>>

>> After both sessions have completed these two lines, I issued a commit to

>> each.  Both commit and leave a total of 4 rows in the table.

>>

>> How can I make a trigger that will prevent this from happening??

>>

>> Please email answers to matt_larson_at_jdedwards.com

>> Thanks

>>

>> create or replace trigger trigger_test

>> AFTER INSERT on test

>> DECLARE

>> v_variable number;

>> BEGIN

>> select count(*) into v_variable from test;

>> if v_variable > 2 then

>> raise_application_error(-20000,'Too many entries in table');

>> end if;

>> END trigger_test;