在SQL2000中，如何设置外键关系为删除参照表主键时，响应的依赖表外键为set null.

[frman]

企业管理器里似乎没有
SQL语句创建表时 又说set null语句非法

如果不支持的话 有什么办法可以达到相同效果？

[LouisXIV]

不支持

替代方案是用触发器来解决

[frman]

触发器没用过啊 怎么在一个表中对另一个表操作啊
比如我想写如果employee.number更新，那house.employenumber也更着更新

应该在哪个表写？谁能给个大概的样子吗 我比着来试试 谢谢了

[LouisXIV]

Create Trigger Trg_number
on number
for delete
as
update b
set id=null
from house.dbo.employenumber b inner join deleted a
on a.id=b.id

[hellowork]

如果主键表的主键更新,外键表的外键也同步更新的话,应该设置该外键为级联更新,这样主键列更新时,外键列自动会同步更新,这种更新是由SQLSERVER来完成的,多省事.例如楼主修改employee.number时,house.employenumber也跟着更新.
至于主键列的值被删除时,SQLSERVER2000没有将外键列自动设为NULL的功能,只能通过在主键表创建DELETE触发器来模拟"自动"设为NULL.假设主键表为employee,外键表为house,触发器为:
create trigger tri_delete
on employee
for delete
as
begin transaction
UPDATE a set employenumber = NULL
FROM house a,deleted d --被删除的主键列都在deleted表中
WHERE a.employenumber = d.number
if @@error > 0
begin
raiserror('Can not update foreign key!',16,1)
rollback
end
commit
go

[lzhs]

没有这样的功能,要触发器可以代替.

[frman]

但如果 使用级联更新后 触发器就不起作用了
因为在删除的时候会违反级联更新原则 而无法删除

我如果想为更新也写触发器的话是不是应该这样？

create trigger tri_delete
on employee
for update
as
UPDATE a set employenumber = 这个值怎么写？（我试inserted.number但无效）
FROM house a,deleted d
WHERE a.employenumber = d.number

[LouisXIV]

lz的想法是在自相矛盾

由于MSSQL不支持外键的Set Null属性

因此原本由外键承担的级联任务改由触发器来执行（即不存在外键关联，而已触发器从形式上达成外键关联）

不存在所谓的级联更新

[frman]

是啊 因为外键不支持set null
所以我现在放弃外键 想用触发器来做了

关键是上面那个值怎么写啊？

感谢大家的帮助

[frman]

非常感谢

[hellowork]

试了一下,果然和楼主说的一样.如果为外键设置了级联更新(ON UPDATE CASCADE),那么DELETE触发器被触发时就会导致外键约束错误.
如果楼主要使用触发器来解决的话,请试试:
if object_id('tri_delete') is not null
drop trigger tri_delete
if object_id('tri_update') is not null
drop trigger tri_update
go
----创建DELETE触发器
create trigger tri_delete
on employee
for delete
as
UPDATE a set employenumber = null
FROM house a,deleted d
WHERE a.employenumber = d.number
go
----创建UPDATE触发器.当employee表中没有IDENTITY标识列时,请使用此触发器
create trigger tri_update
on employee
for update
as
if @@rowcount = 1 /*如果只更新了一行(必须在触发器第一行判断@@rowcount)*/
UPDATE a set a.employenumber = i.number
FROM house a,deleted d,inserted i
WHERE a.employenumber = d.number
else /*如果更新了多行*/
begin
if object_id('tempdb..#tmp1') is not null
drop table #tmp1
if object_id('tempdb..#tmp2') is not null
drop table #tmp2
----为inserted表和deleted表生成用于行一一对应的id
select id = identity(int,1,1),number into #tmp1 from inserted
select id = identity(int,1,1),number into #tmp2 from deleted
----更新'外键列'
UPDATE a set a.employenumber = i.number
FROM house a, #tmp2 d, #tmp1 i
WHERE a.employenumber = d.number and d.id = i.id
----清除临时表
drop table #tmp1
drop table #tmp2
end
go
----如果employee表中有IDENTITY列,假设名称为id,则使用此UPDATE触发器
create trigger tri_update
on employee
for update
as
UPDATE a set a.employenumber = i.number
FROM house a,deleted d,inserted i
WHERE a.employenumber = d.number and d.id = i.id