May 5, 2022 at 2:13 pm
Hi
I have 2 tables in 2 different DBs, I need to have all the rows of the second table in second DB when a filed(column)is "100" in the first table in the first DB. Should I use Trigger, If Yes, Would you please tell me how?
Thank you.
May 5, 2022 at 5:12 pm
To clarify, there is a column in a table in a database. When it's value is 100, you then need to insert all the rows from that table into a table in a different database?
If it's every row from one table/database to another table/database, a trigger may be cause a lot of blocking and deadlocks.
If you want to update only one row when the value of a column hits 100, then a trigger will work.
A sample set of data and the table structure will help us provide you with a solution
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 5, 2022 at 5:26 pm
Thank you for replying me. No. There are 2 DBs. DB1 and DB2. There are two tables. Table1 in DB1 & Table2 in DB2. There is a filed(column) in Table1 in DB1, I want "whenever" it has the value of 100, can select everything from Table2 in DB2.( I have the sp) I dont know how should i make connection between these two Tables in two different DBs and Trigger my sp in DB2.
May 5, 2022 at 6:29 pm
Three part naming convention is the manner to call a cross database query or procedure.
From database1:
EXEC database2.schema.procedurename
Note, the permissions need to be set up properly in each database.
As for how to execute this, you could create an after update trigger on the table in Database1, and then call the procedure on database2. Or, you can set up a SQL job that runs on a schedule, tests for the value in the column, and if it's 100, execute the proc. A third way would be to add code into whatever process inserts or updates the table in database1, and execute the procedure if needed.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply