Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Corrupted date in insetead of trigger
I wrote view on the table that converts varchar formatted date fiels
'YYYYMMDD' from the table into Oracle data type and instead of
triggers to be able to update this fiels back. But my update trigger
corruptes year of the date. I use oracle 8.0.6 on HP Unix . Tried
Oracle 8.0.5 on Solaris - the same problem.
Demo script to reproduce problem is following:
drop table temp_aaa;
create table temp_aaa(
id number,
tdate char(8)
);
insert into temp_aaa(id, tdate) values( 1, '19990426'); insert into temp_aaa(id, tdate) values( 2, '19990425'); commit;
create or replace view vtemp_aaa(id, tdate) as select id, to_date( tdate, 'YYYYMMDD' ) as tdate FROM temp_aaa;
select tdate from vtemp_aaa;
CREATE OR REPLACE TRIGGER tri_temp_aaa INSTEAD OF INSERT ON vtemp_aaa
FOR EACH ROW
BEGIN DBMS_OUTPUT.PUT_LINE(TO_CHAR(:new.tdate,'YYYYMMDD')); INSERT INTO temp_aaa(id, tdate) VALUES (:new.id, TO_CHAR(:new.tdate,'YYYYMMDD')); END;
CREATE OR REPLACE TRIGGER tru_vtemp_aaa INSTEAD OF UPDATE ON vtemp_aaa
FOR EACH ROW
DECLARE Temp date; BEGIN DBMS_OUTPUT.PUT_LINE(TO_CHAR(:new.tdate,'YYYYMMDD')); DBMS_OUTPUT.PUT_LINE(TO_CHAR(:old.tdate,'YYYYMMDD')); --UPDATE temp_aaa SET tdate = TO_CHAR(:new.tdate,'YYYYMMDD') --WHERE id = :old.id; END;
insert into vtemp_aaa(id, tdate) values( 3, SYSDATE );
select * from temp_aaa; Received on Wed May 01 2002 - 12:21:52 CDT