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?
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.
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?
According to this documentation, you can not update the :NEW values while using an AFTER INSERT trigger. Sorry!
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?
