RETURNING clause oddity

I noticed an oddity with the RETURNING clause. Can anyone explain this to me?

I used the RETURNING clause with an insert to get the value of the empno column of the new row. However, RETURNING is not providing the value of empno in the new row, but instead providing the current value of the sequence id_sequence.

This seems to be caused by a trigger incrementing the value of the sequence (I created the trigger only to see if incrementing the sequence had an effect, and it did!). However, since the RETURNING clause is supposed to return what is in empno, I don't understand why it is influenced by the trigger incrementing the sequence.

Here are the commands I issued. As you can see, empno in the new row is 101, but RETURNING puts 102 (the current value of the sequence) in the variable emp_id:

SQL> SELECT id_sequence.NEXTVAL FROM DUAL;

  NEXTVAL

      100

VARIABLE emp_id NUMBER
VARIABLE v_name VARCHAR2(15)
INSERT INTO emp (empno, ename)
VALUES (id_sequence.NEXTVAL, 'TOMMY')
RETURNING empno, ename INTO :emp_id, :v_name

SQL> SELECT * FROM EMP;     EMPNO ENAME
--------- ---------------

      101 TOMMY SQL> PRINT emp_id;

   EMP_ID

      102

SQL> SELECT id_sequence.CURRVAL FROM DUAL;

  CURRVAL

      102

This is the useless trigger I put on the table... it seems to be affecting what the RETURNING clause returns for empno:

create or replace trigger trigger1
after insert on emp
for each row
DECLARE
   tempvar number;
BEGIN    select id_sequence.nextval into tempvar from dual;

END;
/ Received on Sat Aug 04 2001 - 20:37:01 CDT