TRIGGERs using AFTER UPDATE are not updated
I am using Triggers to tell Windows processes that data in a specific table and record has changed and they should reread it.
I have created a number of Triggers for various tables. One of the triggers (sanitized and simplified names and variables) is as follows
CREATE TRIGGER MyTableName_Update
AFTER UPDATE
ON MyTableName
FOR EACH ROW
BEGIN
SELECT BroadcastChange(MyTableName, 1) into @result;
END;
MyTableName is defined as follows:
CREATE TABLE MyTableName (
ID INTEGER UNSIGNED NOT NULL DEFAULT 1,
MyData INTEGER NOT NULL,
PRIMARY KEY(ID)
);
BroadcastChange is a MySQL Function defined as follows:
CREATE FUNCTION BroadcastChange RETURNS INTEGER SONAME 'AT_MySQL_UDF_BroadcastChange.dll';
The 64-bit Windows DLL 'AT_MySQL_UDF_BroadcastChange.dll sends a message to all processes registered to receive notice of a change in the TABLE MyTableName, specifically in this case a change in table MyTableName, record ID =1.
Once receiving the message, each registered process is supposed to reread the MyTableName record with ID = 1 in this case so that they are using the latest data as in
SELECT ID,MyData FROM MyTableName WHERE ID = 1;
All this works fine from a communication point of view. The table record get change, the Trigger occurs, the DLL executes and tells each process to reread the record with ID=1 from table MyTableName. The problem is that when the processes read the record, it usually still has the original values in any columns that were changed.
For example, there is a single record in the table
+----+--------+
| ID | MyData |
+----+--------+
| 1 | 10 |
+----+--------+
If the MyData column is changed to 11 for Record with ID=1, when the processes read it, MyData is usually still equal to the previous value, 10 in this case.
If I put a small delay between the time the process gets the notice and by the time it reads the record, then the data is 11. THe delay needed isn't much (1 second is plenty) but why does it need any delay. It seems that the data is not committed.
I would have expected that data would be committed since the Trigger uses AFTER UPDATE.
I am using INNODB and
Server version: 5.6.24-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Is this a bug or a feature? The only thing I can think of is that the Trigger is a transaction and doesn't commit until completely executed. IF that is the case, then maybe "AFTER UPDATE" is not possible with INNODB and is only possible with MyISAM.
If I turn on MySQL logging I see
300 Query UPDATE MyTableName SET MyData = 11 WHERE ID = 1
300 Query SELECT BroadcastChange('MyTableName','1') into @result
301 Query SELECT * FROM MyTableName WHERE ID = 1
So the read is definitely after the UPDATE.
Thanks for any help or advice.