From: "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Instead_of_insert trigger
Date: Tue, 12 Dec 2000 21:29:06 -0000
Message-ID: <976656783.29371.0.nnrp-09.9e984b29@news.demon.co.uk>
References: <90mklp$72r$1@nnrp1.deja.com> <976181862.1971.0.nnrp-02.9e984b29@news.demon.co.uk> <90up0t$f8h$1@nnrp1.deja.com>
NNTP-Posting-Host: jlcomp.demon.co.uk
X-NNTP-Posting-Host: jlcomp.demon.co.uk:158.152.75.41
X-Trace: news.demon.co.uk 976656783 nnrp-09:29371 NO-IDENT jlcomp.demon.co.uk:158.152.75.41
X-Complaints-To: abuse@demon.net
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Lines: 66



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@my-deja.com wrote in message <90up0t$f8h$1@nnrp1.deja.com>...
>Okay - here is a new copy of the trigger - i am no longer trying to
>insert on the view.