SQL & PL/SQL

Hello!

I have the following problem. I would be very glad if someone could help me with this!

I have a trigger "before update" that change some values, including a timestamp column. My sql code does an update using the "returning clause" to get the values changed by the trigger. The problem is:
When I do an "update [...] returning timestamp_field", this timestamp_field has the old value equals to null (in the trigger). And, this field in the table is not null. This problem not occurs with the others fields of type number, varchar... only with the timestamp field.

Here are the code to simulate this problem:
--Table
create table TB_TEST
(
ID NUMBER(10) not null,
FLAG NUMBER(10) not null,
TAG VARCHAR2(16) not null,
TS_ATU_DTR TIMESTAMP(9) not null
);

INSERT INTO tb_test
VALUES (1, 123, 'teste trigger', CURRENT_TIMESTAMP);

create table textolog ( data date, texto varchar2(100) );

--Trigger
CREATE OR REPLACE TRIGGER TEST_UPD_TS
BEFORE UPDATE ON TB_TEST
FOR EACH ROW
BEGIN

insert into textolog values ( CURRENT_DATE, '1:old.FLAG=' || :old.FLAG || ', :new.FLAG='||:new.FLAG);
insert into textolog values ( CURRENT_DATE, '1:old.TAG=' || :old.TAG || ', :new.TAG='||:new.TAG);
insert into textolog values ( CURRENT_DATE, '1:old.TS_ATU_DTR=' || :old.TS_ATU_DTR || ', :new.TS_ATU_DTR='||:new.TS_ATU_DTR);

:new.TS_ATU_DTR := CURRENT_TIMESTAMP;
:new.tag := 10;

insert into textolog values ( CURRENT_DATE, '2:old.FLAG=' || :old.FLAG || ', :new.FLAG='||:new.FLAG);
insert into textolog values ( CURRENT_DATE, '2:old.TAG=' || :old.TAG || ', :new.TAG='||:new.TAG);
insert into textolog values ( CURRENT_DATE, '2:old.TS_ATU_DTR=' || :old.TS_ATU_DTR || ', :new.TS_ATU_DTR='||:new.TS_ATU_DTR);

END;
/

--Procedure
CREATE OR REPLACE PROCEDURE test1
IS
TSAtuDTR timestamp;
BEGIN
UPDATE TB_TEST
SET
FLAG = 321
WHERE ID = 1;
returning TS_ATU_DTR
into TSAtuDTR;
dbms_output.enable(1000000);
dbms_output.put_line ('O timestamp obtido eh: ['||TSAtuDtr||']');
END;
/

--Testing

exec test1;

select * from textolog order by data, texto;

DATA TEXTO
----------------------------------------------------------------------------------------------------
15-FEB-11 1:old.FLAG=123, :new.FLAG=321
15-FEB-11 1:old.TAG=teste trigger, :new.TAG=teste trigger
15-FEB-11 1:old.TS_ATU_DTR=, :new.TS_ATU_DTR=
15-FEB-11 2:old.FLAG=123, :new.FLAG=321
15-FEB-11 2:old.TAG=teste trigger, :new.TAG=10
15-FEB-11 2:old.TS_ATU_DTR=, :new.TS_ATU_DTR=15/02/2011 11:07:38.094284000


"old.TS_ATU_DTR=, " Isn't it right????? :?
Why the other fields aren't null?
I need the "old.TS_ATU_DTR" to use in my other trigger to compare timestamps, how can I get it?

I think that the problem maybe a bug. I am using the release 11.2.0.1.0.

Could anyone try this on the most recent version of Oracle (11.2.0.2) and post the results?

Thank for any help.

Edited by: 837253 on 16/02/2011 10:35