Re: Importing Triggers from a Different User

Access wrote:
> "tweiss" <tedleyw_at_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
//
//