存储过程删除两表关联的数据记录

最新推荐文章于 2023-01-06 09:14:07 发布

weixin_30664615

最新推荐文章于 2023-01-06 09:14:07 发布

阅读量325

点赞数

文章标签：数据库 javascript ViewUI

原文链接：http://www.cnblogs.com/lizeyan/p/3402325.html

版权

表Device为设备表（主表），如下图

表Rxparam为参数表(从表)，如下图

该存储过程是查找Device表中CustomerID为62的所有设备Rxparam表对应的记录都删除。

CREATE PROCEDURE CopyCustomerInfo
(
  @FromCustomerID int,
  @ToCustomerID int,
  @CustomerType int
)
AS
BEGIN TRANSACTION
     DECLARE @Error int
     SET @Error=0
     DECLARE @DeviceCount int
     SET @DeviceCount=0

BEGIN

        SELECT @DeviceCount=COUNT(*) FROM Device WHERE CustomerID=@FromCustomerID;
         IF @DeviceCount>0
            BEGIN
              DELETE FROM RXParam WHERE DeviceID IN (SELECT DeviceID FROM Device WHERE CustomerID=@FromCustomerID);  --删除从表对应记录
              DELETE FROM Device WHERE CustomerID=@FromCustomerID;  --删除主表记录
              SET @Error=@Error+@@ERROR   --记录错误
            END
        IF @Error>0
           rollback  transaction;       --执行回滚
        ELSE
           COMMIT transaction;        --提交事务
END
END

exec CopyCustomerInfo

转载于:https://www.cnblogs.com/lizeyan/p/3402325.html