Trigger for insert,update,delete to copy a table data

Question

0.00/5 (No votes)

See more:

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:-

SQL

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

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

Posted 26-Feb-13 2:32am

Ram7 from Hyderabad

Updated 26-Feb-13 20:27pm

v5

Add a Solution

2 solutions

Solution 2

You can try with OPENQUERY with help of Linked Server

SQL

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

For more information have a loon on this link:
http://msdn.microsoft.com/en-us/library/ms188427.aspx[^]

Posted 27-Feb-13 0:25am

Khorshed Alam, Dhaka

Updated 27-Feb-13 0:26am

v2

Comments

Ram7 from Hyderabad 27-Feb-13 7:05am

Dear Khorshed,thanks for ur responce, i have tried with ur above rectified trigger but iam getting an error like this:-
Procedure LogRecords, Line 9
MSDTC on server 'RAMU-IBX' is unavailable.
where 'RAMU-IBX'is my DSN name.Can u please guide me where iam doing mistake

Khorshed Alam, Dhaka 27-Feb-13 23:15pm

Dear Ram7 sorry to answer you being late.It seems the RAMU-IBX server's Microsft Distributed Transaction Co-Ordinator is off. Please follow the link for the workaround.
http://blog.sqlauthority.com/2010/03/24/sql-server-fix-error-8501-msdtc-on-server-is-unavailable-changed-database-context-to-publisherdatabase/

Ram7 from Hyderabad 27-Feb-13 23:53pm

Dear Khorshed,i have started Distributed Transaction Co-ordinator with help of ur above link,and again,iam getting the new error like below,please check the below error and guide me how to overcome this,thanks in advance..
Error:-"OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[MySQL][ODBC 5.1 Driver]Optional feature not supported".The operation could not be performed because OLE DB provider "MSDASQL" for linked server "MYSQL" was unable to begin a distributed transaction".

Khorshed Alam, Dhaka 28-Feb-13 0:02am

I am not sure though, but ppl says it is a bug of MySQL. See the link
http://social.msdn.microsoft.com/Forums/is/sqldatabaseengine/thread/5c312706-5144-4a71-83b4-af63dec6810a

Can you test it out of the trigger? Create a stored procedure and test your work like
Create Procedure TestInsertMySQL
AS

INSERT OPENQUERY(MySQLLinkedSvr,'SELECT * FROM Table')
SELECT * FROM SomeTable

Ram7 from Hyderabad 28-Feb-13 0:09am

Thanks Khorshed,i will try with the above link, or else i will go with procedure,
can u please tell me,how many possible ways of migrating data from one server database to another,so that i can try with those alternatives

Khorshed Alam, Dhaka 28-Feb-13 0:22am

You are welcome!, to be honest, the way you are following to transfer data from completely different environment is not the most preferable way .Trigger based solution is preferable to work with the same environment. Better to export data in Flat file (.csv, or fixed length) from SQL Server via Export Import Wizard or SSIS packge. I am not an expert of MySQL, If there any facility of job scheduling then you can schedule a job to pull the data from the files. This solution will work if you do not need any real time in both places. In SQL server there are another feature called SSIS using this features you can expert SQL server data to another data source. There are also several ways but all are not top of my head right now. I will let you inform If I can think of any better solution. Hope you will get your job done soon!

Ram7 from Hyderabad 28-Feb-13 0:36am

Thanks khorshed,thanks for all ur valuable suggestions........

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Chris Reynolds (UK) · Accepted Answer · 2013-02-26T02:52:00

Solution 1

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.

Posted 26-Feb-13 2:52am

Chris Reynolds (UK)

Comments

Ram7 from Hyderabad 26-Feb-13 8:59am

Dear Reynolds thanx for ur responce, i have already created a linked server,and iam able to retrieve data,insert,update,delete with the help of distributed querys.And hear iam unable to copy a table data from sqlserver to mysql using a single trigger,can u please help me in thid..