《【2017年整理】两个数据库表数据实时同步(sql触发器)》由会员分享,可在线阅读,更多相关《【2017年整理】两个数据库表数据实时同步(sql触发器)(2页珍藏版)》请在金锄头文库上搜索。
1、/* Object: Trigger dbo.TR_C0T14 Script Date: 11/01/2011 13:21:04 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO- =- Author: - Create date: - Description: - =-DROP TRIGGER TR_C0T14CREATE TRIGGER dbo.TR_C0T14 ON dbo.C0T14 FOR INSERT,UPDATE,DELETE AS BEGIN IF NOT EXISTS(SELECT * FROM deleted) BEGIN /*
2、插入*/INSERT INTO leamon_test.C0T14 (RATINGID,BROKER,BROKERRATE,SFRATE,SFVALUE,F_OP1,F_OP2,F_OPC1,F_OPC2,F_9997,f_9998) SELECT RATINGID,BROKER,BROKERRATE,SFRATE,SFVALUE,F_OP1,F_OP2,F_OPC1,F_OPC2,F_9997,f_9998 FROM inserted WHERE NOT EXISTS (SELECT TOP 1 * FROM leamon_test.C0T14 WHERE inserted.RATINGID
3、 = leamon_test.C0T14.RATINGID) END /*删除*/ELSE IF NOT EXISTS(SELECT * FROM inserted) DELETE FROM leamon_test.C0T14WHERE RATINGID IN (SELECT RATINGID FROM deleted) -/*若两个主键关联删除表数据ELSE IF NOT EXISTS(SELECT * FROM inserted) DELETE FROM INPUT.C1T30 WHERE EXISTS (SELECT 1 FROM deleted B WHERE INPUT.C1T30.
4、F_CODE=B.F_CODE AND INPUT.C1T30.REPORT=B.REPORT AND INPUT.C1T30.F_YEAR=B.F_YEAR)*/-/*更新*/ELSEBEGIN UPDATE leamon_test.C0T14 SET RATINGID = inserted.RATINGID,BROKER = inserted.BROKER,BROKERRATE = inserted.BROKERRATE,SFRATE = inserted.SFRATE,SFVALUE = inserted.SFVALUE,F_OP1 = inserted.F_OP1,F_OP2 = in
5、serted.F_OP2,F_OPC1 = inserted.F_OPC1,F_OPC2 = inserted.F_OPC2,F_9997 = inserted.F_9997,f_9998 = inserted.f_9998 FROM inserted WHERE inserted.RATINGID = leamon_test.C0T14.RATINGID AND (inserted.BROKER leamon_test.C0T14.BROKERRATE OR inserted.SFRATE leamon_test.C0T14.SFVALUE OR inserted.F_OP1 leamon_test.C0T14.F_OP2 OR inserted.F_OPC1 leamon_test.C0T14.F_OPC2 OR inserted.F_9997 leamon_test.C0T14.f_9998 ) END END