SQL：事务检查xx与应用表的重复性，同时向两个表中同时插入数据...

/********************************************************************************
作用:检查xx与应用表的重复性，同时向两个表中同时插入数据>>新增/删除
Author:xx           
Finish DateTime: 2011-3-15 11:31:13
Return Value:
            -1:存储过程执行失败
            -2:存在相同的主键
            Insert:返回插入自动ID
            Update:返回更新记录数
            Delete:返回删除记录数
*********************************************************************************/

ALTER PROCEDURE [dbo].[P_Res_Car_InsertDelete]
(
    @Erp_CarID  int = 0, -- Erp_CarID
    @P_CarID  int = 0, -- 租车ID
    @CarName  nvarchar(50) = '', -- 车型名
    @CarCardID  int = 0, -- 车品牌ID
    @BoxAmount  nvarchar(50) = '', -- 厢式/车体
    @SeatAmount  nvarchar(50) = '', -- 座位数
    @Shelves  nvarchar(50) = '', -- 变速箱
    @Quantity  nvarchar(50) = '', -- 排量
    @OilUse  nvarchar(50) = '', -- 油耗
    @OilSandard  nvarchar(50) = '', -- 燃油标准
    @CarIntroduce  nvarchar(1000) = '', -- 车型介绍
    @Adding  bit = 0, -- 追加标识- 云计算默认为true ，应用平台为false
    @FillName  nvarchar(50) = '', -- 发布人
    @FillDateTime  datetime = getdate, -- 发布时间
    @DataTable_Action_  varchar(10) = ''  -- 操作方法 Insert:增加 Update:修改 Delete:删除
)
AS
    DECLARE @ReturnValue varchar(18) -- 返回操作结果
    DECLARE @P_Cid int = 88 -- 提交云计算表返回的id
    SET @ReturnValue = -1
    
    
    -- 新增
    IF (@DataTable_Action_='Insert')
    begin   
      if  exists(select  *  from  P_Car  where  CarName=@CarName and BoxAmount=@BoxAmount and Shelves=@Shelves and Quantity=@Quantity ) 
         begin
         --云计算平台已存在相同记录
         SET @ReturnValue = -9         
         END        
      else if  exists(select  *  from  Res_Car  where  CarName=@CarName and BoxAmount=@BoxAmount and Shelves=@Shelves and Quantity=@Quantity ) 
         begin
         --应用平台已存在相同记录
         SET @ReturnValue = -99
         END    
       else
         BEGIN    
            begin tran --开始事务
          
            --提交云计算
            INSERT INTO P_Car(CarName,CarCardID,BoxAmount,SeatAmount,Shelves,Quantity,OilUse,OilSandard,CarIntroduce,Auditing,FillName,FillDateTime,ERPState
            ) VALUES (@CarName,@CarCardID,@BoxAmount,@SeatAmount,@Shelves,@Quantity,@OilUse,@OilSandard,@CarIntroduce,'0',@FillName,@FillDateTime ,'1')
            if @@error<>0
              begin
              rollback tran --回滚事务
              SET @ReturnValue = -1
              end
              
            SELECT @P_Cid = SCOPE_IDENTITY() 
            --提交应用平台
            INSERT INTO Res_Car(P_CarID,CarName,CarCardID,BoxAmount,SeatAmount,Shelves,Quantity,OilUse,OilSandard,CarIntroduce,Adding,FillName,FillDateTime
            ) VALUES (@P_Cid,@CarName,@CarCardID,@BoxAmount,@SeatAmount,@Shelves,@Quantity,@OilUse,@OilSandard,@CarIntroduce,@Adding,@FillName,@FillDateTime)
            if @@error<>0
              begin
              rollback tran --回滚事务
              SET @ReturnValue = -1 
              end
            commit tran --提交事务
            SELECT @ReturnValue = SCOPE_IDENTITY()     
       
      end
     end 
    
    --删除
    IF (@DataTable_Action_='Delete')
    BEGIN
        DELETE Res_Car WHERE (Erp_CarID=@Erp_CarID)
        SET @ReturnValue = @@ROWCOUNT
    END
    SELECT @ReturnValue