跨数据库存间的触发器

---zhouxin 2010-02-03

其中wxd 和yy 分别为测试数据库它们间有共同的表Table_A 结构为：id nchar(8) name nchar(8)
USE wxd
GO

CREATE TRIGGER trigger_selsyn

ON wxd

FOR INSERT,UPDATE,DELETE--触发器类型

AS IF NOT EXISTS (SELECT * FROM deleted)--INSERT触发器

BEGIN

INSERT yy..Table_A(id,[name]) SELECT id,[name] FROM inserted

END

ELSE IF NOT EXISTS (SELECT * FROM inserted)--DELETE触发器

BEGIN

DELETE yy..Table_A WHERE id IN (SELECT id FROM deleted)

END

ELSE--UPTATE触发器

BEGIN

UPDATE yy..Table_A SET [name]=inserted.[name] FROM inserted WHERE yy..Table_A.id=inserted.id

END
GO

SELECT * FROM wxd..Table_A
SELECT * FROM yy..Table_A

GO

INSERT INTO wxd..Table_A VALUES('0001','TestName01')--测试INSERT触发器

SELECT * FROM wxd..Table_A
SELECT * FROM yy..Table_A
GO

UPDATE wxd..Table_A set wxd..Table_A.[name]='UPDATENAME' WHERE wxd..Table_A.id='0001'--测试UPDATE触发器

SELECT * FROM wxd..Table_A
SELECT * FROM yy..Table_A
GO

DELETE wxd..Table_A where wxd..Table_A.id='0001'--测试DELETE触发器

SELECT * FROM wxd..Table_A
SELECT * FROM yy..Table_A
GO