0

I want to update my current row not all rows. I'm doing this. Can I set the value of update set using a variable? I am doing wrong procedure. please help.

CREATE TRIGGER changeActive 
ON offer_plans
AFTER UPDATE AS
BEGIN
DECLARE @a INT;
UPDATE offer_plans SET is_active = @a //Here I want to update the current updating row.
CASE 
WHEN is_active = 0 THEN @a = 1
WHEN is_active = 1 THEN @a = 0
END
END
GO
rakesh shrestha
  • 1,335
  • 19
  • 37
  • Your trigger would cause an infinite recursion, because an update would make the trigger fire, which in turn would do an update, etc. Check the duplicate link for some workarounds. – Tim Biegeleisen Dec 20 '18 at 11:11
  • @TimBiegeleisen thanks for marking it as a duplicate without reading it. But i have gone through all other questions. They are all about insert and updating other rows. All they say is you cannot update the other rows with trigger not from the one from which trigger is called. I dont want to update other rows. I want to update the current row only. Please help sir. – rakesh shrestha Dec 20 '18 at 11:17
  • That doesn't matter, because the trigger fires for an update on _any_ row. – Tim Biegeleisen Dec 20 '18 at 11:18
  • @TimBiegeleisen sir I'm so unable to grasp it. Can you please elaborate and light this up – rakesh shrestha Dec 20 '18 at 11:20
  • 1
    You can easily enough handle this logic from the update itself. Do that while you try to find a workaround. – Tim Biegeleisen Dec 20 '18 at 11:22
  • This is not mysql code are you trying to convert from sqlserver or is the question wrongly tagged? – P.Salmon Dec 20 '18 at 11:24
  • Its mysql. sorry I tried in on sql yog so there are `` left. – rakesh shrestha Dec 20 '18 at 11:25
  • If your case is really to set a value based on the other new values in the update, then you want to look at this answer: https://stackoverflow.com/a/29559195/7358209. You want to change the new.col_name instead of running another update. Also, you will need to change to a `BEFORE UPDATE` trigger. This is a duplicate, you just didn't know what you were looking for. That's happened to me many times. – Kevin Bott Dec 20 '18 at 17:50

0 Answers0