MySQL Update Value

Question

I am trying to create a Trigger that will run through some IF ELSEIF and check for a new NULL value, but it only applies to the first IF expression.

This Trigger is AFTER UPDATE . My question is that I only SET one column value, what others SET to, whether they are NULL or what. How can I check this column if it is not SET in this UPDATE command.

Update example:

 UPDATE `stations_us`.`current_prices` SET `midPrice` = '3.59' WHERE `current_prices`.`_id` =1; 

There are other columns that are not currently updated, but can be updated based on PHP Script.

Trigger:

 BEGIN -- Definition start IF(NEW.regPrice IS NOT NULL) THEN INSERT INTO prices (stationID, price, type, prevPrice, prevDate, dateCreated, apiKey, uid) VALUES(NEW.stationID, NEW.regPrice, 'reg', OLD.regPrice, OLD.regDate, NEW.regDate, NEW.lastApiUsed, NEW.lastUpdatedBy); ELSEIF(NEW.midPrice IS NOT NULL) THEN INSERT INTO prices (stationID, price, type, prevPrice, prevDate, dateCreated, apiKey, uid) VALUES(NEW.stationID, NEW.midPrice, 'mid', OLD.midPrice, OLD.midDate, NEW.midDate, NEW.lastApiUsed, NEW.lastUpdatedBy); ELSEIF(NEW.prePrice IS NOT NULL) THEN INSERT INTO prices (stationID, price, type, prevPrice, prevDate, dateCreated, apiKey, uid) VALUES(NEW.stationID, NEW.prePrice, 'pre', OLD.prePrice, OLD.preDate, NEW.preDate, NEW.lastApiUsed, NEW.lastUpdatedBy); END IF; -- Definition end END 

Answer 1

You cannot specify inside the trigger the columns in the SET clause were pointed to.

You can check if the col value has been changed by the operator by comparing the values ​​of NEW.col and OLD.col .

 IF NOT (NEW.regPrice <=> OLD.regPrice) 

Testing (NEW.col IS NOT NULL) not enough to determine if a value has been changed. Consider the case where the old value is, for example, 4.95, and the new value is NULL, it looks like you probably want to detect this change.

---

Note that an UPDATE statement can set values ​​for more than one column:

 UPDATE foo SET col1 = 'a', col2 = 'b', col3 = 'c' WHERE ... 

Also note that a column can be set to NULL, and a column can be set to its existing value.

 UPDATE foo SET col1 = 'a', col2 = NULL, col3 = col3 WHERE ... 

In this case, inside your trigger, NEW.col2 will evaluate to NULL, and (NEW.col3 <=> OLD.col3) will evaluate to 1 (TRUE), since the values ​​of OLD and NEW are equal.

(What your AFTER UPDATE trigger actually sees is the value that was saved, which is not necessarily the value from your UPDATE statement if the BEFORE INSERT trigger reset the value and provided a different value for this column.)

If you intend to register changes in values, you probably DO NOT want ELSIF, but instead want to skip all the columns to see if that value has been changed or not.