mysql triggers SQL Server to implement synchronization instance in real time

We update the MySQL datasheet real-time trigger SQL Server in the corresponding table of additions and deletions, first of all, add the original MySQL table triggers, including the following three:





Insert data:





Delimiter | |


DROP TRIGGER IF EXISTS t_afterinsert_on_accounts | |


CREATE TRIGGER t_afterinsert_on_accounts


After INSERT on sugarcrm642ce.accounts


For each ROW


BEGIN


INSERT 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 triggers





Delimiter | |


DROP TRIGGER IF EXISTS t_afterupdate_on_accounts | |


CREATE TRIGGER t_afterupdate_on_accounts


After update on sugarcrm642ce.accounts


For each ROW


BEGIN


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





deleting triggers





Delimiter | |


DROP TRIGGER IF EXISTS t_afterdelete_on_accounts | |


CREATE TRIGGER t_afterdelete_on_accounts


After delete on sugarcrm642ce.accounts


For each ROW


BEGIN


INSERT 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 above must be added to the Behaviortype field, because if I represents the Insert, U represents the update, d represents the deletion, then adds the MySQL backup table, specifically recording these three kinds of operations.





CREATE TABLE ' AccountsBackup ' (


' id ' char (+) not NULL,


' Name ' varchar ($) DEFAULT NULL,


' date_entered ' datetime DEFAULT 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 DEFAULT NULL,


' billing_address_state ' varchar DEFAULT NULL,


' billing_address_postalcode ' varchar DEFAULT NULL,


' Billing_address_country ' varchar (255) DEFAULT NULL,


' Rating ' varchar DEFAULT NULL,


' phone_office ' varchar DEFAULT NULL,


' phone_alternate ' varchar DEFAULT NULL,


' website ' varchar (255) DEFAULT NULL,


' Ownership ' varchar DEFAULT NULL,


' Employees ' varchar DEFAULT NULL,


' ticker_symbol ' varchar DEFAULT NULL,


' shipping_address_street ' varchar DEFAULT NULL,


' shipping_address_city ' varchar DEFAULT NULL,


' shipping_address_state ' varchar DEFAULT NULL,


' shipping_address_postalcode ' varchar DEFAULT NULL,


' Shipping_address_country ' varchar (255) DEFAULT NULL,


' parent_id ' char (+) DEFAULT NULL,


' sic_code ' varchar DEFAULT NULL,


' campaign_id ' char (+) DEFAULT NULL,


' behaviortype ' varchar DEFAULT NULL,


' executingstate ' varchar DEFAULT NULL,


' modificationtime ' varchar DEFAULT NULL,


PRIMARY KEY (' id ')


) Engine=innodb DEFAULT Charset=utf8;








If this 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 on


Go





SET QUOTED_IDENTIFIER ON


Go





SET ansi_padding on


Go





CREATE TABLE [dbo]. [Account] (


[ID] [char] (+) not NULL,


[Name] [varchar] (i) 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] (m) NULL,


[Industry] [varchar] (m) NULL,


[Annual_revenue] [varchar] (MB) NULL,


[Phone_fax] [varchar] (MB) NULL,


[Billing_address_street] [varchar] (i) NULL,


[Billing_address_city] [varchar] (MB) NULL,


[Billing_address_state] [varchar] (MB) NULL,


[Billing_address_postalcode] [varchar] (a) NULL,


[Billing_address_country] [varchar] (255) NULL,


[Rating] [varchar] (MB) NULL,


[Phone_office] [varchar] (MB) NULL,


[Phone_alternate] [varchar] (MB) NULL,


[Website] [varchar] (255) NULL,


[Ownership] [varchar] (MB) NULL,


[Employees] [varchar] (a) NULL,


[Ticker_symbol] [varchar] (a) NULL,


[Shipping_address_street] [varchar] (i) NULL,


[Shipping_address_city] [varchar] (MB) NULL,


[Shipping_address_state] [varchar] (MB) NULL,


[Shipping_address_postalcode] [varchar] (a) NULL,


[Shipping_address_country] [varchar] (255) NULL,


[PARENT_ID] [Char] () NULL,


[Sic_code] [varchar] (a) NULL,


[CAMPAIGN_ID] [Char] (+) NULL


) on [PRIMARY] textimage_on [PRIMARY]





Go





SET ansi_padding off


Go





Finally, the SQL Server stored procedure is established, especially to rtrim the MySQL char type, because SQL Server is fully populated for this, and if an extra space insertion is an error, it is defined as an alias, because the insertion below is based on a different name segment.





Use [Sugarcrmdb]


Go


/****** object:storedprocedure [dbo].    [Trigger_account] Script date:2015/6/24 14:38:38 ******/


SET ANSI_NULLS on


Go


SET QUOTED_IDENTIFIER ON


Go


ALTER procedure [dbo]. [Trigger_account]


As


--insert into Sugarcrmtablebackup (id,name) SELECT * to OpenQuery (MYSQL, ' select Id,name from Sugarcrmtablebackup where N OT ISNULL (executingstate) ')





--Insert a record of behaviorstate as 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 ()








--Delete behaviorstate as U's record


If object_id (' tempdb.. #temp ') is not null Begin


drop table #temp


End





SELECT * Into #temp to OPENQUERY (MYSQL, ' select RTrim (ID), name from Sugarcrm642cebackup.accountsbackup where ISNULL (Ex ecutingstate) 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 (IDS) from #temp)





