繁體中文当前位置: 首页 > 图文教程 > 数据库 > MSSQL > 实现删除主表数据时, 判断与之关联的外键表是否有数据
- MSSQL
- SQL Server数据库搭建农村信息化的方案
- 建立合理的索引提高SQL Server的性能
- 如何利用SQL Server 2005中的模板参数
- 浅析SQL Server三大算法的I/O成本
- SQL Server与Oracle数据库在安全性上的异同
- 解析SQL Server与ASP互操作的时间处理
- sql server 中删除默认约束的通用sql脚本
- 实用技巧：优化SQL Server数据库查询方法
- SQL Server的BUILTIN\Administrators用户
- 史上最简单的方法复制或迁移Oracle数据库
- 数据库自动化技术弥补数据库DBA短缺难题
- 教你怎样在Oracle数据库中高速导出/导入
- 怎样在SQL Server中去除表中不可见字符
- 怎样使用 SQL Server 数据库嵌套子查询
- 细化解析：转换 SQL数据库时的疑难问题
- 细化解析：SQL Server 2000 的各种版本
- 轻松掌握 SQL Server 2000数据库的构架
- 带你轻松了解 SQL Server数据库的组成
- 解决SQL Server日志文件损坏严重的问题
- 检测你的SQL Server是否有特洛伊木马
MSSQL 中的 实现删除主表数据时, 判断与之关联的外键表是否有数据
出处:互联网 整理: 软晨网（RuanChen.com） 发布: 2009-10-17 浏览: 143 ::
收藏到网摘: n/a
问题描述：
某个基础信息表，与系统中30多个表存在外键关系，当删除基础数据时，需要判断是否已经被用过，如果用过则更改标志位，如果没有用过则直接删除，如何能很好实现这个处理？最好能够自动适应表的变化
问题解决（SQL Server 2005）
-- SQL Server 2005的错误处理容易控制, 因此， SQL Server 2005中可以直接删除, 通过错误处理来确定是否需要更新.
-- 示例如下.
USE tempdb
GO
CREATE TABLE m(
id int PRIMARY KEY,
bz bit)
INSERT m SELECT 1, 0
UNION ALL SELECT 2, 0
CREATE TABLE c(
id int primary key,
a_id int references m(id)
ON DELETE NO ACTION)
INSERT c SELECT 1, 1
GO
-- 删除处理存储过程
CREATE PROC dbo.p_delete
@id int
AS
SET NOCOUNT ON
BEGIN TRY
BEGIN TRAN
DELETE FROM m WHERE id = @id
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
IF ERROR_NUMBER() = 547 -- 如果是外键约束错误
BEGIN
BEGIN TRY
BEGIN TRAN -- 更新标志
UPDATE m SET bz = 1
WHERE id = @id
COMMIT TRAN
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
END
ELSE
SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
GO
-- 调用
EXEC dbo.p_delete 1
EXEC dbo.p_delete 2
SELECT * FROM m
SELECT * FROM c
GO
DROP TABLE c, m
DROP PROC dbo.p_delete
问题解决（SQL Server 2000）
-- SQL Server 2000 对错误处理不好控制, 一般还是建议做判断
-- 通过系统表查询系统表，可以获取某个表关联的所有外键表
-- 示例存储过程
CREATE PROC dbo.p_Delete
@tbname sysname, -- 基础数据表名
@PkFieldName sysname, -- 基础数据表关键字段名
@PkValue int -- 要删除的基础数据表关键字值
AS
SET NOCOUNT ON
DECLARE @bz bit, @s nvarchar(4000)
DECLARE tb CURSOR LOCAL
FOR
SELECT N'
SET @bz = CASE WHEN EXISTS(
SELECT * FROM ' + QUOTENAME(@tbname)
+ N' A, ' + QUOTENAME(OBJECT_NAME(B.fkeyid))
+ N' B
WHERE A.' + QUOTENAME((SELECT name FROM syscolumns WHERE colid = B.rkey AND id = B.rkeyid))
+ N' = B.' + QUOTENAME((SELECT name FROM syscolumns WHERE colid = B.fkey AND id = B.fkeyid))
+ N' AND A.' + QUOTENAME((SELECT name FROM syscolumns WHERE colid = B.rkey AND id = B.rkeyid))
+ N' = @id) THEN 1 ELSE 0 END'
FROM sysobjects A
JOIN sysforeignkeys B
ON A.id= B.constid
JOIN sysobjects C
ON A.parent_obj = C.id
WHERE A.xtype = 'f'
AND C.xtype = 'U'
AND OBJECT_NAME(B.rkeyid) = @tbname
OPEN tb
FETCH tb INTO @s
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @s, N'@tbname sysname, @id int, @bz bit OUT', @tbname, @PkValue, @bz OUT
IF @bz = 1
BEGIN
SET @s = N'UPDATE ' + QUOTENAME(@tbname)
+ N' SET bz = 1 WHERE ' + QUOTENAME(@PkFieldName)
+ N' = @id'
EXEC sp_executesql @s, N'@id int', @PkValue
RETURN
END
FETCH tb INTO @s
END
CLOSE tb
DEALLOCATE tb
SET @s = N'DELETE FROM ' + QUOTENAME(@tbname)
+ N' WHERE ' + QUOTENAME(@PkFieldName)
+ N' = @id'
EXEC sp_executesql @s, N'@id int', @PkValue
GO
注意事项
设置表的主/外键关系的时候，不要设置级联删除（ON DELETE CASCADE）
评论 (0) All
