Azure SQL Trigger Across Multiple Databases

Question

Here's what I'm trying to accomplish - I have one server with say database A and database B. I want to set up a trigger on Database A to watch for table creation. Upon a table creation, I want to take parts of the data (table schema should be identical every time) of the created table and place it into a specific table on Database B.

I've been trying to do some research on this, I initially started digging into elastic querying to possibly get the job done, but I'm starting to think this approach may not work, as it appears elastic querying is read-only. Is there a similar approach I could use with t-sql or is there an approach I'm possibly not coming up with?

Martijn Pieters · Accepted Answer · 2020-09-19 07:30:56Z

1

If table A has a TIMESTAMP / ROWVERSION column, or you can add to that table a column like that, then you can use an Azure Logic App to create that trigger. The Logic App has a SQL connector that uses a polling mechanism to query a table for changes using the TIMESTAMP / ROWVERSION column. This data type is specifically designed for this kind of processing in SQL. The polling query essentially selects all rows where the rowversion is greater than the last polled value. The behavior is reliable since the column is controlled by SQL Server and the performance is extremely fast in the case where there is no new data. When there is new data, the performance is comparable to a simple row query.

edited Sep 19, 2020 at 7:30

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

answered Mar 6, 2019 at 15:28

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Azure SQL Trigger Across Multiple Databases

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related