On one of our application's databases, we have some triggers in place for row updates and deletes. These triggers fire off stored procedures that take care of making data snapshots for auditing purposes before changes are made. This week, we discovered that those triggers were not always firing, resulting in some gaps in the record. Obviously, this is not the desired behavior.
So first, of course, I double checked the trigger code to be sure it had the modified field in there. Here is a simplified version of the trigger code (for this post, I'll presume you guys know how to the create aspects and just focus on the main code):
IF OLD.totalamount <> NEW.totalamount
OR OLD.paymentmethod <> NEW.paymentmethod
OR OLD.paymentcode <> NEW.paymentcode
OR OLD.receiptnumber <> NEW.receiptnumber
OR OLD.updatedby <> NEW.updatedby
OR OLD.changereason <> NEW.changereason
OR OLD.systemnotes <> NEW.systemnotes
OR OLD.deleted_date <> NEW.deleted_date
OR OLD.deleted_reason <> NEW.deleted_reason THEN
/* Call our stored precedure to do the archiving */
Yep, our modified field is there and the code looked right. So first I replicated the action that should have fired the trigger but didn't and confirmed that the trigger itself was being called (good) but that the statement that should fire the stored procedure was not. After another hour or so of additional tests, googling, and go checks, I finally narrowed it down to only occurring when one of the fields that should be activating our stored procedure changes from NULL to a value or vice versa.
So then the question was: well why? The search results I found on Google only half hinted around at it – it was my partner who suddenly said "let's try something" and had me run three queries against the DB.
Well, now we knew why the stored procedures weren't firing – as far as MySQL was concerned comparing anything to NULL gives a NULL result instead of a 1 (i.e. yes, they are not equal) result. After another quick search, I found a solution to fixing our stored procedure – using COALESCE to take NULL out of the equation.
IF COALESCE(OLD.totalamount, '-9000') <> COALESCE(NEW.totalamount, '-9000')
OR COALESCE(OLD.paymentmethod, '-9000') <> COALESCE(NEW.paymentmethod, '-9000')
OR COALESCE(OLD.paymentcode, '-9000') <> COALESCE(NEW.paymentcode, '-9000')
OR COALESCE(OLD.receiptnumber, '-9000') <> COALESCE(NEW.receiptnumber, '-9000')
OR COALESCE(OLD.updatedby, '-9000') <> COALESCE(NEW.updatedby, '-9000')
OR COALESCE(OLD.changereason, '-9000') <> COALESCE(NEW.changereason, '-9000')
OR COALESCE(OLD.systemnotes, '-9000') <> COALESCE(NEW.systemnotes, '-9000')
OR COALESCE(OLD.deleted_date, '-9000') <> COALESCE(NEW.deleted_date, '-9000')
OR COALESCE(OLD.deleted_reason, '-9000') <> COALESCE(NEW.deleted_reason, '-9000') THEN
It's kind of uglier now, but it works – anything that is NULL is replaced with a value of -9000 allowing the comparison to work. What can I say I am a geek after all? 😛 You can use anything else in place of it, the main thing is that it is a value none of the fields will ever possibly have.
What about the NULL safe equals operator, that ridiculous <=> (seriously, the NULL safe equal is equal wrapped in not equal??)? If we were wanted to know the values were equal, this would work fine and certainly be cleaner than COALESCE, but since we needed not equals, it wasn't working nor did some of the various hacks I found suggestion ways to use it for a NULL safe not equal check.
I'm sure there are other options out there, but this one worked for us and without any extra noticeable performance degradation.