查看完整版本: 实现删除主表数据时, 判断与之关联的外键表是否有数据引用, 有标志, 无则删除

实现删除主表数据时, 判断与之关联的外键表是否有数据引用, 有标志, 无则删除

问题描述：
某个基础[wiki]信息[/wiki]表，与[wiki]系统[/wiki]中30多个表存在外键关系，当删除基础数据时，需要判断是否已经被用过，如果用过则更改标志位，如果没有用过则直接删除，如何能很好实现这个处理？最好能够自动适应表的变化

问题解决（[wiki]SQL[/wiki]Server2005）
--SQLServer2005的错误处理容易控制,因此，SQLServer2005中可以直接删除,通过错误处理来确定是否需要更新.

--示例如下.
USEtempdb
GO

CREATETABLEm(
idintPRIMARYKEY,
bzbit)
INSERTmSELECT1,0
UNIONALLSELECT2,0

CREATETABLEc(
idintprimarykey,
a_idintreferencesm(id)
ONDELETENOACTION)
INSERTcSELECT1,1
GO

--删除处理存储过程
CREATEPROCdbo.p_delete
@idint
AS
SETNOCOUNTON
BEGINTRY
BEGINTRAN
DELETEFROMmWHEREid=@id
COMMITTRAN
ENDTRY
BEGINCATCH
ROLLBACKTRAN
IFERROR_NUMBER()=547--如果是外键约束错误
BEGIN
BEGINTRY
BEGINTRAN--更新标志
UPDATEmSETbz=1
WHEREid=@id
COMMITTRAN
ENDTRY
BEGINCATCH
SELECTERROR_NUMBER(),ERROR_MESSAGE()
ENDCATCH
END
ELSE
SELECTERROR_NUMBER(),ERROR_MESSAGE()
ENDCATCH
GO

--调用
EXECdbo.p_delete1
EXECdbo.p_delete2
SELECT*FROMm
SELECT*FROMc
GO

DROPTABLEc,m
DROPPROCdbo.p_delete

问题解决（SQLServer2000）
--SQLServer2000对错误处理不好控制,一般还是建议做判断
--通过系统表查询系统表，可以获取某个表关联的所有外键表

--示例存储过程
CREATEPROCdbo.p_Delete
@tbnamesysname,--基础数据表名
@PkFieldNamesysname,--基础数据表关键字段名
@PkValueint--要删除的基础数据表关键字值
AS
SETNOCOUNTON
DECLARE@bzbit,@snvarchar(4000)
DECLAREtbCURSORLOCAL
FOR
SELECTN'
SET@bz=CASEWHENEXISTS(
SELECT*FROM'+QUOTENAME(@tbname)
+N'A,'+QUOTENAME(OBJECT_NAME(B.fkeyid))
+N'B
WHEREA.'+QUOTENAME((SELECTnameFROMsyscolumnsWHEREcolid=B.rkeyANDid=B.rkeyid))
+N'=B.'+QUOTENAME((SELECTnameFROMsyscolumnsWHEREcolid=B.fkeyANDid=B.fkeyid))
+N'ANDA.'+QUOTENAME((SELECTnameFROMsyscolumnsWHEREcolid=B.rkeyANDid=B.rkeyid))
+N'=@id)THEN1ELSE0END'
FROMsys[wiki]object[/wiki]sA
JOINsysforeignkeysB
ONA.id=B.constid
JOINsysobjectsC
ONA.parent_obj=C.id
WHEREA.x[wiki]type[/wiki]='f'
ANDC.xtype='U'
ANDOBJECT_NAME(B.rkeyid)=@tbname
OPENtb
FETCHtbINTO@s
WHILE@@FETCH_STATUS=0
BEGIN
EXECsp_executesql@s,[email=N]N'@tbname[/email]sysname,@idint,@bzbitOUT',@tbname,@PkValue,@bzOUT
IF@bz=1
BEGIN
SET@s=N'UPDATE'+QUOTENAME(@tbname)
+N'SETbz=1WHERE'+QUOTENAME(@PkFieldName)
+N'=@id'
EXECsp_executesql@s,[email=N]N'@id[/email]int',@PkValue

RETURN
END

FETCHtbINTO@s
END
CLOSEtb
DEALLOCATEtb

SET@s=N'DELETEFROM'+QUOTENAME(@tbname)
+N'WHERE'+QUOTENAME(@PkFieldName)
+N'=@id'
EXECsp_executesql@s,[email=N]N'@id[/email]int',@PkValue
GO

注意事项
设置表的主/外键关系的时候，不要设置级联删除（ONDELETECASCADE）