Renaming a table while leaving the existing triggers behind

Question

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:

• Back up existing table under a different name (via a rename or something)
• Create a new clustered table under the old name
• Copy all the data from the backup table into the newly created one

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?

Answer 1

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