Have a trigger stored in database1 that triggers when a table in database2 is updated, both databases in a linked server?

Question

As the long title says:

I have the database of "my" software (lets call it Database2) and a 'local' work database of my team to work and create objects (Lets call it Database1), that is linked via linked server with the Database2. I know how to write and make complex triggers, I've done some in the Database2.

What I want to know is, can I write a trigger and store it in the Database1 that will look and trigger when the update I want to monitor happens in a table in Database2?

The motive for this is that we can no longer create objects in the Database2, we can only create objects in Database1.

If its possible, could you show me what is necessary for me to put in it so it 'look' to another database? Or at least show me some article or key works that I can research about? I tried to look it on google but couldn't find anything about it.

Thanks

Answer 1

1. If both databases Database1 and Database2 are in same server: you can do something like INSTEAD OF UPDATE trigger on Database2, which takes care of updating both Database1 and Database2.Refer to MSSQLTips for cross database linked server

2. If both databases Database1 and Database2are in different servers: don't use linked server to UPDATE another table inside trigger. Create a job in server of Database2, which polls for the changes in the Database2 and inserts into Database1. Refer to SO Post

Update:

3. If you are not able to create objects in Database2: you can create job in Database1 server, which polls for the changes in the Database2 table using linked server, every N minutes (depending on your latency SLA) and then propagate those changes to Database1. It is similar to Option 2, but the job residing on Database1 server side, which is polling for changes.