Re: Instead_of_insert trigger

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 12 Dec 2000 21:29:06 -0000
Message-ID: <976656783.29371.0.nnrp-09.9e984b29@news.demon.co.uk>

I have to say that I can't spot an error in the trigger.

The error message, however, is saying the trigger is invalid - what happens when you:

    alter trigger trg_vRF_adv_data compile;

Are there two triggers in different schemas with the same name - and is it the one you are not showing us that is invalid, perhaps ?

The following is a sample of code which appears to be identical to what you are doing. It works on 8.0.5

create table jpl_temp (

        n1      number  primary key,
        v1      varchar2(10),
        v2      varchar2(10)

);

create or replace view jpl_view as select * from jpl_temp;

create or replace trigger jpl_bri
instead of insert on jpl_view
declare

        m_ct number;
begin

        select count(*) into m_ct
        from jpl_temp
        where n1 = :new.n1
        ;

        if m_ct = 0 then
                insert into jpl_temp values(
                        :new.n1, :new.v1, :new.v2
                );
        else
                update jpl_temp
                set v1 = :new.v1, v2 = :new.v2
                where n1 = :new.n1;
        end if;

end;
.
/

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



mkarasick_at_my-deja.com wrote in message <90up0t$f8h$1_at_nnrp1.deja.com>...


>Okay - here is a new copy of the trigger - i am no longer trying to

>insert on the view.

Received on Tue Dec 12 2000 - 15:29:06 CST