CODEWITHSUNDEEP
mysqltriggers
TurmDrummer
TurmDrummer

Reputation: 160

MySQL: INSERT INTO ... ON DUPLICATE KEY UPDATE is not triggering a AFTER UPDATE Trigger

I have a confusing Problem. I am using 5.6.12 Community Version.

I have an AFTER UPDATE Trigger, that is working correctly, when the table is called with for example

UPDATE test_table SET TestVariable = 2 WHERE TestIndex = 2;

But when I use

INSERT INTO test_table (TestIndex,TestVariable) VALUES (2,3) ON DUPLICATE KEY UPDATE TestVariable = 3;

the UPDATE-Trigger is not "triggered".

This is of course a heavily simplified case and has nothing to do with my current code. I have not found much information about this. In my opinion the UPDATE on a DUPLICTE KEY is internaly a normal UPDATE and should call my trigger.

Has anyone made similar observations, or can explain to me, why MySQL is behaving that way, or assure me, that I must have some error in my setup, because it should work?

I do NOT need a answer suggesting, that I should NOT use INSERT INTO ... ON DUPLICTE KEY ..., because a) thats not the question and b) I can not easily modify the business application i currently work on.

Thank you so much for your Help

Upvotes: 1

Views: 171

Answers (1)

TurmDrummer
TurmDrummer

Reputation: 160

I have tested this on the smalles possible scale and came to a conclusion.

  1. The Update trigger works, like you would expect it. It gets called by the ON DUPLICATE UPDATE. I could reproduce this by calling the queries from console.

  2. The Update trigger is not called (as I reported) when I execute the query from my .net code, using the Oracle .net connector! I have installed the current version 6.9.8.0. To this moment I do not know, if this is a bug in the current version, or if this is a generall issue. I will investigate this further, but for this case, my question is answered.

The issue is for clerification not a MySQL problem, but a connector problem.

Thank you for your help, People.

Upvotes: 1

Related Questions