title: ON DUPLICATE KEY UPDATE doesn't work when there's an UPDATE trigger
body: <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>