触发插入，更新，删除以复制表数据 [英] Trigger for insert,update,delete to copy a table data

问题描述

Hii to all

我有一个要求，我有两个表（i）mainlog，存在于sqlserver（ii）sparelog中，存在于MySql中，存在于不同的服务器位置。我需要编写触发器将主日志表数据复制到备用日志表。当对主日志表执行任何DML操作时，触发器必须自动触发并反映出存在于不同服务器位置的备用日志表。我已经创建了一个Linkedserver来自我本地Sqlserver的Mysql。

我尝试过编写以下触发器并成功将表数据从mainlog表复制到sparelog表中，仅用于插入操作，并且在sqlserver的同一个实例中： -

Hii to all
I have a requirement that,i have two tables (i)mainlog,present in sqlserver(ii)sparelog,present in MySql which is present in different server location.I need to write a trigger to copy a mainlog table data to sparelog table.when ever any DML operation is performed to mainlog table the trigger has to fire automatically and reflect the same to sparelog table present in different server location.I have already created a Linkedserver to Mysql from my local Sqlserver.
I have tried by writing the following trigger and successfully copied the table data from mainlog table to sparelog table for only insert operation,and with in the same instance of sqlserver:-

CREATE TRIGGER LogRecords
ON mainlog FOR INSERT
AS
BEGIN
 SET NOCOUNT ON
 IF EXISTS(SELECT * FROM mainLog)
  INSERT INTO sparelog
  SELECT * FROM inserted
 ELSE
  INSERT INTO spareLog
  SELECT * FROM inserted
END 

上述触发器仅在两个表存在于同一sqlserver中时才有效。任何人帮助我如何纠正我的上述触发器，以便我可以执行当两个表存在于不同的服务器位置时，只有单个触发器的所有插入，更新，删除操作。

我使用sqlserver作为我的源服务器，MySql作为目标服务器...提前感谢

The above trigger working only when both the tables are present in same sqlserver.Can any one help me how to rectify my above trigger so that i can perform all the Insert,Update,Delete, operation with only single Trigger when both the tables present in different server location.
Iam using sqlserver as my source server and MySql as Destination server...Thanks in advance

推荐答案

您需要查看链接的服务器。这允许SQL服务器实例与另一个实例（通常是SQL Server）通信，包括MySql。这篇文章告诉你如何创建一个到MySQL的链接服务器：



www.sqlservercentral.com/Forums/Topic340912-146-1.aspx [ ^ ]



我从来没有这样做过MySQL，所以我可以没有直接帮助，但链接服务器的使用已有详细记录和理解。

You need to look into linked servers. This lets a SQL server instance talk to another instance (usally of SQL Server) including MySql. Here is a post that tells you how to create a linked server to MySQL:

www.sqlservercentral.com/Forums/Topic340912-146-1.aspx[^]

I''ve never done this to MySQL before so I can''t help directly but the use of linked servers is well documented and understood.

您可以在链接服务器的帮助下尝试使用OPENQUERY

You can try with OPENQUERY with help of Linked Server

CREATE TRIGGER LogRecords
ON mainlog
FOR  INSERT
AS
BEGIN
	SET NOCOUNT ON
	IF EXISTS(
	       SELECT *
	       FROM   mainLog
	)
	BEGIN
	    INSERT OPENQUERY(MySQLLinkedSvr,'SELECT * FROM Table')
	    SELECT * FROM   INSERTED
	END
	    
	 --------------Rest of your Code-----------------------------
END 

欲了解更多信息，请点击此链接：

http:// msdn.microsoft.com/en-us/library/ms188427.aspx [ ^ ]

这篇关于触发插入，更新，删除以复制表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！