MYSQL即時觸發SQL SERVER 執行個體,mysqlsql

來源:互聯網
上載者:User

MYSQL即時觸發SQL SERVER 執行個體,mysqlsql

  以下給出如何?MYSQL資料表更新即時觸發sql server裡面對應表的增刪改操作,首先,增加原來的mysql的表的觸發器,包括以下三個:

插入資料: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`,`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||更新觸發器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`,`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||刪除觸發器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||


上面必須增加behaviortype欄位,因為如果是I代表插入,U代表更新,D代表刪除,接著增加mysql的備份表,專門記錄這三種操作。


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;

這個表如無意外,ID不要設定成為唯一索引,因為本身就存在多條同一個記錄的更改。接下來在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  


最後建立sql server預存程序,這邊尤其要注意的是要rtrim mysql char類型,因為sql server對這個是完全填充的,如果有多餘空格插入會報錯,同時 要定義為 別名,因為在 下面的插入是根據別名欄位來的。


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 into sugarcrmtablebackup(id,name) select * from openquery(MySql, 'select id,name from sugarcrmtablebackup where NOT ISNULL(ExecutingState) ')                                 --插入BehaviorState為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 * from openquery(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 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 * 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,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()                 --刪除BehaviorState為U的記錄                 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 --變數定義                 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                 --更新BehaviorState為D的記錄                 if object_id ('tempdb..#temp1') is not 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 --變數定義                  set @count2 =(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_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_type=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_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 ,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 #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 * into #temp2 from #temp1 a where a. 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 ,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 .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 #temp2 t                                  --inner join  ( select id from account) a on a.id !=t.id                 if not exists (SELECT * FROM account  a,#temp1 b WHERE a .ID = b .ID) and not exists (SELECT * FROM account   a, #temp1 b WHERE a.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, 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 .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') 



最後再開啟sql server代理,因為本身這個需要計劃任務每秒去monitor這個mysql變化,因為sql server這邊只能設定10秒更新一次,那這邊就只能如此,如果要看是否成功,可以右擊該計劃任務查看記錄。



著作權聲明:本文為博主原創文章,未經博主允許不得轉載。

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.