Date corrupt inside instead of trigger

From: Alexei <alexei_sokolovski_at_epam.com>
Date: 1 May 2002 09:18:24 -0700
Message-ID: <8a1a890f.0205010818.2467b89d@posting.google.com>

Seeking help for the problem:

I wrote view on the table that converts character representation of date from the table ( whis is in 'YYYYMMDD' format ) into Oracle data format in view . Then I wrote
instead of triggers to make it possible to update this view and convert data back to character representation. But in my update trigger I have corrupt date. The year is 9190 ???

I use oracle 8.0.6 on HP Unix. Tried the same on Oracle 8.0.5 on Solaris - the same problem.

Test scripts is attached below to represent the problem:

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

update vtemp_aaa set id = 1 where id = 1;
-- Trigger writes

• 91900101
• 91900101

select * from temp_aaa; Received on Wed May 01 2002 - 11:18:24 CDT