转:http://blog.csdn.net/zjcxc/archive/2006/09/18/1236639.aspx
问题描述:
某个基础信息表,与系统中
30
多个表存在外键关系,当删除基础数据时,需要判断是否已经被用过,如果用过则更改标志位,如果没有用过则直接删除,如何能很好实现这个处理?最好能够自动适应表的变化
问题解决(SQL Server 2005)
--
SQL Server 200
5
的错误处理容易控制
,
因此,
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
)