CODEWITHSUNDEEP
sql-serverazure
Ashish Mishra
Ashish Mishra

Reputation: 1

Azure sql cross database trigger

. I have two databases in same azure sql server .i want that both database interact to each other using trigger. i.e If any record is inserted in Customer table of first database the trigger gets fired and record is inserted in another database.

Upvotes: 0

Views: 1123

Answers (3)

Girts S
Girts S

Reputation: 11

Can try EXEC sp_execute_remote and set the periodic batch execution across databases. To me seems that the EXEC sp_execute_remote is not working across databases if started from trigger. If started manually cross DB executions work.

Upvotes: 0

Daniel
Daniel

Reputation: 21

We had / have the same problem with triggers that we use for insert-update-delete where we write a record to Database-1 that has the primary table, but also updates Database-2 where we hold "archive" versions of the tables.

The only solution we have identified and are testing is to bring all of the tables into a single database and separate the different tables under separate database schemas in the one database.

Analysis so far of this approach looks promising.

Upvotes: 2

Ionut N
Ionut N

Reputation: 454

I think what you're trying to do is not allowed in Sql Azure. From my expertise what you are trying to do is a bad practice on-premise as well (think backups-restore and availability issue scenarios). You should move the dependency in the application and have the application update both databases, as appropriate. Anyway, if you want to continue with this approach please take a look over Elastic Query feature: https://learn.microsoft.com/en-in/azure/sql-database/sql-database-elastic-query-overview

Please let me know if I can help with something

Upvotes: 1

Related Questions