StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Your <code>INSERT ... ON DUPLICATE KEY UPDATE</code> appears to be a way of preventing an error if <code>officer_number</code> already exists. Do you need the update to happen (to fire the TRIGGER), or could you instead use <code>INSERT IGNORE</code>?:</p> <pre><code>INSERT IGNORE INTO officer (officer_number, name, bank_id) VALUES ('', '', 8); </code></pre> <p>That would simply do nothing if <code>officer_id</code> already exists, thus removing the need for the update (and therefore <code>LAST_INSERT_ID()</code>) altogether.</p> <p>If that is not possible, then perhaps your <code>INSERT ... ON DUPLICATE KEY UPDATE</code> could be tweaked. I'm not clear on the purpose of:</p> <pre><code>id = LAST_INSERT_ID(id) </code></pre> <p><code>LAST_INSERT_ID()</code> (without any arguments), returns the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column.</p> <p>However, if you supply an argument, it returns the value of that argument, and the next call to <code>LAST_INSERT_ID()</code> (without any arguments), returns the same value. For example:</p> <pre><code>SELECT LAST_INSERT_ID(100); +---------------------+ | LAST_INSERT_ID(100) | +---------------------+ | 100 | +---------------------+ SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 100 | +------------------+ </code></pre> <p>So, if we assume that <code>id == 100</code>, then this should be true:</p> <pre><code>SELECT LAST_INSERT_ID(id); +--------------------+ | LAST_INSERT_ID(id) | +--------------------+ | 100 | +--------------------+ SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 100 | +------------------+ </code></pre> <p>Following on from that:</p> <pre><code>id = LAST_INSERT_ID(id) </code></pre> <p>Should be the same as:</p> <pre><code>id = id </code></pre> <p>Or, as suggested by Josh Davis, it shouldn't be necessary at all. Have you tried simply <code>id = id</code>? What exactly happens when you exclude it?</p> <p>The <a href="http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id" rel="nofollow">manual</a> states that:</p> <blockquote> <p>However, if you mix references to LAST_INSERT_ID() and LAST_INSERT_ID(expr), the effect is undefined</p> </blockquote> <p>and:</p> <blockquote> <p>The ID that was generated is maintained in the server on a <em>per-connection</em> basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column <em>by that client</em>. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own.</p> </blockquote> <p>As you are using both <code>LAST_INSERT_ID()</code> and <code>LAST_INSERT_ID(expr)</code>, the behaviour is undefined. Furthermore, the TRIGGER may be regarded as being one connection (it's run directly on the server), whereas the INSERT and CREATE statements are possibly called from a different connection. Given this, and the various changes and bugs that have been reported associated with LAST_INSERT_ID between versions, it's likely that there <em>will</em> be problems with your approach.</p> <p>Going back to what Josh Davis said, I'd be inclined to resolve the use of <code>id = LAST_INSERT_ID(id)</code> in your INSERT statement. It would also be helpful to know how you derive the <code>officer_id</code> in your <code>INSERT INTO account</code> statement - the one which is receiving a zero value.</p>

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