MySQL Forums
Forum List  »  InnoDB

TRIGGERs using AFTER UPDATE are not updated
Posted by: Roger Visser
Date: September 09, 2016 07:21PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
TRIGGERs using AFTER UPDATE are not updated
932
September 09, 2016 07:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.