Click here to Skip to main content
15,112,333 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 3: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.
Member 11265547 6-Feb-15 9:21am
   
yes it is a string
ZurdoDev 6-Feb-15 9:23am
   
Unless it is getting truncated it should work. What makes you think it isn't working? Error?
Member 11265547 6-Feb-15 9:41am
   
I know its not working because when I search through the deleted products and the product code does not have the 'D_' prefix.
ZurdoDev 6-Feb-15 9:42am
   
Can you then update your question? Because your question shows you deleting and not updating.
Liju Sankar 6-Feb-15 9:10am
   
you mentioned that "when a product is deleted it mainly change the field name 'delete' from 0 to 1", but the SP you attached here does not have any update statements
Member 11265547 6-Feb-15 9:25am
   
You may just have made me realized I've done something stupid, It looks like there may be a second procedure for deleting products because this one like you say does not update the 'delete' field.
I'm looking for another one now!
Liju Sankar 6-Feb-15 9:37am
   
:)
Member 11265547 6-Feb-15 10:02am
   
so 'DELETE FROM Product WHERE ProductID = @ProductID' would delete the product from the table completely?

I thought there was another procedure that handles what happens when a product is deleted but I've not found any yet still looking though, there's 716 to look through, loosing the will!
Member 11265547 6-Feb-15 10:11am
   
Ok so I tried changing the error message to see if it shows up when i try to delete a product with stock and it did so this is the right procedure!

so maybe i'm putting the code into the wrong part of the code where should I insert (UPDATE Product SET ProductCode = 'D_' + ProductCode WHERE ProductID = @ProductID) in to the procedure
CHill60 6-Feb-15 10:29am
   
replace the DELETE FROM Product WHERE ProductID = @ProductID with UPDATE Product SET ProductCode = 'D_' + ProductCode WHERE ProductID = @ProductID
Member 11265547 6-Feb-15 11:23am
   
this does work but I need it to do both things
CHill60 6-Feb-15 11:58am
   
There is no point in updating a record to then just delete it. You are removing it from the database. Better would be to have a "purge" flag on the table which is set to True when you want to "delete" the record, and filter any queries on purge=false
Member 11265547 9-Feb-15 3:08am
   
The reason I need to change the code before I delete it is because it is causing problems with some software that imports into the database. it is importing the data into the deleted products when there is a duplicate product code instead of the non deleted products.
CHill60 9-Feb-15 4:32am
   
I don't understand - if you are deleting the row then the fault must be in your import. Or do you have a trigger on the delete that copies the data elsewhere?
Member 11265547 9-Feb-15 4:45am
   
finally found out how to do it, yes there was a trigger (sorry wasn't aware there was one) so I added the update to the trigger and it works great now.

Thanks everyone for your help.
and sorry I'm fairly new to SQL but its part of my job now so I appreciate the help.
Liju Sankar 6-Feb-15 9:16am
   
I tried this simple query, update is working good for me.


declare @product as table(id int, Name varchar(max))
insert @product
select 1, 'Test1'

select * from @product

Result : 1, Test1

update @product
set Name = 'D_' + Name where id = 1

select * from @product
Result = 1, D_Test1
Liju Sankar 6-Feb-15 9:19am
   
as RyanDev pointed out, update will fail if the product code is not a string.
Niju1 6-Feb-15 9:37am
   
The parameter used is integer and you said it is string , update will fail if it is string
Member 11265547 6-Feb-15 9:49am
   
I know the UPDATE works because I have used the same thing to change a batch of products

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 ..

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
   

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