I have a trigger that checks to see if certain fields changed during the update.
If any of these fields changed I update another table.
I'd like to "break" out of the if conditions as soon as I know that something changed.
Is there a way to do this within a MySQL Trigger?
What I have works, but It seems inefficient.
CREATE TRIGGER profile_trigger
BEFORE UPDATE ON profile
FOR EACH ROW
BEGIN
DECLARE changed INTEGER;
SET changed = 0;
IF STRCMP(NEW.first_name, OLD.first_name) <> 0 THEN
SET changed = 1;
ELSEIF STRCMP(NEW.last_name, OLD.last_name) <> 0 THEN
SET changed = 1;
ELSEIF STRCMP(NEW.maiden_name, OLD.maiden_name) <> 0 THEN
SET changed = 1;
ELSEIF STRCMP(NEW.suffix, OLD.suffix) <> 0 THEN
SET changed = 1;
ELSEIF STRCMP(NEW.title, OLD.title) <> 0 THEN
SET changed = 1;
ELSEIF STRCMP(NEW.gender, OLD.gender) <> 0 THEN
SET changed = 1;
ELSEIF STRCMP(NEW.street, OLD.street) <> 0 THEN
SET changed = 1;
ELSEIF STRCMP(NEW.street2, OLD.street2) <> 0 THEN
SET changed = 1;
ELSEIF STRCMP(NEW.city, OLD.city) <> 0 THEN
SET changed = 1;
ELSEIF STRCMP(NEW.state, OLD.state) <> 0 THEN
SET changed = 1;
ELSEIF STRCMP(NEW.zip, OLD.zip) <> 0 THEN
SET changed = 1;
ELSEIF STRCMP(NEW.home_phone, OLD.home_phone) <> 0 THEN
SET changed = 1;
ELSEIF NEW.date_of_birth <> OLD.date_of_birth THEN
SET changed = 1;
END IF;
IF changed > 0 THEN
update other_table set updated = CURRENT_TIMESTAMP where id = NEW.id;
END IF;
END;
|
解决方案
That's how ELSEIF works inherently. Once MySQL finds any condition that passes (i.e., evaluates to TRUE) it will not evaluate any further conditions in the IF..ELSEIF..ELSE chain.
1504
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
