Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

performance Tuning 10g reference poster
 
Oracle training in Linux 

commands
 
Oracle training 

Excel
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Closed TopicStart new topic
> Trigger with null old value
Paulafnsc
post Feb 15 2011, 08:41 AM
Post #1


Newbie
*

Group: Members
Posts: 3
Joined: 15-February 11
Member No.: 44,697



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.

Go to the top of the page
 
+Quote Post
burleson
post Feb 15 2011, 09:13 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,100
Joined: 26-January 04
Member No.: 13



Hi Paul,

>> This problem not occurs with the others fields of type number, varchar... only with the timestamp field.

If this works with a DATE consistently, It's got to be a bug!

Check the bugs database on MOSC:

http://support.oracle.com


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Paulafnsc
post Feb 15 2011, 10:45 AM
Post #3


Newbie
*

Group: Members
Posts: 3
Joined: 15-February 11
Member No.: 44,697



QUOTE (burleson @ Feb 15 2011, 09:13 AM) *
Hi Paul,

>> This problem not occurs with the others fields of type number, varchar... only with the timestamp field.

If this works with a DATE consistently, It's got to be a bug!

Check the bugs database on MOSC:

http://support.oracle.com




Thanks, Burleson.

It works fine with DATE, but not TIMESTAMP.

Unfortunately, I haven't an Oracle CSI or Contract Number now.
Is there another way to report a bug or get a list of reported bugs and the patches?


Go to the top of the page
 
+Quote Post
dave
post Feb 16 2011, 03:55 AM
Post #4


Advanced Member
***

Group: Members
Posts: 5,205
Joined: 8-October 04
Member No.: 785



QUOTE (Paulafnsc @ Feb 15 2011, 03:45 PM) *
Thanks, Burleson.

It works fine with DATE, but not TIMESTAMP.

Unfortunately, I haven't an Oracle CSI or Contract Number now.
Is there another way to report a bug or get a list of reported bugs and the patches?


speak to your sales rep and ask what your CSI is
Go to the top of the page
 
+Quote Post
burleson
post Feb 16 2011, 11:21 AM
Post #5


Advanced Member
***

Group: Members
Posts: 11,100
Joined: 26-January 04
Member No.: 13




>> I haven't an Oracle CSI or Contract Number now.

If you are running a "legal" copy of Oracle, you will have a CSI number even if you no longer have purchased technical support (which is REQUIRED to get bugs and patches). . . .

Like Dave sez, call your local CSR (customer support representative) and ask.

Good Luck!


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Closed TopicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 24th April 2014 - 08:36 AM