存储过程和更新 [英] Stored Procedures and UPDATE

问题描述

我们有这个存储过程，当产品被删除时，它主要将字段名称'delete'从0更改为1，但我试图让它更改产品代码，使其具有前缀'D_'，当它是删除



我尝试添加（UPDATE Product SET ProductCode ='D_'+ ProductCode WHERE ProductID = @ProductID）到代码但我不工作。



可以帮助一下



这是当前未更改的代码

  CREATE   PROCEDURE  ProductDelete 
  @ProductID   int  
 
  AS  
  SET   NOCOUNT   ON  
 
  IF   EXISTS （ SELECT  *  FROM 股票
  WHERE  ProductID =  @ ProductID  
  AND （StockActual<>  0  或 StockAvailable<>  0  
 或 StockOnOrder<>  0  或 StockDue<>  0  
 或 StockAllocated<>  0  
 或 StockReserved<>  0  
 或 StockSuspense<>  0  或 StockThirdParty<>  0  
 或 StockAwaitingProcess<>  0  
 或 StockAwaitingProcessNotAvailable<>  0 ））
  BEGIN  
  RAISERROR （' 此产品的库存余额为非零。无法删除。' ， 16 ， -  1）
 
  RETURN   1  
  END  
 
  DELETE   FROM 产品 WHERE  ProductID =  @ ProductID  
 
 返回  0  
 
 
 < span class =code-keyword> SET   NOCOUNT   OFF  
  GO  

解决方案

要删除的解决方案摘要来自未答复列表的问题...



OP在桌面上发现了一个删除前触发器。这似乎是将已删除的记录复制到另一个表。随后的更新随后出现重复记录问题。



存储过程保持不变，但相当于

 UPDATE Product SET ProductCode ='D_ '+ ProductCode WHERE ProductID = @ProductID 

已移至触发器（实际表名未知）

尝试..

 创建 程序 ProductDelete 
  @ ProductID   int ，
  @ outputRes  vachar（max）输出 
 
  AS  
  SET   NOCOUNT   ON  
 声明  @ outputRes   as   varchar （max）
 
  IF   EXISTS （ SELECT  *  FROM 股票
  WHERE  ProductID =  @ ProductID  
  AND （StockActual<>  0  或 StockAvailable<>  0  
 或 StockOnOrder<>  0  或 StockDue<>  0  
 或 StockAllocated<>  0  
 或 StockReserved<>  0  
 或 StockSuspense<>  0  或 StockThirdParty<>  0  
 或 StockAwaitingProcess<>  0  
 或 StockAwaitingProcessNotAvailable<>  0 ））
  BEGIN  
  set  outputRes = ' 此产品的库存余额为非零。它无法删除。' 
 返回  @ outputRes  
 < span class =code-keyword> END  
 否则 
 开始 
   -    DELETE FROM Product WHERE ProductID = @ProductID  
   -   已永久删除 
 更新产品 SET  ProductCode = '  D _' + ProductCode  WHERE  ProductID =  @ ProductID  
   -   未删除，但更新的ProductCode前缀为_D 
  set  @outputRes = ' 已成功删除。' 
 返回  @ outputRes  
 结束 
 
  SET   NOCOUNT   OFF  
  GO  

We have this stored procedure for when a product is deleted it mainly change the field name 'delete' from 0 to 1 but i'm trying to make it change the product code to have a prefix of 'D_' when it is deleted

I have tried adding (UPDATE Product SET ProductCode = 'D_' + ProductCode WHERE ProductID = @ProductID) to the code but I does not work.

can some one help

This is the current unchanged code

CREATE PROCEDURE ProductDelete
	@ProductID	int

AS
SET NOCOUNT ON
	
	IF EXISTS(SELECT * FROM Stock 
             WHERE ProductID = @ProductID 
             AND (StockActual <> 0 OR StockAvailable <> 0 
                  OR StockOnOrder <> 0 OR StockDue <> 0 
                  OR StockAllocated <> 0 
                  OR StockReserved <> 0 
                  OR StockSuspense <> 0 OR StockThirdParty <> 0 
                  OR StockAwaitingProcess <> 0 
                  OR StockAwaitingProcessNotAvailable <> 0))
	BEGIN
		RAISERROR('This product has a non zero stock balance. It cannot be deleted.', 16, -1)
	
		RETURN 1
	END
	
	DELETE FROM Product WHERE ProductID = @ProductID
	
	RETURN 0
	
	
SET NOCOUNT OFF
GO

解决方案

Summary of the solution to remove the question from the unanswered list ...

OP discovered a before-delete trigger on the table. This appears to be copying the deleted record to another table. Subsequent updates were then hitting duplicate record issues.

The stored procedure remains as it was, but the equivalent of

UPDATE Product SET ProductCode = 'D_' + ProductCode WHERE ProductID = @ProductID

has been moved to the trigger (actual tablename not known)

try ..

CREATE PROCEDURE ProductDelete
    @ProductID  int,
    @outputRes vachar(max) output

AS
SET NOCOUNT ON
declare @outputRes as varchar(max)

    IF EXISTS(SELECT * FROM Stock
             WHERE ProductID = @ProductID
             AND (StockActual <> 0 OR StockAvailable <> 0
                  OR StockOnOrder <> 0 OR StockDue <> 0
                  OR StockAllocated <> 0
                  OR StockReserved <> 0
                  OR StockSuspense <> 0 OR StockThirdParty <> 0
                  OR StockAwaitingProcess <> 0
                  OR StockAwaitingProcessNotAvailable <> 0))
    BEGIN
       set outputRes='This product has a non zero stock balance. It cannot be deleted.'
       return @outputRes
    END
Else
Begin
    --DELETE FROM Product WHERE ProductID = @ProductID
--permanently deleted
UPDATE Product SET ProductCode = 'D_' + ProductCode WHERE ProductID = @ProductID
--not deleted but updated ProductCode  with a prefix '_D'
set @outputRes='Deleted successfully.'
return @outputRes
End

SET NOCOUNT OFF
GO

这篇关于存储过程和更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！