Corrupted date in insetead of trigger

From: Alexei <alexei_sokolovski_at_epam.com>
Date: 1 May 2002 10:21:52 -0700
Message-ID: <8a1a890f.0205010921.4d6d9600@posting.google.com>

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 );

• Error here update vtemp_aaa set id = 1 where id = 1;
• Result year
• is 9190 ???
• why ?

select * from temp_aaa; Received on Wed May 01 2002 - 12:21:52 CDT