Is it possable to do a ALL SERVER DML Trigger?

Question

My department works on converting a client's database when a client switches from another company to us. We have a RDP session they can connect to and see our software with their data. Once they approve the conversion we copy the data from our server and set it up on site for them.

During the conversion approval phase it would make things a lot simpler for us if we could have some AFTER and INSTEAD OF triggers be running on all databases attached to the server (Our department is a different department than the one that develops the software and they will not make a version for the testing site that does the stuff for us built in for us).

I see two options:

1. Create a DML trigger that either performs the action on all databases attached to the server, but when the database is moved to the live server the action no longer occurs because it is run on the server not the database.
2. Have a DML trigger that deletes itself if it detects that it is no longer attached to the test server and is in a live environment.

From what I read I don't think the first option is possible, but I wanted to check and see. I think I know enough to do the 2nd option, but any examples of how to implement it would be very helpful.

Answer 1

You are not going to be able to create a server-wide trigger, because it has to exist in the same database that the DML changes are occurring in.

Having self-deleting code sounds a bit problematic as well.

I think you are going to have to have a process in place that creates the triggers when a new database is attached, and removes them when you are finished with them. This is likely going to be a manual process.