CODEWITHSUNDEEP
sqlsql-servertriggers
cashonly
cashonly

Reputation: 119

How can I have a trigger on the same table in multiple databases without modifying the DBs?

I have a server with dozens of "child" databases that all have the same schema/structure. I also have a controller DB which is totally different. What I need to do, without creating triggers in each of the child databases, is to have a trigger on the same table in each child DB... like maybe have it reside in the controller DB? Is something like this possible? And if so, how?

Upvotes: 0

Views: 146

Answers (1)

anon
anon

Reputation:

What I need to do, without creating triggers in each of the child databases, is to have a trigger on the same table in each child DB... like maybe have it reside in the controller DB? Is something like this possible?

No. A trigger is tightly coupled to the object it references, and therefore has to live inside the same schema, inside the same database.

  • Perhaps you can have a central queue table in the controller DB, the triggers just insert their relevant data into that central table, and a background process comes around and applies whatever the trigger was supposed to do to the relevant database. This at least minimizes the business logic inside the triggers.

  • If a central queue table doesn't make sense, then you should be able to mitigate most of the concern through automation / deployment. I once managed 100s of databases with identical schema / procedures / triggers, and deploying a change to all of them was not really any harder than deploying a single change to one of them.

Upvotes: 1

Related Questions