Problem solve Get help with specific problems with your technologies, process and projects.

Triggers not working after export from user X to user Y

I did an export at the user level from a user X. Then I imported all the objects of the user X to a user Y. All was fine, but some triggers are not working. Those triggers kept the schema of the user X in the triggering statement.
1. Why?
2. Is there a way to resolve this problem without dropping and recreating the triggers?

1) Triggers are owned by the person who created them. When you export into another user's schema, the trigger will still be owned by the original creator. In other words, the trigger will be imported but it will not "switch ownership". The trigger would still fire when appropriate, but will be ran under the definer rights model. This means that whoever executes the object (in this case, the trigger), will execute it with the rights/privileges of the definer (i.e. the owner/creator) so the objects used in the trigger (tables and such) will be pointing to the original owner's tables. If, however, the trigger was written to execute using the invoker rights model, then when the trigger executed, it would run with the rights/privileges of the invoker and therefore would point to the "touser" user's objects.

2) If you want the new user to own the trigger, then they need to be created by the new user. If that's what you want, it'd likely be best to export with the triggers=n parameter and then create a script to run after the import which would create all the triggers under the new user's schema.

For More Information

Dig Deeper on Oracle database export, import and migration

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

How can i write a trigger with invoker rigths model.