Click here to Skip to main content
15,918,516 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
SQL
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
Posted
Updated 6-Feb-15 2:59am
v2
Comments
CHill60 6-Feb-15 9:01am    
What do you mean by "doesn't work"? The update doesn't happen or an error is thrown?
Member 11265547 6-Feb-15 9:09am    
sorry, the update doesn't happen but no error is thrown up!
thanks
CHill60 6-Feb-15 9:13am    
If you pass in an ID where there is non zero stock balance do you get the expected error thrown?
Member 11265547 6-Feb-15 9:39am    
yes
ZurdoDev 6-Feb-15 9:09am    
The update looks fine, unless ProductCode is integer or something not a string.

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)
 
Share this answer
 
try ..

SQL
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900