How to activate trigger by taking any input from user?

Question

--Server part :

alter trigger ProductInsteadOfDelete on Products  
  
instead of delete  
  
as  
  
declare @ProductName nvarchar(max)  
  
update Products  
  
set Discontinued = 1 where ProductName = @ProductName  

--Client part :

delete from Products where productName = 'Psi-Blade'  

This does not work unless I change set statement into this :

set Discontinued = 1 where ProductName = 'Psi-Blade'  

and then run the Client part

delete from Products where productName = 'Psi-Blade'  

it works only for 'Psi-Blade'

However I want it to work for any @ProductName input that is available in database(of course).

Doing set Discontinued = 1 where ProductName = @ProductName is not working.

Answer 1

That isn't how triggers work. Triggers are called when one or more rows are modified on a table. Within the trigger you get access to all the impacted rows, not the original query that was run. So any where clause on the original query isn't relevant here because the trigger just sees the rows impacted by that delete. What you have to do is take the impacted rows and find the corresponding rows in the original table and update them accordingly. Imagine, for example that your Products table has a ProductId column that uniquely identifies the row. Then you'd need to join the set of "deleted" rows with the original table to find which one's to update. Perhaps something like this.

   UPDATE Products  
   SET Discontinued = 1  
   FROM Products JOIN deleted on Products.ProductId = deleted.ProductId  

If my SQL is correct then this query takes all the rows to be deleted from the deleted pseudo table, joins them back to the existing Products table and sets the corresponding rows to discontinued. But you should test this.

Note that triggers can be raised on a single or multiple rows so you should never assume a trigger is being run because of a single row change. Always build the trigger to run against all rows that were modified.

Answer 2

Try another trigger:

create or alter trigger ProductInsteadOfDelete on Products  
instead of delete  
as  
begin  
    update Products                     
    set Discontinued = 1   
    where ProductName in (select ProductName from deleted)  
end  

However, you should change it to where ID in (select ID from deleted), where ID is the Primary Key of Products table.

Answer 3

Can you try to find the products from deleted virtual table inside your trigger? Use a singular value variable has potential problem if you delete more than one row.
Try this (not tested):

alter trigger ProductInsteadOfDelete on Products

instead of delete

as

merge Products tgt
using deleted d on d.productid=tgt.productid
When matched then
Update
set tgt.Discontinued = 1 ;

Answer 4

Hi @Cem Tuğanlı

The problem with your code is that although you define the variable ProductName, you do not assign it a value, so the subsequent update statement cannot find the line that needs to be updated.
I've made some modifications based on your code, you can give it a try.

create or alter trigger ProductInsteadOfDelete on Products  
instead of delete  
as  
declare @ProductName nvarchar(max);  
select @ProductName = ProductName from deleted;   
update Products set Discontinued = 1 where ProductName = @ProductName;  

Best regards,
Percy Tang

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.