Re: Insert into another users table

I belive that your problem is: :new.s.table_4.id=:new.w.table_1.id Unless things have changed with ver 8.1 the only table that :new and :old refer to are the table being updated by the triggering statement and not the target of any SQL within the trigger. Also you shoud just refer to the triggering table columns as :new.id. If helps people determine your actual problem if you post the error messages with the code.

In article <8evb8e$njb$1_at_nnrp1.deja.com>,   m_kelly13_at_my-deja.com wrote:
> sys info: Oracle 8i on Solaris
> user has priviliges
>
> I am trying to insert into another user's table based on the status.
> However, I
> get a bind varible error whenever I try to create the following
 trigger:
>
> create or replace trigger wtable_1_status after update of status on
> w.table_1 for each row
> begin
> delete from s.table_4 where id = :new.id;
> select (id, name) into s.table_4 from w.table_2
> where :new.s.table_4.id=:new.w.table_1.id and new.status='S';
> end;
> /
>
> w.table_1 (
> id number(2),
> rev number(2),
> status varchar2(1));
>
> w.table_2 (
> id number(2),
> rev number(2),
> name varchar2(1));
>
> s.table_3 (
> id number(2),
> rev number(2),
> status varchar2(1));
>
> s.table_4 (
> id number(2),
> rev number(2),
> name varchar2(1));
>
> The following triggers works fine...
>
> create or replace trigger wtable_2 before insert on w.table_2 for each
> row
> begin
> insert into w.table_1 (id, rev, status) values (id, rev, 'W');
> end;
> /
>
> create or replace trigger stable_4 before insert on s.table_4 for each
> row
> begin
> insert into s.table_3 (id) values (:new.id);
> end;
> /
>

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.