Behaviour of triggers when a table is RENAMED

[oramad]

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.

[adewri]

Best way would be to try it and see what happens...

[oramad]

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.

[slimdave]

Why did you use exp/imp to move the table? Why not "alter table ... move ..."?

[marist89]

or just plain old rename:

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.

[oramad]

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...

[hrishy]

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

[oramad]

Hello Hrishy,

What the hell are you smoking?

Oramad

[DaPi]

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!