-- sql 2005的错误处理容易控制, 因为sql 2005 可以直接删除, 通过错误处理来确定是否需要更新.-- 示例如下.USE tempdb GOCREATE TABLE m(id int primary key, bz bit) INSERT m SELECT 1, 0 UNION ALL SELECT 2, 0CREATE 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 GODROP TABLE c, m DROP PROC dbo.p_delete
-- sql 2000对错误处理不好控制, 一般还是建议做判断DECLARE @tbname sysname, @id int SELECT @tbname = N'm', -- 基础数据表 @id = 2 -- 要删除的id的值 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, @id, @bz OUT IF @bz = 1 BEGIN UPDATE m SET bz = 1 WHERE id = @id RETURN END FETCH tb INTO @s END CLOSE tb DEALLOCATE tb DELETE FROM m WHERE id = @id
GOCREATE TABLE m(id int primary key, bz bit)
INSERT m SELECT 1, 0
UNION ALL SELECT 2, 0CREATE 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
GODROP TABLE c, m
DROP PROC dbo.p_delete
SELECT
@tbname = N'm', -- 基础数据表
@id = 2 -- 要删除的id的值
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, @id, @bz OUT
IF @bz = 1
BEGIN
UPDATE m SET bz = 1
WHERE id = @id
RETURN
END
FETCH tb INTO @s
END
CLOSE tb
DEALLOCATE tb
DELETE FROM m
WHERE id = @id