|
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?
Or, is there a patch for this problem?
I am using Oracle 11.2.0 - Suse Linux
Thank for any help.
|
Trigger with null old value
Feb 15 2011, 08:41 AM
