Re: Corrupted date in insetead of trigger

From: Chris Leonard <chris_at_databaseguy.com>
Date: Tue, 14 May 2002 12:02:16 -0500
Message-ID: <tqbE8.49$TC5.36583@news.uswest.net>

Alexei,

Maybe this is specific to 8.0. I ran your scripts on 9i without any problems. Do you have access to any more current Oracle server versions?

Chris

---

Chris Leonard
MCSE, MCDBA, MCT, OCP, CIW
The Database Guy at PPI
http://www.propoint.com
Brainbench MVP for Oracle Admin
http://www.brainbench.com

"Alexei" <alexei_sokolovski_at_epam.com> wrote in message news:8a1a890f.0205010921.4d6d9600_at_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 Tue May 14 2002 - 12:02:16 CDT