两个数据库表数据实时同步（sql2008 触发器）代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】两个数据库表数据实时同步（sql2008 触发器）

作者:Dezai.CN / 发布于2011/11/22/ 616

<div>/****** Object: Trigger [dbo].[TR_C0T14] Script Date: 11/01/2011 13:21:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:<span style="white-space: pre"> </span><LEAMON>
-- Create date: <2011-11-01>
-- Description:<span style="white-space: pre"> </span><两个数据库表数据实时同步>
-- =============================================


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


</div>

试试其它关键字

　数据实时同步　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)