MYSQL triggers SQL SERVER instances in real time.

MYSQL triggers SQL SERVER instances in real time.

The following describes how to update a mysql data table to trigger the addition, deletion, and modification operations on the corresponding table in SQL server. First, add the triggers for the original mysql table, including the following three:

INSERT data: delimiter | drop trigger if exists t_afterinsert_on_accounts | create trigger t_afterinsert_on_accountsAFTER insert 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 ', 'ployees', '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 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 ', 'ployees', '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 ', 'ployees', '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 represents insertion, U represents update, D represents deletion, and then mysql's backup table is added to record these three operations.

CREATE TABLE `accountsbackup` (  `id` char(36) NOT NULL,  `name` varchar(150) DEFAULT NULL,  `date_entered` datetime DEFAULT NULL,  `date_modified` datetime DEFAULT NULL,  `modified_user_id` char(36) DEFAULT NULL,  `created_by` char(36) DEFAULT NULL,  `description` text,  `deleted` tinyint(1) DEFAULT NULL,  `assigned_user_id` char(36) DEFAULT NULL,  `account_type` varchar(50) DEFAULT NULL,  `industry` varchar(50) DEFAULT NULL,  `annual_revenue` varchar(100) DEFAULT NULL,  `phone_fax` varchar(100) DEFAULT NULL,  `billing_address_street` varchar(150) DEFAULT NULL,  `billing_address_city` varchar(100) DEFAULT NULL,  `billing_address_state` varchar(100) DEFAULT NULL,  `billing_address_postalcode` varchar(20) DEFAULT NULL,  `billing_address_country` varchar(255) DEFAULT NULL,  `rating` varchar(100) DEFAULT NULL,  `phone_office` varchar(100) DEFAULT NULL,  `phone_alternate` varchar(100) DEFAULT NULL,  `website` varchar(255) DEFAULT NULL,  `ownership` varchar(100) DEFAULT NULL,  `employees` varchar(10) DEFAULT NULL,  `ticker_symbol` varchar(10) DEFAULT NULL,  `shipping_address_street` varchar(150) DEFAULT NULL,  `shipping_address_city` varchar(100) DEFAULT NULL,  `shipping_address_state` varchar(100) DEFAULT NULL,  `shipping_address_postalcode` varchar(20) DEFAULT NULL,  `shipping_address_country` varchar(255) DEFAULT NULL,  `parent_id` char(36) DEFAULT NULL,  `sic_code` varchar(10) DEFAULT NULL,  `campaign_id` char(36) DEFAULT NULL,  `BehaviorType` varchar(45) DEFAULT NULL,  `ExecutingState` varchar(45) DEFAULT NULL,  `ModificationTime` varchar(45) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

If this table is not unexpected, do not set the ID as a unique index, because there are multiple changes to the same record. Next, create the corresponding table on 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] (36) NOT NULL,                 [name] [varchar] (150) NULL,                 [date_entered] [datetime] NULL,                 [date_modified] [datetime] NULL,                 [modified_user_id] [char] (36) NULL,                 [created_by] [char] (36) NULL,                 [description] [text] NULL,                 [deleted] [smallint] NULL,                 [assigned_user_id] [char] (36) NULL,                 [account_type] [varchar] (50) NULL,                 [industry] [varchar] (50) NULL,                 [annual_revenue] [varchar] (100) NULL,                 [phone_fax] [varchar] (100) NULL,                 [billing_address_street] [varchar] (150) NULL,                 [billing_address_city] [varchar] (100) NULL,                 [billing_address_state] [varchar] (100) NULL,                 [billing_address_postalcode] [varchar] (20) NULL,                 [billing_address_country] [varchar] (255) NULL,                 [rating] [varchar] (100) NULL,                 [phone_office] [varchar] (100) NULL,                 [phone_alternate] [varchar] (100) NULL,                 [website] [varchar] (255) NULL,                 [ownership] [varchar] (100) NULL,                 [employees] [varchar] (10) NULL,                 [ticker_symbol] [varchar] (10) NULL,                 [shipping_address_street] [varchar] (150) NULL,                 [shipping_address_city] [varchar] (100) NULL,                 [shipping_address_state] [varchar] (100) NULL,                 [shipping_address_postalcode] [varchar] (20) NULL,                 [shipping_address_country] [varchar] (255) NULL,                 [parent_id] [char] (36) NULL,                 [sic_code] [varchar] (10) NULL,                 [campaign_id] [char] (36) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOSET ANSI_PADDING OFFGO  

At last, the SQL server storage process should be established. In particular, the rtrim mysql char type is required, because SQL server fully fills this field. If there are extra spaces to insert, an error will be reported, at the same time, it must be defined as an alias, because the following insert is based on the alias field.

