存储过程删除主从表，回滚操作

用存储过程操作删除主从表记录，操作成功提交事务，失败回滚操作，避免删除异常误删数据。

ALTER PROCEDURE [dbo].[CopyCustomerDelete]
    /*
    (
    @parameter1 int = 5,
    @parameter2 datatype OUTPUT
    )
    */
    (
          @FromCustomerID int,
          @CustomerType int,
          @Result int output
    )
AS
    /* SET NOCOUNT ON */
BEGIN TRANSACTION
     DECLARE @Error int
     SET @Error=0
    IF @CustomerType>-1
     BEGIN
       DELETE FROM LightingControlSceneLevel WHERE  SceneID IN (SELECT SceneID FROM LightingControlScene where CustomerID=@FromCustomerID)--删除Machine场景配置记录
               
       DELETE FROM ParadoxScene WHERE SceneID IN (SELECT SceneID FROM LightingControlScene WHERE CustomerID=@FromCustomerID) --删除Machine场景Paradox配置记录
            
         DELETE FROM LightingControlSceneComfort WHERE SceneID IN (SELECT SceneID FROM LightingControlScene WHERE CustomerID=@FromCustomerID)--删除Comfort场景配置记录

       DELETE FROM LightingControlSceneStatus WHERE CustomerID=@FromCustomerID --删除Comfort场景状态记录

       DELETE FROM LightingControlScene WHERE CustomerID=@FromCustomerID --删除场景
            
       DELETE FROM LightingControlCircuit WHERE LCID IN (SELECT LCID FROM LightingControl WHERE CustomerID=@FromCustomerID)
           
       DELETE FROM LightingControl WHERE CustomerID=@FromCustomerID

       DELETE FROM Alarm WHERE CustomerID=@FromCustomerID

       DELETE FROM RXParam WHERE DeviceID IN (SELECT DeviceID FROM Device WHERE CustomerID=@FromCustomerID) --删除从表对应记录

       DELETE FROM Device WHERE CustomerID=@FromCustomerID   --删除主表记录

       DELETE FROM Comfort WHERE CustomerID=@FromCustomerID
          
       DELETE FROM CCTVGroup WHERE CustomerID=@FromCustomerID
          
       DELETE FROM Cctv WHERE CustomerID=@FromCustomerID
          
       DELETE FROM Area WHERE CustomerID=@FromCustomerID
          
       DELETE FROM ComfortRX WHERE CustomerID=@FromCustomerID
       --有错误则回滚，没有错误则提交事务
       SET @Error=@Error+@@ERROR
       IF @Error>0
           BEGIN            
                ROLLBACK TRANSACTION;
                SET @Result=-1
           END
       ELSE
           BEGIN
                COMMIT TRANSACTION;
                SET @Result=1
           END
     END

View Code

 

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