-
Behaviour of triggers when a table is RENAMED
Hello,
What is the default behaviour of the dependency objects especially triggers when the table is renamed and and imported into a new tablespace.
Let's say I have table called 'ORIG_TABLE'.
I exported the table and renamed the table 'ORIG_TABLE' to 'RENAMED_TABLE'.
Now I imported the table with new storage parameters into a different tablespace. I dropped all the indexes on the renamed table (RENAMED_TABLE) and re-created on the newly imported table which is 'ORIG_TABLE'.
I have two triggers on the renamed table 'RENAMED_TABLE'.
My questions are:
What will be the behaviour of the triggers that were there on the table 'RENAMED_TABLE'. I know they will be invalidated once the table is renamed. But If an action is performed on the newly imported table 'ORIG_TABLE' will Oralce automatically recompile the trigger, validate it and then fire the trigger on the new table or will they reference to the renamed table?
Thanks in Advance.
-
Best way would be to try it and see what happens...
Amar
"There is a difference between knowing the path and walking the path."
-
Hello,
I performed the test. The thing is after renaimg and importing the table the triggers were there. But when I dropped the old table (renamed table) the triggers were not there. I wanted to know if that is the default bevaiour or there is something else I am missing.
Thanks in Advance.
-
Why did you use exp/imp to move the table? Why not "alter table ... move ..."?
-
or just plain old rename:
Code:
SQL> create table xyz (x number(10), y varchar2(10), z date);
Table created.
SQL> desc xyz
Name Null? Type
----------------------------------------- -------- ----------------------------
X NUMBER(10)
Y VARCHAR2(10)
Z DATE
SQL> rename xyz to abc;
Table renamed.
SQL> desc xyz
ERROR:
ORA-04043: object xyz does not exist
SQL> desc abc
Name Null? Type
----------------------------------------- -------- ----------------------------
X NUMBER(10)
Y VARCHAR2(10)
Z DATE
By using this method, any triggers attached to xyz will be "moved" to abc.
Jeff Hunter
-
Well let me answer your questions first:
1) The table in question is a range partitioned table and i wanted to reorganize as hash partitioned table.
Now as the table was renamed and imported, shouldn't the triggers reference to the imported table?
What am I missing?
Thanks in Advance...
-
Hi celebguy_dv
Please dont do that.this is not britney spears fan club..this is a geeky fan club for oracle...probably gurls dont like geeks anyway..i am not sure of britney though..
regards
Hrishy
-
Hello Hrishy,
What the hell are you smoking?
Oramad
-
Originally posted by oramad
Hello Hrishy,
What the hell are you smoking?
Oramad
Probably shredded version 7 manuals - if you ain't tried it, don't knock it!
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|