24.5.07

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.

4 comments:

Paweł Barut said...

Good to know that as I use returning into quite often.
Thanks for sharing,
Paweł

Anonymous said...

Hey Jornica,

This is behavior really unbelievable! In my opinion, this is a bug in Oracle. What's your opinion on that?

Regards,
Theo

Peter K said...

I would say that this behaviour is as documented in the official Oracle documentation (SQL Reference Guide).

Reading the documentation on the returning clause, and although it didn't explicitly state it, the returning clause deals with values in the affected row(s) and I would read it as that it is part of the insert operation and hence it would not know anything about operations taken by the after-insert trigger or post-statement triggers.

Jornica said...

Theo,

In my opinion it is a feature with enough X-factor to become a bug ;-)

With kind regards,

Jornica