Re: HELP: Attempting to use Before Triggers and Sequences to auto-generate Primary Keys

Lee,

By syntax definition of seq.NEXTVAL, you can use only SQL statement, like

        SELECT test_seq.nextval into :new.id from dual;

Also, I think it is a typing mistake in your post (the table name must be test not "Activator". Your code works fine for me (after correcting the table name in your trigger).

Hope this will help.

Bala.

Lee Doty wrote:
>
> So,
>
> anyone have any idea if this is possible? The functionality I want
> is:
>
> CREATE TABLE Test
> (
> id int,
> dummy1 int,
> dummy2 int
> );
>
> <create the sequence Test_SEQ>
>
> CREATE OR REPLACE TRIGGER Test_TRG
> BEFORE INSERT
> ON Activator
> FOR EACH ROW
> WHEN ( new.Id IS NULL )
> BEGIN
>
> :new.Id := Test_SEQ.NEXTVAL;
>
> END;
>
> INSERT INTO Test
> ( dummy1, dummy2 )
> VALUES
> ( 1, 2 )
>
> ...and have the trigger use the sequence to automaticly insert the
> NEXTVAL as the Id.
>
> (we're doing this to try to emmulate SQL Server IDENTITY column
> functionality, since we are porting a very large system and want to
> maintain code compatibility)
>
> Here are the problems:
>
> -Oracle complains about the when clause
> -Oracle complains about the assignment from the sequence
>
> Why? I certainly don't understand why oracle by convention would
> stipulate that you can't use a sequence in an assignment statement
> (which seems to be the case) Why not?
>
> Is the problem with the WHEN Clause related to the IS NULL bit?
>
> Is there any way to pull this kind of insert off? Is there something
> I'm missing?
>
> Any help would be GREATLY appreciated.
>
> -Lee
> ----
> "It can't rain all the time"
Received on Mon Jun 16 1997 - 00:00:00 CDT