MySQL real-time triggering of SQL SERVER instances

The following shows how to implement MySQL data table update real-time trigger SQL Server inside the corresponding table to add and delete operations, first, increase the original MySQL table trigger, including the following three:

Inserting data: delimiter | | DROP TRIGGER IF EXISTS t_afterinsert_on_accounts | | CREATE TRIGGER t_afterinsert_on_accountsafter INSERT on sugarcrm642ce.accountsfor all rowbegininsert into Sugarcrm642cebackup.accountsbackup (' id ', ' name ', ' date_entered ', ' date_modified ', ' modified_user_id ', ' created_by ', ' Description ', ' deleted ', ' assigned_user_id ', ' account_type ', ' industry ', ' annual_revenue ', ' phone_fax ', ' billing_ Address_street ', ' billing_address_city ', ' billing_address_state ', ' billing_address_postalcode ', ' Billing_address_ Country ', ' rating ', ' phone_office ', ' phone_alternate ', ' website ', ' ownership ', ' employees ', ' ticker_symbol ', ' shipping ' _address_street ', ' shipping_address_city ', ' shipping_address_state ', ' shipping_address_postalcode ', ' shipping_ Address_country ', ' parent_id ', ' sic_code ', ' campaign_id ', ' Behaviortype ') VALUES (new.id,new.name,new.date_entered, NEW.DATE_MODIFIED,NEW.MODIFIED_USER_ID,NEW.CREATED_BY,NEW.DESCRIPTION,NEW.DELETED,NEW.ASSIGNED_USER_ID, New.account_type,new.industry,new.annual_revenue,new.phone_fax,new.bilLing_address_street,new.billing_address_city,new.billing_address_state,new.billing_address_postalcode, New.billing_address_country,new.rating,new.phone_office,new.phone_alternate,new.website,new.ownership, New.employees,new.ticker_symbol,new.shipping_address_street,new.billing_address_city,new.shipping_address_ STATE,NEW.SHIPPING_ADDRESS_POSTALCODE,NEW.SHIPPING_ADDRESS_COUNTRY,NEW.PARENT_ID,NEW.SIC_CODE,NEW.CAMPAIGN_ID, ' I '); end| | Update Trigger Delimiter | | DROP TRIGGER IF EXISTS t_afterupdate_on_accounts | | CREATE TRIGGER t_afterupdate_on_accountsafter Update on sugarcrm642ce.accountsfor all Rowbegininsert into Sugarcrm642cebackup.accountsbackup (' id ', ' name ', ' date_entered ', ' date_modified ', ' modified_user_id ', ' created_by ', ' Description ', ' deleted ', ' assigned_user_id ', ' account_type ', ' industry ', ' annual_revenue ', ' phone_fax ', ' billing_ Address_street ', ' billing_address_city ', ' billing_address_state ', ' billing_address_postalcode ', ' Billing_address_ Country ', ' rating ', ' phone_office ', ' phone_alternate ', ' website ', ' OwnershIP ', ' Employees ', ' ticker_symbol ', ' shipping_address_street ', ' shipping_address_city ', ' shipping_address_state ', ' Shipping_address_postalcode ', ' shipping_address_country ', ' parent_id ', ' sic_code ', ' campaign_id ', ' BehaviorType ') VALUES (New.id,new.name,new.date_entered,new.date_modified,new.modified_user_id,new.created_by,new.description, New.deleted,new.assigned_user_id,new.account_type,new.industry,new.annual_revenue,new.phone_fax,new.billing_ Address_street,new.billing_address_city,new.billing_address_state,new.billing_address_postalcode,new.billing_ Address_country,new.rating,new.phone_office,new.phone_alternate,new.website,new.ownership,new.employees, New.ticker_symbol,new.shipping_address_street,new.billing_address_city,new.shipping_address_state,new.shipping _address_postalcode,new.shipping_address_country,new.parent_id,new.sic_code,new.campaign_id, ' U '); end| | Delete Trigger Delimiter | | DROP TRIGGER IF EXISTS t_afterdelete_on_accounts | | CREATE TRIGGER t_afterdelete_on_accountsafter Delete on Sugarcrm642ce.acCountsfor each rowbegininsert into sugarcrm642cebackup.accountsbackup (' id ', ' name ', ' date_entered ', ' date_modified ', ' modified_user_id ', ' created_by ', ' description ', ' deleted ', ' assigned_user_id ', ' account_type ', ' industry ', ' Annual_ Revenue ', ' phone_fax ', ' billing_address_street ', ' billing_address_city ', ' billing_address_state ', ' Billing_address_ PostalCode ', ' billing_address_country ', ' rating ', ' phone_office ', ' phone_alternate ', ' website ', ' ownership ', ' Employees ', ' ticker_symbol ', ' shipping_address_street ', ' shipping_address_city ', ' shipping_address_state ', ' Shipping_address_postalcode ', ' shipping_address_country ', ' parent_id ', ' sic_code ', ' campaign_id ', ' BehaviorType ') VALUES (Old.id,old.name,old.date_entered,old.date_modified,old.modified_user_id,old.created_by,old.description, Old.deleted,old.assigned_user_id,old.account_type,old.industry,old.annual_revenue,old.phone_fax,old.billing_ Address_street,old.billing_address_city,old.billing_address_state,old.billing_address_postalcode,old.billing_ Address_country,old.rating, Old.phone_office,old.phone_alternate,old.website,old.ownership,old.employees,old.ticker_symbol,old.shipping_ Address_street,old.billing_address_city,old.shipping_address_state,old.shipping_address_postalcode, old.shipping_address_country,old.parent_id,old.sic_code,old.campaign_id, ' D '); end| |

The Behaviortype field must be added above, because if I is an INSERT, U represents an update, D is deleted, and then the backup table of MySQL is added, specifically documenting these three operations.

CREATE TABLE ' accountsbackup ' (' id ' char (*) not NULL, ' name ' varchar () ' DEFAULT ' null ', ' date_entered ' datetime defau  LT null, ' date_modified ' datetime default NULL, ' modified_user_id ' char ($) default null, ' Created_by ' char ($) Default  NULL, ' description ' text, ' deleted ' tinyint (1) default null, ' assigned_user_id ' char (*) default NULL, ' Account_type '  varchar (+) default NULL, ' industry ' varchar (+) default NULL, ' annual_revenue ' varchar (+) default NULL, ' Phone_fax ' varchar (+) default NULL, ' billing_address_street ' varchar default NULL, ' billing_address_city ' varchar (DEFA) ULT null, ' billing_address_state ' varchar (+) default NULL, ' billing_address_postalcode ' varchar (default null), ' BI Lling_address_country ' varchar (255) default NULL, ' rating ' varchar (+) default NULL, ' Phone_office ' varchar (+) Defaul T null, ' phone_alternate ' varchar (+) default NULL, ' website ' varchar (255) default NULL, ' ownership ' varchar (+) DEFA ULT NULL, ' employees ' VArchar (Ten) default null, ' Ticker_symbol ' varchar (TEN) default null, ' Shipping_address_street ' varchar (. ' shipping_address_city ' varchar default NULL, ' shipping_address_state ' varchar (+) default NULL, ' Shipping_addre Ss_postalcode ' varchar ' default null, ' shipping_address_country ' varchar (255) default NULL, ' parent_id ' char (DEFA) ULT null, ' sic_code ' varchar (TEN) default null, ' campaign_id ' char ($) default null, ' behaviortype ' varchar ' default ' NULL, ' executingstate ' varchar default NULL, ' modificationtime ' varchar default NULL, PRIMARY KEY (' id ')) engin E=innodb DEFAULT Charset=utf8;

If the table is not unexpected, the ID is not set to be a unique index because there are multiple changes to the same record. Next, create the corresponding table in SQL Server.

Use [sugarcrmdb]go/****** object:table [dbo].    [Account] Script date:2015/6/24 13:49:20 ******/set ansi_nulls ongoset quoted_identifier ongoset ansi_padding ONGOCREATE TABLE [dbo ]. [Account] ([id] [char] (*) not NULL, [name] [varchar] () NULL, [date_entered] [                 DATETIME] NULL, [date_modified] [datetime] NULL, [modified_user_id] [char] (*) NULL, [Created_by] [Char] NULL, [description] [text] null, [deleted] [smallint] NULL, [Assigned _USER_ID] [char] ($) NULL, [Account_type] [varchar] () NULL, [industry] [varchar] (50)                 NULL, [annual_revenue] [varchar] (+) NULL, [Phone_fax] [varchar] (+) NULL, [Billing_address_street] [varchar]  NULL, [billing_address_city] [varchar] (+) NULL, [billing_address_state] [varchar] () null, [Billing_address_postalcode] [varchar] (+) NULL, [billing_address_country] [VA                 Rchar] (255) NULL, [rating] [varchar] (+) NULL, [Phone_office] [varchar] (+) NULL, [Phone_alternate] [varchar]                 NULL, [website] [varchar] (255) NULL, [ownership] [varchar] (+) NULL, [Employees] [varchar] () null, [Ticker_symbol] [varchar] (TEN) null, [Shipping_address_street] [varchar] (150) NULL, [shipping_address_city] [varchar] (+) NULL, [shipping_address_state] [varchar] (10 0) NULL, [Shipping_address_postalcode] [varchar] (+) NULL, [shipping_address_country] [VA                 Rchar] (255) NULL, [parent_id] [char] ($) NULL, [Sic_code] [varchar] (TEN) NULL, [CAMPAIGN_ID] [Char] (+) NULL) on [PRIMARY] textimage_on [primary]goset Ansi_padding Offgo   

Finally, the SQL Server stored procedure is built, this is especially important to note is to rtrim the MySQL char type, because SQL Server is fully populated with this, if there are extra spaces inserted will error, but also to be defined as aliases, because the following insert is based on the alias field.

Use [sugarcrmdb]go/****** object:storedprocedure [dbo].    [Trigger_account] Script date:2015/6/24 14:38:38 ******/set ansi_nulls ongoset quoted_identifier ongoalter procedure [dbo]. [Trigger_account]as--insert to Sugarcrmtablebackup (id,name) SELECT * FROM OPENQUERY (MySql, ' Select Id,name from Suga                 Rcrmtablebackup where not ISNULL (executingstate) ')--Inserts a record of behaviorstate for I --insert into account (id,name,date_entered,date_modified,modified_user_id,created_by,[description],deleted, Assigned_user_id,account_type,industry,annual_revenue,phone_fax,billing_address_street,billing_address_city, Billing_address_state,billing_address_postalcode,billing_address_country,rating,phone_office,phone_alternate, Website,[ownership],employees,ticker_symbol,shipping_address_street,shipping_address_city,shipping_address_                                 STATE,SHIPPING_ADDRESS_POSTALCODE,SHIPPING_ADDRESS_COUNTRY,PARENT_ID,SIC_CODE,CAMPAIGN_ID) --select * FROMOPENQUERY (MYSQL, ' select Id,name,date_entered,date_modified,modified_user_id,created_by,description,deleted, Assigned_user_id,account_type,industry,annual_revenue,phone_fax,billing_address_street,billing_address_city, Billing_address_state,billing_address_postalcode,billing_address_country,rating,phone_office,phone_alternate, Website,ownership,employees,ticker_symbol,shipping_address_street,shipping_address_city,shipping_address_state , shipping_address_postalcode,shipping_address_country,parent_id,sic_code,campaign_id from Sugarcrm642cebackup.accountsbackup where ISNULL (executingstate) and behaviortype= "I") insert INTO ACC Ount (ID, name, date_entered,date_modified, modified_user_id, created_by,[description], deleted, assigned_user_id, Account_type, industry, Annual_revenue,phone_fax, Billing_address_street, Billing_address_city,billing_address_ State, Billing_address_postalcode, Billing_address_country,rating, Phone_office, Phone_alternate,website, [ Ownership], Employees, Ticker_symbol, Shipping_address_street, Shipping_address_city,shipping_address_state, Shipping_address_ PostalCode, shipping_address_country,parent_id, Sic_code, campaign_id) SELECT * FROM Openq Uery (MYSQL, ' select RTrim (ID), Name,date_entered,date_modified,rtrim (modified_user_id), RTrim (created_by), Description,deleted,rtrim (assigned_user_id), Account_type,industry,annual_revenue,phone_fax,billing_address_ Street,billing_address_city,billing_address_state,billing_address_postalcode,billing_address_country,rating, Phone_office,phone_alternate,website,ownership,employees,ticker_symbol,shipping_address_street,shipping_ Address_city,shipping_address_state,shipping_address_postalcode,shipping_address_country,rtrim (parent_id), sic_ Code,rtrim (campaign_id) from Sugarcrm642cebackup.accountsbackup where ISNULL (executingstate) and behaviortype= ' I ' Update OpenQuery (MYSQL, ' select * ' from Sugarcrm642cebackup.accountsbackup where ISNULL (executingstate) and behaviortype= "I") set executingstate= ' D ', Modificationtime= getdate ()--delete record of behaviorstate to u If object_id (' tempdb.. #temp ') is not a null Begin drop table #tempEnd Select * Into #temp from OPENQUERY (MySql, ' select RTrim (ID), name fr Om sugarcrm642cebackup.accountsbackup where ISNULL (executingstate) and behaviortype= ' D ') declare @coun                 t INT--variable definition set @count = (select count (*) from #temp);                 If @count >0 begin delete from account where ID in (select RTRIM (ID) from #temp) Update OpenQuery (MYSQL, ' select * ' from Sugarcrm642cebackup.accountsbackup where ISNULL (executingstate) and is Haviortype= ' d ') set executingstate= ' d ', modificationtime= getdate () End--Update Behaviorsta Te is a record of D if object_id (' tempdb. #temp1 ') is not a null Begin drop table #temp1End Select * Into #temp1 from OPENQUERY (MySQL, ' select RTrim (ID) ID, name,date_entered,date_modified,rtrim (modified_user_id) Modified_user_id,rtrim (created_by ) Created_by,description,deleted,rtrim (assigned_user_id) assigned_user_id,account_type,industry,annual_revenue, Phone_fax,billing_address_street,billing_address_city,billing_address_state,billing_address_postalcode,billing _address_country,rating,phone_office,phone_alternate,website,ownership,employees,ticker_symbol,shipping_ Address_street,shipping_address_city,shipping_address_state,shipping_address_postalcode,shipping_address_ Country,rtrim (parent_id) Parent_id,sic_code,rtrim (campaign_id) campaign_id from Sugarcrm642cebackup.accountsbackup where ISNULL (executingstate) and behaviortype= "U") declare @count2 INT--Variable definition set @co                                 Unt2 = (select count (*) from #temp1) if @count2 >0 begin If EXISTS (SELECT * from Account A, #temp1 b WHERE a.id = b.id)--declare@a int Update account set id=t. ID, name=t. Name, date_entered=t. date_entered, date_modified=t. date_m Odified, Modified_user_id=t modified_user_id, created_by=t. created_by, [Description]=t. [description], deleted=t deleted, assigned_user_id=t. assigned_user_id, account_type=t. Account_type, Industry=t. Industry, Annual_revenue=t Annual_revenue, phone_fax=t. Phone_fax, billing_address_street=t. billing_addr                 Ess_street, Billing_address_city=t billing_address_city, billing_address_state=t. Billing_address_state, Billing_address_postalcode =t. Billing_address_postalcode,billing_address_country =t. Billing_address_country, Rating =t. Rating,phone_office =t. Phone_office, phone_alternate=t. Phone_alternate, website=t. website, [ Ownership]=t. [ownership], employees=t employees, ticker_symbol=t. Ticker_symbol, shipping_address_street=t. Shipping_address_ Street, Shipping_address_city =t. shipPing_address_city,shipping_address_state =t. Shipping_address_state,shipping_address_postalcode =t. Shipping_  Address_postalcode, Shipping_address_country =t. shipping_address_country,parent_id =t. Parent_id,sic_code =t. sic_code,campaign_id =t. campaign_id from account INNER JOIN (select ID, name, date_entered,date_modified, Modifi ED_USER_ID, Created_by,[description], deleted, Assigned_user_id,account_type, industry, Annual_revenue,phone_fax, Billing_address_street, Billing_address_city,billing_address_state, Billing_address_postalcode, Billing_address_ Country,rating, Phone_office, Phone_alternate,website, [ownership], Employees,ticker_symbol, Shipping_address_ Street, Shipping_address_city,shipping_address_state, Shipping_address_postalcode, Shipping_address_country, parent_id, Sic_code, campaign_id from #temp1) T-on T. id=account. ID if EXISTS (SELECT * From account A, #temp1 b WHERE a.id! = b.id) if object_id ('Tempdb.. #temp2 ') is not null Begin drop table #temp2 End Select * to #temp2 from #temp1 a wh ere a. ID not in (SELECT ID from account) INSERT into account (ID, name, date_entered,date_modified, MoD IFIED_USER_ID, Created_by,[description], deleted, Assigned_user_id,account_type, industry, Annual_revenue,phone_fax , Billing_address_street, Billing_address_city,billing_address_state, Billing_address_postalcode, Billing_address_ Country,rating, Phone_office, Phone_alternate,website, [ownership], Employees,ticker_symbol, Shipping_address_ Street, Shipping_address_city,shipping_address_state, Shipping_address_postalcode, Shipping_address_country,  parent_id, Sic_code, campaign_id) select T. ID, T. name,t. date_entered, t.date_modified , T. modified_user_id,t created_by, T.[description], T. deleted,t. assigned_user_id, T.account_type, T. industry,t. Annu Al_revenue, T.phone_fax, T. billing_address_street,t Bill.Ing_address_city, T.billing_address_state, T. billing_address_postalcode,t billing_address_country, t.rating, phone _office, T.phone_alternate, T. website,t. [Ownership], T.employees, T. ticker_symbol,t Shipping_address_street, t.shipping_address_city, T. shipping_address_                                  State,t. Shipping_address_postalcode, T.shipping_address_country, T. parent_id,t Sic_code, t.campaign_id from #temp2 t --inner join (SELECT ID from account) A to a.id!=t.id if not exists (SE Lect * From Account A, #temp1 b where a. id = B. ID) and NOT EXISTS (SELECT * from Account A, #temp1 b where a.id! = B.I D) insert into account (ID, name, date_entered,date_modified, modified_user_id, created_by,[description ], deleted, Assigned_user_id,account_type, industry, Annual_revenue,phone_fax, Billing_address_street, Billing_ Address_city,billing_address_state, Billing_address_postalcode, billing_address_country,rating, Phone_office, Phone_alternate,weBsite, [ownership], Employees,ticker_symbol, Shipping_address_street, shipping_address_city,shipping_address_state , Shipping_address_postalcode, shipping_address_country,parent_id, Sic_code, campaign_id) s Elect T. ID, T. name,t. date_entered, T.date_modified, T. modified_user_id,t. Created_by, T.[description], T. deleted,t . assigned_user_id, T.account_type, T. industry,t. Annual_revenue, T.phone_fax, T. billing_address_street,t. Billing_ Address_city, T.billing_address_state, T. billing_address_postalcode,t billing_address_country, t.rating, Phone_ Office, T.phone_alternate, T. website,t. [Ownership], T.employees, T. ticker_symbol,t Shipping_address_street, t.shipping_address_city, T. shipping_address_                                  State,t. Shipping_address_postalcode, T.shipping_address_country, T. parent_id,t Sic_code, t.campaign_id from #temp1 t Update OpenQuery (MYSQL, ' select * ' from Sugarcrm642cebackup.accountsbackup where ISNULL (E Xecutingstate) and behaviortype= ' U ') set executingstate= ' D ', Modificationtime= getdate () End--delete from Sql_  TEM--delete OPENQUERY (MYSQL, ' SELECT * from Sugarcrmtablebackup ')

Finally turn on SQL Server Agent, because itself this need to schedule tasks per second to monitor this MySQL change, because SQL Server can only set 10 seconds to update once, that this is the only way, if you want to see whether it is successful, you can right-click the scheduled task to view the history.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL real-time triggering of SQL SERVER instances