MS SQL Server 中如何实现一对多外键的级联更新

最新推荐文章于 2024-09-15 16:28:55 发布

转载最新推荐文章于 2024-09-15 16:28:55 发布 · 2.5k 阅读

标签

#sql server #delete #insert #go #测试

解决MS SQL Server中一个表拥有多个指向另一表主键的外键时，如何实现主键更新自动同步的问题。通过创建触发器，实现级联更新及删除，并确保数据一致性。

我最近在做项目时发现，我有一个表A、表B，在表B中三处引用了A的主键。我希望在表A主键更新时能够自动更新。但是MS SQL Server不用在一个表的外键约束只能使用一次级联操作（ON UPDATE CASCADE），这样就导致不能修改表A的主键，修改就报错（因为外键约束）。看了MS SQL SERVER的说明，可以用INSTEAD OF UPDATE触发器，但是找了半天资料，都是基于视图的，仿照写了一个，整个表A的数据都不能修改了，也不报错。谁能告诉我该怎么办？

----------------------------------------------------------------------

B表的三个字段只能有一个可以由系统自动级联更新或级联删除，其他两个字段可以建外键，但不能级联更新或删除，只能自己写触发器来处理。

--------------------------------------------------------

这种情况下,必须取消B表的另外二个非级联外键的'对INSERT和UPDATE强制关系',否则A表主键和B表的非级联外键都无法修改.
之后,为A表创建UPDATE触发器,修改主键时更新B表非级联外键的值,并且还要创建DELETE触发器,当主键被删除时,将B表的非级联更新外键值设为NULL,否则无法删除主键.
建议楼主为A表添加IDENTITY标识列,并为该列设置UNIQUE约束,使该ID列能唯一定位行,用于触发器中.
请楼主参考以下代码.首先建立了二个表,testtable1是主键表,主键列为pid,testtable2为外键表,外键为ChildID(级联),fidx(禁止INSERT和UPDATE约束),fidy(禁止INSERT和UPDATE约束),为testtable1建立了UPDATE和DELETE触发器.请楼主测试一下.

--------------------------------------------------------

if object_id('testtable2') is not null
drop table testtable2
if object_id('testtable1') is not null
drop table testtable1
GO
----创建主键表,主键列为pid,IDENTITY列为id,并为id设置UNIQUE约束,保证id唯一
CREATE TABLE testTable1(id int IDENTITY(1,1) NOT NULL ,pid varchar(10) NOT NULL) ON [PRIMARY]
GO
ALTER TABLE testTable1 ADD
CONSTRAINT PK_testTable1 PRIMARY KEY  CLUSTERED (pid) ON [PRIMARY] ,
CONSTRAINT IX_testTable1 UNIQUE  NONCLUSTERED (id)  ON [PRIMARY]
GO
----创建外键表,ChildID为级联更新外键,fidx和fidy为非级联外键
CREATE TABLE testTable2(ChildID varchar(10),fidx varchar(10),fidy varchar(10)) ON [PRIMARY]
GO
ALTER TABLE testTable2 ADD
CONSTRAINT FK_testTable2_testTable1 FOREIGN KEY (ChildID) REFERENCES testTable1(pid) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION ,
CONSTRAINT FK_testTable2_testTable11 FOREIGN KEY (fidx) REFERENCES testTable1(pid) NOT FOR REPLICATION ,
CONSTRAINT FK_testTable2_testTable12 FOREIGN KEY (fidy) REFERENCES testTable1(pid) NOT FOR REPLICATION
----禁止非级联外键的'强制INSERT和UPDATE约束'
alter table testTable2 nocheck constraint FK_testTable2_testTable11
alter table testTable2 nocheck constraint FK_testTable2_testTable12
GO
----插入测试数据
insert into testtable1(pid) values('1')
insert into testtable1(pid) values('2')
insert into testtable1(pid) values('3')
insert into testtable1(pid) values('4')
insert into testtable1(pid) values('5')
insert into testtable1(pid) values('6')
insert into testtable2(ChildID,fidx,fidy) values('1','6','6')
insert into testtable2(ChildID,fidx,fidy) values('2','5','5')
insert into testtable2(ChildID,fidx,fidy) values('3','4','4')
insert into testtable2(ChildID,fidx,fidy) values('4','3','3')
insert into testtable2(ChildID,fidx,fidy) values('5','2','2')
insert into testtable2(ChildID,fidx,fidy) values('5','1','1')
GO
----创建UPDATE触发器
CREATE TRIGGER tri_update_tab1 on testtable1
for update
as
if update(pid)
begin
update a set fidx = i.pid from testtable2 a,inserted i,deleted d where a.fidx = d.pid and i.id = d.id
update a set fidy = i.pid from testtable2 a,inserted i,deleted d where a.fidy = d.pid and i.id = d.id
/*----如果楼主的表中没有id列,请使用以下代码,使inserted表和deleted表能行对应
if object_id('tempdb..#inserted') is not null
drop table #inserted
if object_id('tempdb..#deleted') is not null
drop table #deleted
select ids = identity(int,1,1),* into #inserted from inserted
select ids = identity(int,1,1),* into #deleted from deleted
update a set x = i.pid from table2 a,#inserted i,#deleted d where a.x = d.pid and i.ids = d.ids
update a set y = i.pid from table2 a,#inserted i,#deleted d where a.y = d.pid and i.ids = d.ids
drop table #inserted,#deleted
*/
end
GO
----创建DELETE触发器,将外键值设为NULL
CREATE TRIGGER tri_delete_tab1 on testtable1
for delete
as
update a set fidx = NULL from testtable2 a,deleted d where a.fidx = d.pid
update a set fidy = NULL from testtable2 a,deleted d where a.fidy = d.pid
GO

--------------------------------------------------------

请在查询分析器中进行测试:
----测试UPDATE触发器(请使用right(rtrim(pid),1)恢复为原来的pid值)
update a set pid = 'T' + pid from testtable1 a
select 'Afterupdate',* from testtable2
----测试DELETE触发器
delete testtable1 where pid in('T1','T2')
select 'Afterdelete',* from testtable2
----恢复pid原值
update a set pid = right(rtrim(pid),1) from testtable1 a
select * from testtable2

--drop table testtable2,testtable1