USE [SugarCRMDB] GO/****** Object: StoredProcedure [dbo]. [trigger_account] Script Date: 14:38:38 *****/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo]. [trigger_account] as -- insert into sugarcrmtablebackup (id, name) select * from openquery (MySql, 'select id, name from sugarcrmtablebackup where not isnull (ExecutingState )') -- insert a record whose BehaviorState is I -- insert into account (id, n Ame, date_entered, date_modified, married, created_by, [description], deleted, assigned_user_id, account_type, industry, industry, metrics, phone_fax, metrics, rating, phone_office, phone_alternate, website, [ownership], employees, ticker_symbol, shipping_address_street, shipping_address_city, shipping_add Ress_state, province, province, parent_id, sic_code, campaign_id) -- select * from openquery (MySql, 'select id, name, date_entered, date_modified, modified_user_id, created_by, description, deleted, encrypted, 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, region, parent_id, sic_code, campaign_id from region. accountsbackup where ISNULL (ExecutingState) and BehaviorType = ''I ') insert into account (id, name, date_entered, date_modified, modified_user_ I D, created_by, [description], deleted, assigned_user_id, account_type, industry, login, phone_fax, billing_address_street, login, indexing, rating, phone_office, phone_alternate, website, [ownership], employees, ticker_symbol, shipping_address_street, shipping_address_city, shipping_address_state, shipping_address_po Stalcode, parser, parent_id, sic_code, campaign_id) select * from openquery (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, region, Region Y, rating, phone_office, phone_alternate, website, ownership, employees, ticker_symbol, role, role, metrics, metrics, rtrim (parent_id), sic_code, rtrim (campaign_id) from region. 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 the record if object_id ('tempdb .. # temp ') is not null Begin drop table # tempEnd select * into # temp from openquery (MySql, 'select rtrim (id), name from sugarcrm642cebackup. accountsbackup where ISNULL (ExecutingState) and BehaviorType = ''d') declare @ count 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 BehaviorType = ''d') set ExecutingState = 'D', ModificationTime = getdate () end -- Update if object_id ('tempdb .. # temp1') is not null Begin drop table # temp1 End 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, province, rat Ing, phone_office, phone_alternate, website, ownership, employees, numbers, metrics, rtrim (parent_id) parent_id, sic_code, rtrim (campaign_id) campaign_id from region. accountsbackup where ISNULL (ExecutingState) and BehaviorType = ''u'') declare @ count2 int -- variable definition set @ count2 = (s Elect count (*) from # temp1) if @ count2> 0 begin if EXISTS (SELECT * FROM account a, # temp1 B WHERE. ID = B. ID) -- declare @ a int update account set id = t. id, name = t. name, date_entered = t. date_entered, date_modified = t. date_modified, 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_ty Pe = t. account_type, industry = t. industry, annual_revenue = t. annual_revenue, phone_fax = t. phone_fax, billing_address_street = t. billing_address_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_offic E, 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, modified_user_id, created_by, [description], deleted, login, account_type, industry, login, phone_fax, login, billing_address_state, billing_address_postalcode, billing_address_country, rating, Phone_office, phone_alternate, website, [ownership], employees, ticker_symbol, delimiter, parent_id, sic_code, campaign_id from # temp1) t on t. id = account. id if EXISTS (SELECT * FROM account a, # temp1 B WHERE. ID! = B. ID) if object_id ('tempdb .. # temp2 ') is not null Begin drop table # temp2 end select * into # temp2 from # temp1 a where. id not in (select id from account) 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, success, fail, rating, phone_office, phone_alternate, website, [ownership], employees, ticker_symbol, role, 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. [de Success], 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. shippi Ng_address_state, t. Country, t. shipping_address_country, t. parent_id, t. sic_code, t. campaign_id from # temp2 t -- inner join (select id from account) a on a. id! = T. id if not exists (SELECT * FROM account a, # temp1 B WHERE. ID = B. ID) and not exists (SELECT * FROM account a, # temp1 B WHERE. ID! = B. ID) insert into account (id, name, date_entered, date_modified, modified_user_id, created_by, [description], deleted, assigned_user_id, account_type, industry, login, phone_fax, login, example, example, rating, phone_office, phone_alternate, website, [ownership], employees, ticker_symbol, role, 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. 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 (ExecutingState) and BehaviorType = ''u'') set ExecutingState = 'd, ModificationTime = getdate () End -- delete from SQL _tem -- delete openquery (MySql, 'select * FROM sugarcrmtablebackup ')

Finally, enable the SQL server proxy, because the scheduled task needs to monitor the mysql changes every second, because SQL server can only set the update once every 10 seconds, so this is the only way to do this, if you want to check whether the task is successful, right-click the scheduled task to view its history.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.