CODEWITHSUNDEEP
databaseoracle-databasetable-rename
Ceiling Gecko
Ceiling Gecko

Reputation: 3186

Renaming a table while leaving the existing triggers behind

A decision was made to join a couple of existing tables together in a cluster.

This obviously means that the tables need to be recreated so they can be clustered together, however the existing data should be preserved.

Naturally I assume the procedure would be something along the lines of:

However the current table has quite a few triggers assigned to it (and correct me if I'm wrong here but) when I'll execute the rename operation on that table I assume all the triggers assigned to it by order of convenience will refactor themselves to match the new name.

The perfect scenario in this case would probably be for the triggers to temporarily "detach" themselves from the table once it gets renamed (still point to the old table name which would not exist at that point), and then would be once again functional when the newly created clustered table appears.

However I'm not sure if this is possible.

So the question here is: Can I leave the triggers behind when renaming the table, or should I deal with them manually?

Upvotes: 4

Views: 5733

Answers (2)

Richard
Richard

Reputation: 3100

Yes you are correct: When you rename a table its associated triggers will still refer to the same table. ( Note the trigger names themselves will not be renamed ).

I had this problem so I wrote a script that utilised DMBS_METADATA.GET_DDL to extract the trigger(s) code for the table. The process was:

For each table T:

  1. For each trigger, constraint, check constraints, non-pk indexes on T extract to DDL using DMBS_METADATA.GET_DDL
  2. Rename the table T to OLD_TABLE_NAME
  3. Create the new table ( you could use CTAS if you want to migrate data )
  4. Drop ( or rename ) all triggers, constraints, indexes from OLD_TABLE_NAME
  5. Run extracted DDL to recreate trigger, constraint, indexes on new table

Upvotes: 3

David Aldridge
David Aldridge

Reputation: 52346

The triggers do not reference the table by name, only the DDL that creates them does that. They reference the internal identifier of the table, so when you rename the table the trigger does not change at all. However, if you reverse-engineer the DDL for the trigger from the database the code will of course reference the new name of the table. Code inside the trigger is not changed if it specifically references the table name, but that's hopefully not the case.

So of course the triggers cannot be disassociated from the table, and the best you can do is export the DDL for the triggers, indexes privileges etc..

Similarly indexes do not directly reference table names.

It sounds like an underlying problem here is that you are not using a code repository, because that would enable you to just rerun the scripts required for granting privileges, creating indexes, applying trigger etc after renaming the table and dropping the associated schema items.

Upvotes: 4

Related Questions