Trigger sequence oracle problem

Question

I have a sequence :

CREATE SEQUENCE test_seq START WITH 10000001 INCREMENT BY 1;

and a table :

create table Entry(
id number(5),
name varchar(50)  );

I need to increment the value of sequence after inserting a row in the Entry table. So i did this:

CREATE OR REPLACE TRIGGER test_trigger
after INSERT
ON Entry
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_seq.nextval INTO :NEW.id FROM dual;
END;
/

but 'after' does not work here but 'before' works. How can i do it for after?

Answer 1

Usually, you call the sequence.nextval in the INSERT statement itself:

INSERT INTO your_table
   (id, ...)
VALUES
   (test_seq.nextval, ...)

Are you set on using the trigger?

Answer 2

According to this documentation, you can not update the :NEW values while using an AFTER INSERT trigger. Sorry!

Answer 3

If you really want to increment the sequence after the insert (which seems peculiar) you can do this:

CREATE OR REPLACE TRIGGER test_trigger
after INSERT
ON Entry
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE
    l_id INTEGER;
BEGIN
    SELECT test_seq.nextval INTO l_id FROM dual;
END;
/

Of course, this doesn't set the ID of the new row to that sequence value - but if you had wanted to do that, you would have made it a BEFORE trigger.

Answer 4

I can't understand why you'd want to increment the sequence after the insert; you're going to lose a significant number of values doing it that way, because the sequence's context CURRVAL can't be interrogated in a session until that session has called NEXTVAL once.

Running the following will raise an ORA-08002:

create sequence s1;
select s1.currval from dual;

So the way you've coded it with an after insert trigger, where does the first value for entry.id come from?