CODEWITHSUNDEEP
mysqltriggers
Alex
Alex

Reputation: 39

MYSQL AFTER Trigger UPDATE IF

I want to UPDATE my row after it has been updated by the user. Example if after the user selects 32, and Principal previously entered is > 1000. Then Update the 32 to 40.

CREATE TRIGGER updateusers_id AFTER UPDATE ON Table
FOR EACH ROW
BEGIN       

IF(NEW.users_id = 32 AND Table.Principal > 50000) THEN
UPDATE loans SET users_id = 40;
END IF;

END

Upvotes: 0

Views: 74

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179094

Think about it for a second and you'll realize the first problem: if what you actually need here is an AFTER update trigger... then what would a BEFORE update trigger do?

AFTER UPDATE means after the update query has already changed the row data. What you are actually looking to is BEFORE UPDATE -- you want to hijack the update in mid-stream, before the table data actually gets changed, and potentially modify the values that will actually be written to the database. 

UPDATE loans SET users_id = 40;

There are two problems with this. First, you know what an UPDATE without WHERE does... right? It updates all the rows in the table. Luckily, the server didn't let you do that. But the solution is not to add a WHERE clause -- it's this:

IF(NEW.users_id = 32 AND NEW.Principal > 50000) THEN
  SET NEW.users_id = 40;
END IF;

The NEW pseudotable contains the row data that the user has tried to cause the UPDATE (that fired the trigger) to write to the row. You are in a BEFORE UPDATE ... FOR EACH ROW trigger, so setting NEW values overrides anything the user tried to do... which appears to be what you are wanting to accomplish.

Upvotes: 1

Related Questions