两个数据库表数据实时同步(sql2008 触发器)
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
/****** Object: Trigger [dbo].[TR_C0T14] Script Date: 11/01/2011 13:21:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: <2011-11-01>
-- Description: <两个数据库表数据实时同步>
-- =============================================
--DROP TRIGGER TR_C0T14
CREATE TRIGGER [dbo].[TR_C0T14] ON [dbo].[C0T14]
FOR INSERT,UPDATE,DELETE
AS
BEGIN
IF NOT EXISTS(SELECT * FROM deleted)
BEGIN
/*插入*/
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 = leamon_test..C0T14.RATINGID)
END
/*删除*/
ELSE IF NOT EXISTS(SELECT * FROM inserted)
DELETE FROM leamon_test..C0T14
WHERE 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.F_CODE=B.F_CODE AND
[INPUT]..C1T30.REPORT=B.REPORT AND [INPUT]..C1T30.F_YEAR=B.F_YEAR) */
-------
/*更新*/
ELSE
BEGIN
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 = inserted.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.[BROKER]
OR inserted.BROKERRATE <> leamon_test..C0T14.BROKERRATE
OR inserted.SFRATE <> leamon_test..C0T14.SFRATE
OR inserted.SFVALUE <> leamon_test..C0T14.SFVALUE
OR inserted.F_OP1 <> leamon_test..C0T14.F_OP1
OR inserted.F_OP2 <> leamon_test..C0T14.F_OP2
OR inserted.F_OPC1 <> leamon_test..C0T14.F_OPC1
OR inserted.F_OPC2 <> leamon_test..C0T14.F_OPC2
OR inserted.F_9997 <> leamon_test..C0T14.F_9997
OR inserted.f_9998 <> leamon_test..C0T14.f_9998
)
END
END