Date corrupt inside instead of trigger

Date corrupt inside instead of trigger

Post by Alex » Fri, 03 May 2002 01:18:24



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;

 
 
 

1. Using SELECT statement inside an INSTEAD OF UPDATE trigger

I have a view which consists of data from some tables, and I need an INSTEAD
OF UPDATE trigger for it. Some columns in the view is ntext, while they're
bigint in the table where it is created from. The bigint's in the main table
is index values for other tables where they 'point' to the ntext value. So I
have a ntext in my view which I need to get the bigint value for.

I thought I could do code like this:
DirectingService = (SELECT DirectingServiceID FROM DirectingServices WHERE
(DirectingServiceName LIKE  inserted.DirectingServiceName))

(where the first value - DirectingService is a bigint column in my main
table, DirectingServices is a table which holds all the bigint/ntext values
and finally the inserted.DirectingServiceName is a ntext)

But SQL Server won't accept this line - giving me this error message:
Topic: Microsoft SQL-DMO (ODBC SQLSTate: 42000)
Message: Error 306: The text, ntext, and image data types cannot  be
compared or sorted, except when using IS NULL or LIKE operator.

Hope someone can help me with this error!
PS: I'm using SQL Server 2000 Enterprise Edition on my server
--
Andreas H?ber

2. 4000 character limit in dynamic sql

3. Access to Text Data-Type Fields inside Instead-Of-Triggers

4. multi-user updates

5. Corrupted date in insetead of trigger

6. Problem connecting to Oracle

7. Reproducing an Oracle BEFORE trigger in an INSTEAD OF trigger

8. swedish pg_controldata

9. instead-of triggers and before triggers

10. ADO Accessing date fields in Text file - returns NULL instead of date

11. INSTEAD OF Triggers to update multiple base tables in a SQL Server 2000 view

12. INSTEAD OF INSERT TRIGGER with dynamic SQL using EXEC

13. INSTEAD OF triggers on View not firing with DTS