将新增记录插入A表后,,再执行以下SQL语句对B表数据进行插入更新。 insert into B表 select * from A表 where 主键ID号 not in B表.主键ID号
在a表上写一个insert触发器吧。
问:我需要创建一个触发器以保持两个独立数据库表的一致性。在更改被应用之前,是否有方法可以让触发器验证其它数据库/表中不存在的更新呢?如果更改已经存在,我不想遭遇触发器无限循环的问题。例如: 新的纪录被添加到表A。触发器可以在插入命令执行前检验表B是否已经有记录了。如果表B被更改,触发器可以再更新命令执行前检验表A的更改是否已经存在了。 我使用的是SQL Server 2005来创建触发器。我没有访问任何的管理员工具或数据服务。 答:是的,当然可以做到这一点。这只需要一些额外的触发器逻辑位。你需要对列名做出相应的修改以对你的表进行匹配。如果你不需要嵌套的触发器,可以通过sp_configure系统存储过程关闭嵌套触发器。CREATE TRIGGER Trigger1 ON Table1 FOR INSERT, UPDATE AS BEGIN IF NOT EXISTS (SELECT * FROM deleted) BEGIN INSERT INTO Table2 (YourColumns...) SELECT YourColumns FROM inserted WHERE NOT EXISTS (SELECT * FROM Table2 WHERE inserted.PrimaryKey = Table2.PrimaryKey) END ELSE BEGIN UPDATE Table2 SET YourColumn1 = inserted.YourColumn1,... FROM inserted WHERE inserted.PrimaryKey = Table2.PrimaryKey AND (inserted.YourColumn1 <> Table2.YourColumn1 OR inserted.YourColumn2 <> Table2.YourColumn2 OR ...) END END GO CREATE TRIGGER Trigger2 ON Table2 FOR INSERT, UPDATE AS BEGIN IF NOT EXISTS (SELECT * FROM deleted) BEGIN INSERT INTO Table1 (YourColumns...) SELECT YourColumns FROM inserted WHERE NOT EXISTS (SELECT * FROM Table1 WHERE inserted.PrimaryKey = Table1.PrimaryKey) END ELSE BEGIN UPDATE Table1 SET YourColumn1 = inserted.YourColumn1,... FROM inserted WHERE inserted.PrimaryKey = Table1.PrimaryKey AND (inserted.YourColumn1 <> Table1.YourColumn1 OR inserted.YourColumn2 <> Table1.YourColumn2 OR ...) END END GO文章地址: http://www.searchdatabase.com.cn/showcontent_24328.htm
将新增记录插入A表后,,再执行以下SQL语句对B表数据进行插入更新。
insert into B表 select * from A表 where 主键ID号 not in B表.主键ID号
FOR
INSERT, UPDATE
AS
BEGIN
IF NOT EXISTS (SELECT * FROM deleted)
BEGIN
INSERT INTO Table2
(YourColumns...)
SELECT YourColumns
FROM inserted
WHERE NOT EXISTS (SELECT * FROM Table2 WHERE inserted.PrimaryKey = Table2.PrimaryKey)
END
ELSE
BEGIN
UPDATE Table2
SET YourColumn1 = inserted.YourColumn1,...
FROM inserted
WHERE inserted.PrimaryKey = Table2.PrimaryKey
AND (inserted.YourColumn1 <> Table2.YourColumn1
OR inserted.YourColumn2 <> Table2.YourColumn2
OR ...)
END
END
GO
CREATE TRIGGER Trigger2 ON Table2
FOR
INSERT, UPDATE
AS
BEGIN
IF NOT EXISTS (SELECT * FROM deleted)
BEGIN
INSERT INTO Table1
(YourColumns...)
SELECT YourColumns
FROM inserted
WHERE NOT EXISTS (SELECT * FROM Table1 WHERE inserted.PrimaryKey = Table1.PrimaryKey)
END
ELSE
BEGIN
UPDATE Table1
SET YourColumn1 = inserted.YourColumn1,...
FROM inserted
WHERE inserted.PrimaryKey = Table1.PrimaryKey
AND (inserted.YourColumn1 <> Table1.YourColumn1
OR inserted.YourColumn2 <> Table1.YourColumn2
OR ...)
END
END
GO文章地址: http://www.searchdatabase.com.cn/showcontent_24328.htm