How do I exit a series of If / else conditions in a mysql trigger?

Question

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

Answer 1

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.

Answer 2

What about one monster OR-ing? it would mean only one if-statement ;)

IF STRCMP(NEW.first_name, OLD.first_name) <> 0 or
   STRCMP(NEW.last_name, OLD.last_name) <> 0 or
   STRCMP(NEW.maiden_name, OLD.maiden_name) <> 0 or ...
   ...
   SET changed = 1;
end if;

IF changed > 0 THEN
    update other_table set updated = CURRENT_TIMESTAMP where id = NEW.id;
END IF;

...but seriously, your code is fine. Nothing to worry about. VoteyD was right.

The only other way to clean this up would be to remove some of these conditions - though it's probably not an option.

Answer 3

Try a CASE statement ...

SET changed = CASE
   WHEN STRCMP(NEW.first_name, OLD.first_name) <> 0 THEN 1
   WHEN STRCMP(NEW.last_name, OLD.last_name) <> 0  THEN 1
   WHEN STRCMP(NEW.maiden_name, OLD.maiden_name) <> 0  THEN 1
   ...
   ELSE 0
END

Answer 4

what about informix trigger. we can use if else condition in informix trigger.

[email protected]