Returning into clause and post statement triggers

If you are using RETURNING INTO clauses with DML statements and you are also using (post statement) triggers then you should be aware of the following caveat where the values of the RETURNING INTO do not match the column values.

To show this behavior, set up a small test case first. A table t1 with one column c1 with an after insert statement trigger, modifying the c1 column.

SQL> CREATE TABLE t1(c1 VARCHAR2(10));

Table created.

SQL>
SQL> CREATE TRIGGER t_ais_t1 AFTER INSERT ON t1
2  BEGIN
3    UPDATE t1
4    SET c1 = 'TRIGGER';
5  END;
6  /

Trigger created.

Insert a row in t1 and return the new value of c1 into bind variable c1.

SQL> VARIABLE c1 VARCHAR2(10)
SQL>
SQL> INSERT INTO t1(c1)VALUES('SQLPLUS') RETURNING c1 INTO :c1
2  /

1 row created.

And inspect the contents of the table and the value of the bind variable.

SQL>
SQL> SELECT c1 "column",
2    :c1 "bind"
3  FROM t1
4  /

column     bind
---------- ----------
TRIGGER    SQLPLUS

1 row selected.

As expected the value of column c1 is TRIGGER. The value is the bind variable is SQLPLUS. This value is set before the after statement trigger starts.

Although this code is not production code, you might notice this behavior in production code as well: I discovered this behavior hidden in a mutating table workaround.