SQL存储过程:删除基础资料前逻辑判断,检查是否有关联业务数据
作者:C/S框架网|www.cscode.ne  发布日期:2020-05-14 13:39:40
  SQL存储过程:删除基础资料前逻辑判断,检查是否有关联业务数据

我们设计的业务系统,在删除基础资料(比如:客户资料、用户资料)前必须检查当前用户或客户是否关联业务数据、当前用户是否创建业务数据,若有关联业务数据是不可删除的,避免产生无效数据。


以下提供存储过程仅供参考。


删除客户资料存储过程:


SQL Code:

ALTER PROCEDURE [dbo].[usp_DeleteCustomer]
  
@CustomerID VARCHAR(30)--客户编码
AS
BEGIN
  
  
/************************************************************
  功能:删除客户
  
  SELECT * FROM dbo.tb_Customer
  SELECT * FROM dbo.tb_OrderDetail
  
  
  usp_DeleteCustomer 'admin'
  usp_DeleteCustomer 'C-000003'
  
  ************************************************************
*/
  
  
DECLARE @Count INT
  
SELECT @Count=0;
  
  
--检查业务关联
  
SELECT @Count=@Count+COUNT(1) FROM dbo.tb_OrderDetail WHERE CustomerId=@CustomerID
  
SELECT @Count=@Count+COUNT(1) FROM dbo.tb_GoodsDownDtl WHERE CustomerId=@CustomerID
  
SELECT @Count=@Count+COUNT(1) FROM dbo.tb_GoodsUpDtl WHERE CustomerId=@CustomerID
  
  
IF @Count>0
  
BEGIN
  
SELECT 0 AS Result,'当前客户关联业务数据,不可删除!' AS ErrMsg;
  
RETURN;
  
END ELSE
  
BEGIN
  
--删除客户资料
  
DELETE FROM tb_Customer WHERE CustomerID=@CustomerID
  
SELECT 1 AS Result,NULL AS ErrMsg;
  
END;
  
  
END
  
  
//来源:C/S框架网 | www.csframework.com | QQ:23404761




SQL Code:

ALTER PROCEDURE [dbo].[usp_DeleteEmployee]
  
@EmpID VARCHAR(30)--员工编码
AS
BEGIN
  
  
/************************************************************
  功能:删除员工资料
  
  SELECT * FROM dbo.tb_Employee
  SELECT * FROM dbo.tb_GoodsDownDtl
  
  
  usp_DeleteEmployee 'admin'
  usp_DeleteEmployee 'E-000003'
  usp_DeleteEmployee 'E-000012'
  
  ************************************************************
*/
  
  
DECLARE @Count INT
  
SELECT @Count=0;
  
  
--检查业务关联
  
SELECT @Count=@Count+COUNT(1) FROM dbo.tb_GoodsDownDtl WHERE EmPId=@EmpID
  
SELECT @Count=@Count+COUNT(1) FROM dbo.tb_GoodsUpDtl WHERE EmPId=@EmpID
  
  
IF @Count>0
  
BEGIN
  
SELECT 0 AS Result,'当前员工关联业务数据,不可删除!' AS ErrMsg;
  
RETURN;
  
END ELSE
  
BEGIN
  
--删除客户资料
  
DELETE FROM tb_Employee WHERE EmpID=@EmpID
  
SELECT 1 AS Result,NULL AS ErrMsg;
  
END;
  
  
END
  
  
//来源:C/S框架网 | www.csframework.com | QQ:23404761




C/S框架网|原创精神.创造价值.打造精品

扫一扫加微信
C/S框架网作者微信 C/S框架网|原创作品.质量保障.竭诚为您服务

上一篇 下一篇