SEO

permalink · #1 (**permalink**) 02-24-2008, 08:41 AM

Hi,

I did an export at the user level from a user1. Then I imported all
the objects of the user1 into user2. Everything seemed to come over
fine with the exception of some triggers - they kept the schema of
user1 in the triggering statement. This only happened with about half
of them - the other half listed user2 in the triggering statement. I
can find no differences between the ones that retain the original
schema and those that inherit the new one.

Does anyone have any idea what might be happening here???

Thanks,
Ted

permalink · #2 (**permalink**) 02-24-2008, 08:41 AM

"tweiss" <tedleyw@yahoo.com> wrote in message
news:a5eedefd.0411031205.77975d32@posting.google.c om...
> Hi,
>
> I did an export at the user level from a user1. Then I imported all
> the objects of the user1 into user2. Everything seemed to come over
> fine with the exception of some triggers - they kept the schema of
> user1 in the triggering statement. This only happened with about half
> of them - the other half listed user2 in the triggering statement. I
> can find no differences between the ones that retain the original
> schema and those that inherit the new one.
>
> Does anyone have any idea what might be happening here???
>
> Thanks,
> Ted

If you hard-coded user1 in the trigger syntax, this won't change during the
import. The same thing for pl/sql packages and views.

Matthias

permalink · #3 (**permalink**) 02-24-2008, 08:42 AM

Access wrote:
> "tweiss" <tedleyw@yahoo.com> wrote

>>I did an export at the user level from a user1. Then I imported all
>>the objects of the user1 into user2. Everything seemed to come over
>>fine with the exception of some triggers - they kept the schema of
>>user1 in the triggering statement. This only happened with about half
>>of them - the other half listed user2 in the triggering statement. I
>>can find no differences between the ones that retain the original
>>schema and those that inherit the new one.
>>
>>Does anyone have any idea what might be happening here???

> If you hard-coded user1 in the trigger syntax, this won't change during the
> import. The same thing for pl/sql packages and views.

Yes. If you created the trigger with the schema name in the create
statement, it will dutifully create a new trigger on the original table.

i.e. if the create statement looks like:

CREATE OR REPLACE TRIGGER mytrigger BEFORE INSERT OR UPDATE ON
user1.mytable REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
BEGIN
/* code goes here*/
END;

upon import, it will create a trigger on user1.mytable and you will have
two triggers user1.mytrigger and user2.mytrigger, both of which act on
user1.mytable.

If you omit the schema name in the create statement, it will
export/import the way most people think it should.

The problem is that these triggers were probably created some time ago
by somebody else, so it can be tough to figure out if it was done
"correctly" at create time. Another gotcha is that some tools like TOAD
will "helpfully" add the schema name if you ask it to generate a create
statement.

Some people think this is a bug. Others consider it a feature. But
that's what's going on.

--
//-Walt
//
//

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Importing Triggers from a Different User