Insert into another users table

From: <m_kelly13_at_my-deja.com>
Date: 2000/05/05
Message-ID: <8evb8e$njb$1@nnrp1.deja.com>#1/1

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;
/

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri May 05 2000 - 00:00:00 CDT