Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> HELP: Attempting to use Before Triggers and Sequences to auto-generate Primary Keys
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