Re: How to build this specific trigger ?

There is many mistakes in your trigger:

1)
Syntax is select nvl(max(id),1) into new_id from msg.test; to get the maximum id and 1 if there is no row. not new_id := select max(id,1) from msg.test;

2)
Syntax is :new.id := new_id + 1;
not id := new_id + 1;

3)
To do what you want you must use a trigger for each row. But in this case you cannot select in your current table, that is you cannot get your max number.

5)
The best way to do this is to use a sequence to generate your id:
create sequence my_seq start with 1 increment by 1 order; (order if you want to have absolutly ascendant id but is that any meaning with simultaneous transactions that doesn't commit in the same order as they get the sequence number).

in your trigger use, for example:
select my_seq.nextval into new_id from dual; :new.id := new_id;

This assumes that you can have some holes in your id sequence in the test table because of the rollbacks or shutdowns.

6)
If you want absolutly id in the order without hole, the only way is to use a table with the last number. Each transaction locks the table, gets the number, increments it, inserts into the table test and commits. But that's mean that all transactions are serialized on the lock of the number table.

Finally, as you calculate the id of the inserted row, you have to use a before insert trigger.

Olivier Utkala a écrit dans le message <7rlvaj$inb$1_at_wanadoo.fr>...
>
>I need a trigger who incremente automatically the id column of a table.
>Is this trigger is BEFORE INSERT or AFTER INSERT ?
>I try some test and I have the returned error : ORA-04098.
>
>The SQL code of one of my test :
>
>CREATE OR REPLACE TRIGGER "MSG".INS_TEST BEFORE INSERT OR UPDATE OF "ID" ON
>"MSG"."TEST" DECLARE
> new_id INTEGER;
>BEGIN
> new_id := select max(id,1) from msg.test;
> id := new_id + 1;
>END;
>
>Thanks for any help.
>
>---------------------------------
>Olivier
>
>
Received on Wed Sep 15 1999 - 03:33:42 CDT