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

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.