Update OpenQuery (MYSQL, ' select * from Sugarcrm642cebackup.accountsbackup where ISNULL (executingstate) and Behaviortype = ' d ') set executingstate= ' d ', modificationtime= getdate ()





End


--Update the record for Behaviorstate D


If object_id (' tempdb.. #temp1 ') is not null Begin


drop table #temp1


End





SELECT * Into #temp1 the 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 @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,[DESCRI Ption], 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 to #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 * To account A, #temp1 b WHERE an. 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 ')








Finally, open the SQL Server Agent, because it needs to schedule the task to monitor this MySQL change per second, because SQL Server can only set 10 seconds to update once, then this is the only way, if you want to see whether the success, you can right-click the scheduled task to view the history.











SQL Server data synchronizes to MySQL in real time





1. Installation and Installation Mysqlconnector





2. Configure Mysqlconnector





ODBC Data Manager-> System dsn-> add->mysql ODBC 5.3 ANSI driver-> fill in the IP, username, password of the data source name such as Jt,mysql





3. New Linked server





EXEC sp_addlinkedserver


@server = ' JT ',--ODBC inside data source Name


@srvproduct = ' MySQL ',--Be free


@provider = ' Msdasql ',--fixed this


@datasrc =null,


@location =null,


@provstr = ' Driver={mysql ODBC 5.3 ANSI DRIVER}; Server=192.168.5.188;database=suzhou; Uid=root; port=3306; ',


@catalog = NULL





EXEC sp_addlinkedsrvlogin


@rmtsrvname = ' JT ',


@useself = ' false ',


@rmtuser = ' root ',


@rmtpassword = ' password ';





SELECT * FROM OPENQUERY (JT, ' select * from SZ; ')


Go





Use [master]


Go


EXEC master.dbo.sp_serveroption @server =n ' JT ', @optname =n ' rpc out ', @optvalue =n ' TRUE '


Go


EXEC master.dbo.sp_serveroption @server =n ' JT ', @optname =n ' remote proc transaction promotion ', @optvalue =n ' false '


Go





---4.sqlserver and MySQL new libraries and tables





Create DATABASE Suzhou;





CREATE TABLE SZ (


ID int NOT NULL identity (1,1) primary key,


OrderNo char (not NULL),


Ordertime datetime NOT NULL default GETDATE (),


Remark varchar (200)


)


Go





CREATE TABLE SZ (


ID Int (one) not NULL,


OrderNo char (not NULL),


Ordertime datetime (6) NOT NULL,


Remark varchar (200),


Primary KEY (ID)


) Engine=innodb default Charset=utf8;





---5. Establish a loop


--Establish a loopback server link





EXEC sp_addlinkedserver @server = n ' loopback ', @srvproduct = N ', @provider = N ' sqlncli ',


@datasrc = @ @SERVERNAME


Go





--Set server link options to prevent SQL Server from promoting local transactions to distributed transactions due to remote procedure calls (emphasis)


Use [master]


Go


EXEC master.dbo.sp_serveroption @server =n ' loopback ', @optname =n ' rpc out ', @optvalue =n ' TRUE '


Go


EXEC master.dbo.sp_serveroption @server =n ' loopback ', @optname =n ' remote proc transaction ' promotion ', @optvalue =n ' false '


Go





----6. Writing triggers and stored procedures





----6.1 Insert





--Overriding triggers


Use Suzhou


Go


Alter TRIGGER TR_INSERT_SZ on SUZHOU.DBO.SZ


For insert


As


declare @id int, @orderno char, @ordertime datetime, @remark varchar (200)


Select @id =id, @orderno =orderno, @ordertime =ordertime, @remark =remark from inserted;


Begin


Print @id


Print @orderno


Print @ordertime


Print @remark


exec Loopback.suzhou.dbo.sp_insert @id, @orderno, @ordertime, @remark


End


Go





--Stored procedures


Use Suzhou


Go


Create PROCEDURE Sp_insert (


@id int,


@orderno Char (20),


@ordertime datetime,


@remark varchar (200)


)


As


BEGIN


SET NOCOUNT on;


Insert OPENQUERY (JT, ' select * from sz ') (Id,orderno,ordertime,remark) VALUES (@id, @orderno, @ordertime, @remark)


End


Go





----6.2 Update





--Overriding triggers


Use Suzhou


Go


Create Trigger TR_UPDATE_SZ on SUZHOU.DBO.SZ


For update


As


Declare @orderno char (m), @remark varchar (200)


Select @orderno =orderno, @remark =remark from inserted;


Begin


exec loopback.suzhou.dbo.sp_update @orderno, @remark


End


Go





--Stored procedures


Use Suzhou


Go


Create PROCEDURE Sp_update (


@orderno Char (20),


@remark varchar (200)


)


As


BEGIN


SET NOCOUNT on;


Update OPENQUERY (JT, ' select * from sz ') set remark= @remark where orderno= @orderno


End


Go





---update data test





Use Suzhou


Go


Update sz set remark= ' Ocpyang ' where orderno= ' a001 '


Go





----6.3 Delete





--Overriding triggers


Use Suzhou


Go


Create Trigger TR_DELETE_SZ on SUZHOU.DBO.SZ


For delete


As


Declare @orderno char (20)


Select @orderno =orderno from deleted;


Begin


EXEC loopback.suzhou.dbo.sp_delete @orderno


End


Go





--Stored procedures


Use Suzhou


Go


Create PROCEDURE Sp_delete (


@orderno Char (20)


)


As


BEGIN


SET NOCOUNT on;


Delete OpenQuery (JT, ' select * from sz ') where orderno= @orderno


End


Go





---delete data test





Use Suzhou


Go


Delete from sz where orderno= ' a001 '


Go