StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POON DUPLICATE KEY UPDATE doesn't work when there's an UPDATE trigger
text
Body
copied!<p>I have an <code>INSERT</code> statement that looks like this:</p> <pre><code>INSERT INTO officer (officer_number, name, bank_id) VALUES ('', '', 8) ON DUPLICATE KEY UPDATE officer_number = '', name = '', bank_id = 8, id = LAST_INSERT_ID(id) </code></pre> <p>This way of doing it has been working just fine. It stopped working when I added the following trigger:</p> <pre><code>CREATE TRIGGER officer_update BEFORE UPDATE ON `officer` FOR EACH ROW SET NEW.updated_at = NOW(), NEW.created_at = OLD.created_at </code></pre> <p>It's not that the <code>officer</code> record isn't getting inserted. It just seems that the trigger is hijacking <code>LAST_INSERT_ID()</code> or something. I say this because the next query that's executed is this:</p> <pre><code>INSERT INTO account (import_id, branch_id, account_number, officer_id, customer_id, open_date, maturity_date, interest_rate, balance, opening_balance) VALUES ('123', '4567', '789', '0', # This is the officer id which is of course invalid '321', '1992-04-22', '2012-05-22', '0.0123', '0', '10000') </code></pre> <p>Since I've run dozens of successful imports with the same exact file, I haven't changed my code, and now my imports aren't working after I added this trigger, I must deduce that the trigger is the culprit. I had a similar situation with another table and removing the trigger fix the problem.</p> <p>So my questions are:</p> <ol> <li><strong>Can someone explain what, specifically, is causing my officer id to get set to 0?</strong></li> <li><strong>What's a good solution to this problem?</strong></li> </ol> <p>I have another trigger on <code>officer.created_at</code> (and a lot of other tables' <code>created_at</code>s) and I would prefer to avoid some sort of awkward solution where I have a trigger on <code>created_at</code> but a <code>DEFAULT CURRENT_TIMESTAMP</code> on <code>updated_at</code>. For some reason, MySQL only allows one auto-timestamp per table, so I can't do <code>CURRENT_TIMESTAMP</code> for both <code>created_at</code> and <code>updated_at</code>.</p> <p>Here is the <code>SHOW CREATE TABLE</code> for <code>officer</code>:</p> <pre><code>CREATE TABLE `officer` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `officer_number` varchar(255) NOT NULL, `name` varchar(255) NOT NULL, `bank_id` bigint(20) NOT NULL, `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), UNIQUE KEY `officer_number` (`officer_number`,`name`), UNIQUE KEY `officer_number_2` (`officer_number`,`bank_id`), KEY `bank_id` (`bank_id`), CONSTRAINT `officer_ibfk_1` FOREIGN KEY (`bank_id`) REFERENCES `bank` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=102735 DEFAULT CHARSET=latin1 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload