SQL & PL/SQL

Hello community, i am having a little difficulty with the exporting objects from one user to another specifically the exporting of the trigger.

Here is the situation, because of SOX purpose whenever a update is sent to the client the dba have to execute the script as himself (priviliged user) and is not allowed to log into the schema to make changes. Therefore we perpend the object definitions with &user_schema.. and they define user_schema in sqlplus and execute the update script.

Here is a small example which requires two users (user1 and user2) with the following grants (connect, create table, create trigger, create view, create sequence). Please forgive the naming of the objects, just trying to be as simple as possible.

I start out by logging in as system user via sqlplus and execute the following.

------------------Begin sqlplus----------------------

define user_schema=user1;

create table &user_schema..abc01 (
  col1 number,
  col2 varchar2(20),
  col3 number,
  constraint pk_abc01_col1 primary key (col1)
);

create table &user_schema..xyz01 (
  col1 number,
  col2 varchar2(20),
  col3 number,
  constraint pk_xyz01_col1 primary key (col1)
);

create or replace view &user_schema..view1 as
select x.col1, x.col2, x.col3, a.col1 as acol1, a.col2 as acol2, a.col3 as acol3
from xyz01 x
inner join abc01 a on a.col1 = x.col1;

create sequence &user_schema..seq_xyz01 start with 1 increment by 1;

create or replace trigger &user_schema..trig01
before insert on &user_schema..xyz01 for each row
begin
  if (nvl(:new.col1, -1) = -1) then
    select seq_xyz01.nextval into :new.col1 from dual;
  end if;
end;
/

--------------------End sqlplus----------------------

I would then proceed to export using the exp utility via the command line
exp system/systempassword file=user1.dmp owner=user1

Then import user1 objects into user2
imp system/systempassword file=user1.dmp fromuser=user1 touser=user2

Now the problem:
When i take a look at the sql for user2 trigger (trig01) i see the following (viewed via sqldeveloper)

create or replace TRIGGER "USER2".trig01
before insert on user1.xyz01 for each row
begin
  if (nvl(:new.col1, -1) = -1) then
    select seq_xyz01.nextval into :new.col1 from dual;
  end if;
end;

its referring to user1.xyz01 table, however i want it to point to is user2.xyz01 table. Can someone please help me out or offer another solution to go about this because i need the ability to import the objects into a different user without the import failing and having to recompile the object.

I've also tried executing this while connected as system user via sqlplus:

define user_schema=user1

create or replace trigger &user_schema..trig01
before insert on xyz01 for each row
begin
  if (nvl(:new.col1, -1) = -1) then
    select seq_xyz01.nextval into :new.col1 from dual;
  end if;
end;
/

but that fails stating that table or view does not exist, please help
however that fails because i

Edited by: user3868150 on Nov 6, 2009 6:05 PM