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

From: Lee Doty <iarld_at_connectnet.com>
Date: 1997/06/13
Message-ID: <33a1c8d4.17499412@news.connectnet.com>#1/1

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 Fri Jun 13 1997 - 00:00:00 CDT