Multiple if statements in MySQL trigger

Question

If you have multiple if statements within a MySQL trigger, what is the best approach?

Currently my SQL looks like this:

IF NOT (NEW.status <=> OLD.status) THEN
  {my sql}

ELSEIF NOT (NEW.actual <=> OLD.actual) THEN
  {my sql}
END IF

On first sight this seems to work. However, I noticed that when multiple of the (else)if statements are true, only the first one gets executed (just like in PHP, for example).

How can I just use multiple if's, not necessarily elseif's, so that more than 1 statement is executed? Creating multiple triggers with the same action is not possible, at least that is what phpMyAdmin shows. Putting multiple if's in one trigger results in errors.

edwardmp · Accepted Answer · 2013-02-14 10:36:13Z

7

Figured it out. Instead of using the visual GUI in phpMyAdmin for creating triggers, I used plain SQL.

So I used this SQL:

delimiter //
create t1
after update
on my_table
for each row
    begin
        IF (NEW.status <> OLD.status) THEN
            {your sql}
        END IF;

        IF (NEW.actual <> OLD.actual) THEN
            {your sql}
        END IF;     
    end;//
delimiter ;

Which works fine. When lookup the trigger up in the phpMyAdmin GUI, I noticed it is because of the begin and end; which have to be added.

answered Feb 14, 2013 at 10:36

edwardmp

6,6316 gold badges53 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Muhammad Ali Over a year ago

I noticed it is because of the begin and end; which have to be added.....That's the real point

jophab · Accepted Answer · 2016-12-04 11:01:01Z

0

if(NEW.shift = 0 )then

SELECT `max_morning` , `count_morning` into @max_morning , @count_morning FROM `count_reserve` where `date` = NEW.date_reserve;
if(@count_morning is NULL and @max_morning is NULL) then

  select `max_morning` , `max_evening` into @max_morning , @max_evening   from `work_time` where `day` = new.day;

    insert into `count_reserve` (`date`, `count_morning` ,      `count_evening`,`from_morning`, `to_morning`, `max_morning`, `from_evening`, `to_evening`, `max_evening` , `status_morning` , `status_evening`) values (NEW.date_reserve , 1 , 0 , NULL, NULL, @max_morning , NULL, NULL, @max_evening ,1, 1);


end if
end if

edited Dec 4, 2016 at 11:01

jophab

5,54714 gold badges45 silver badges61 bronze badges

answered Dec 4, 2016 at 9:30

user7247599

1

Comments

user2001117 · Accepted Answer · 2013-02-13 07:22:20Z

-1

Use like this:

 IF (NEW.status <> OLD.status) THEN
    <statement>
 ELSEIF (NEW.actual <> OLD.actual) THEN
     <statement>
 END IF;

answered Feb 13, 2013 at 7:22

user2001117

3,7871 gold badge20 silver badges18 bronze badges

1 Comment

edwardmp Over a year ago

That produces the same? When the first IF is true, the elseif isn't executed.

Collectives™ on Stack Overflow

Multiple if statements in MySQL trigger

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related