Behaviour of triggers when a table is RENAMED
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Behaviour of triggers when a table is RENAMED

  1. #1
    Join Date
    Oct 2002
    Posts
    22

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Best way would be to try it and see what happens...
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Oct 2002
    Posts
    22
    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.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Why did you use exp/imp to move the table? Why not "alter table ... move ..."?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Oct 2002
    Posts
    22
    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...

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828
    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

  8. #8
    Join Date
    Oct 2002
    Posts
    22
    Hello Hrishy,

    What the hell are you smoking?

    Oramad

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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
  •  



Click Here to Expand Forum to Full Width