Using trigger to insert data into seperate database.
-
Hello c'pians, I have got two SQL databases on seperate machines (master/slave). I am able to connect to both the databases using Enterprise Manager. Both databases have identical tables. What i would like to do is write a trigger which fires when a new row is inserted into the master db and inserts the same data into the slave database. What i would like to know is, is it possible to write a trigger which can insert data into a completly seperate database?
-
Hello c'pians, I have got two SQL databases on seperate machines (master/slave). I am able to connect to both the databases using Enterprise Manager. Both databases have identical tables. What i would like to do is write a trigger which fires when a new row is inserted into the master db and inserts the same data into the slave database. What i would like to know is, is it possible to write a trigger which can insert data into a completly seperate database?
It is possible and fairly straight forward. I am doing this from memory as I only have SQL Server 2005 in front of me (so forgive if directions are slightly off). Open Enterprise Manager, create a linked server on the master DB to the slave DB. Then you will be able to access the slave DB in your trigger basically as if you were acessig another (local) DB. I hope this helps.
-
Hello c'pians, I have got two SQL databases on seperate machines (master/slave). I am able to connect to both the databases using Enterprise Manager. Both databases have identical tables. What i would like to do is write a trigger which fires when a new row is inserted into the master db and inserts the same data into the slave database. What i would like to know is, is it possible to write a trigger which can insert data into a completly seperate database?
Yes, use linked server. Steps would be a) Create Linked server for your slave on master server by using enterprise mgr or sp_addlinkedserver. b) Write Trigger on master table...and do somothing like as below Insert into Slave.Table values(..............) where slave is linked server name..... Simple........ Amit
-
Yes, use linked server. Steps would be a) Create Linked server for your slave on master server by using enterprise mgr or sp_addlinkedserver. b) Write Trigger on master table...and do somothing like as below Insert into Slave.Table values(..............) where slave is linked server name..... Simple........ Amit
hey guys... cheers ever so much for this.. I will be looking into this first thing monday morning. :):):)
-
Yes, use linked server. Steps would be a) Create Linked server for your slave on master server by using enterprise mgr or sp_addlinkedserver. b) Write Trigger on master table...and do somothing like as below Insert into Slave.Table values(..............) where slave is linked server name..... Simple........ Amit
i have just found this good guide on using linked servers http://www.informit.com/articles/article.asp?p=21644&rl=1[^] thanks again