当前位置: 首页 > 图文教程 > 数据库 > MSSQL > 实现删除主表数据时, 判断与之关联的外键表是否有数据

MSSQL
Sql语句密码验证的安全漏洞
SQL Server的Inner Join及Outer Join
恢复系统数据库
Sql2005如何用dtexec运行ssis(DTS)包
监测你的SQL SERVER--让瓶颈暴露
解读SQL Server2008的新语句MERGE
如何有效防止Java程序源码被人偷窥?
精彩的表数据排序分组
怎样做sql server数据库的还原
SQL Server 查询分析器快捷键集合
初探 SQL Server 2008 “Katmai”
剖析SQL Server2005 SQLCLR代码安全性
SQL Server2005的XML数据类型之基础篇
探讨SQL Server 2005.NET CLR编程
SQL Server与Oracle并行访问的本质区别
让你的MySQL数据库彻底与中文联姻
SQL Server 2005 CE软件环境需求
透视MySQL数据库之更新语句
深入浅出SQL之左连接、右连接和全连接
深入浅出SQL教程之Group by和Having

No. « 31 32 33 34 »

技术文章搜索

关键字

MSSQL 中的 实现删除主表数据时, 判断与之关联的外键表是否有数据

出处:互联网   整理: 软晨网（RuanChen.com）   发布: 2009-10-17   浏览: 45 ::
收藏到网摘: 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）