87605

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.

Answer1:

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.

Answer2:

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

Answer3:

Use like this:

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

Recommend

  • Muliple upload php script and store in mysql database
  • Get highest value from a file using mSL and mIRC
  • SQL complicated recursive CTE
  • Table field that holds row count from another table
  • How can I configure a Tkinter widget from a separate class?
  • iOS 9 custom transition - animationControllerForDismissedController not called
  • jQuery Mobile - Dialogs without changing hash
  • How to create a custom Powershell operator?
  • Thread synchronization with syncwarp
  • Is there any way to call saveCurrentTurnWithMatchData without sending a push notification?
  • .NET video play library which allows to change the playback rate?
  • Julia: How to give multiple workers access to functions that are 'include(…)' into a modul
  • How can I run DataNucleus Bytecode Enhancer from SBT?
  • Retrieving specified columns from a list of csv files to create a data data frame in R
  • How to 'create temp table as select' in Slick?
  • Jquery Knockout: ko.computed() vs classic function?
  • Bootstrap (v3.3.4) glyphicons not displayed in IE when refresh page (F5)
  • Tell Git to stop prompting me for conflicts when none really exist?
  • Implementing “partial void” in VB
  • Sending HTML Form Data to Spring REST Web Service
  • How to disable all widgets inside Panel or inside Composite?
  • MySQL Order by column = x, column asc?
  • SharedPreferences or SQLite Database?
  • Initializer list vs. initialization method
  • Control modification in presentation layer
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Fetching methods from BroadcastReceiver to update UI
  • swift auto completion not working in Xcode6-Beta
  • ActionScript 2 vs ActionScript 3 performance
  • what is the difference between the asp.net mvc application and asp.net web application
  • Upload files with Ajax and Jquery
  • Weird JavaScript statement, what does it mean?
  • Do I've to free mysql result after storing it?
  • Jquery - Jquery Wysiwyg return html as a string
  • A cron job substitute?
  • json Serialization in asp
  • Hits per day in Google Big Query
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Linking SubReports Without LinkChild/LinkMaster
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass