title: MySql trigger not executed when new rows come in over JDBC connection?
body: <p>I'm trying to track revisions to data over time.
  I've decided to use the 'id, revision' pattern.
  Note that neither key field is an AUTO_INCREMENT - I'm using a BEFORE INSERT trigger to calculate
  what id and revision the new row should get. Yes I could handle all this on the application side
  but choose to do it on the db side.</p>
  
  <pre><code>DROP TABLE IF EXISTS person;
  CREATE TABLE person (
    id BIGINT(12) UNSIGNED NOT NULL,
    revision BIGINT(12) UNSIGNED NOT NULL,
    last_name varchar(30) NOT NULL,
    first_name varchar(30) NOT NULL,
    PRIMARY KEY ( id, revision)
  );
  
  DROP TABLE IF EXISTS person_debug;
  CREATE TABLE person_debug (
    id BIGINT(12) UNSIGNED NOT NULL,
    revision BIGINT(12) UNSIGNED NOT NULL,
    message varchar(40),
    PRIMARY KEY ( id, revision)
  );
  
  DELIMITER $$
  CREATE TRIGGER person_insert BEFORE INSERT ON person
  FOR EACH ROW
  BEGIN
  DECLARE new_id BIGINT;
  DECLARE new_revision BIGINT;
  DECLARE row_cnt BIGINT;
  IF( NEW.id IS NULL) THEN
    SET new_id = 1;
    SET new_revision = 0;
    SET row_cnt = (SELECT TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_NAME = 'person' AND TABLE_TYPE = 'BASE TABLE');
    IF( row_cnt &gt; 0 ) THEN
      SET new_id = (SELECT MAX( id) + 1 FROM person);
    END IF;
  ELSE
    SET new_id = NEW.id;
    SET new_revision = (SELECT MAX( revision) + 1 FROM person WHERE id = new_id);
    INSERT INTO person_debug VALUES ( new_id, new_revision, "tried an update");
  END IF;
  SET NEW.id = new_id;
  SET NEW.revision = new_revision;
  END;
  $$
  DELIMITER ;
  
  INSERT INTO person ( id, last_name, first_name) VALUES
    ( null, 'Hamilton', 'Mike'),
    ( null, 'Jefferson', 'Thomas'),
    ( null, 'Nixon', 'Richard'),
    ( null, 'Ford', 'Gerald'),
    ( null, 'Reagan', 'Ronald'),
    ( null, 'Lincoln', 'Abraham')
    ( null, 'Roosevelt', 'Teddy')
    ( null, 'Hayden', 'Rutherford')
  ;
  
  INSERT INTO person ( id, last_name, first_name) VALUES
    (    1, 'Hamilton', 'Alexis'),
    (    8, 'Hays', 'Rutherford')
    (    1, 'Hamilton', 'Alexander')
  ;
  </code></pre>
  
  <p>this all works perfectly when I run the run the script manually from within an MySql session</p>
  
  <pre><code>source create_tables.sql
  
  select * from person;
  
    id| revision| last_name  | first_name
  ====================================
     1|        0| 'Hamilton' | 'Mike'
     1|        1| 'Hamilton' | 'Alexis'
     1|        2| 'Hamilton' | 'Alexander'
     2|        0| 'Jefferson'| 'Thomas'
     3|        0| 'Nixon'    | 'Richard'
     4|        0| 'Ford'     | 'Gerald'
     5|        0| 'Reagan'   | 'Sylvester'
     6|        0| 'Lincoln'  | 'Abraham'
     7|        0| 'Roosevelt'| 'Teddy'
     8|        0| 'Hayden'   | 'Rutherford'
     8|        1| 'Hays'     | 'Rutherford'
  
  select * from person_debug;
  
    id| revision| message
  ====================================
     1|        1| 'tried an update'
     1|        2| 'tried an update'
     8|        1| 'tried an update'
  </code></pre>
  
  <p>Fantastic. All parts of the trigger are being executed and they all work exactly as expected.</p>
  
  <p>Now I have a Java/SpringData/MVC/MySql webapp built up in which a page contains a table containing
  the contents of person as rows and you can select a row, choose to update the row, which populates a popup form
  which you edit then submit, which then goes back to a controller which instantiates a new Person entity and sends
  it along to the database.</p>
  
  <p>The problem is when I then try to update a person the ORIGINAL row gets UPDATED and no new row gets inserted.
  Example, submitting the update form with
         5|        0| 'Reagan'   | 'Ronald'</p>
  
  <p>should result in </p>
  
  <pre><code>select * from person;
  
    id| revision| last_name  | first_name
  ====================================
     1|        0| 'Hamilton' | 'Mike'
     1|        1| 'Hamilton' | 'Alexis'
     1|        2| 'Hamilton' | 'Alexander'
     2|        0| 'Jefferson'| 'Thomas'
     3|        0| 'Nixon'    | 'Richard'
     4|        0| 'Ford'     | 'Gerald'
     5|        0| 'Reagan'   | 'Sylvester'
     5|        1| 'Reagan'   | 'Ronald'
     6|        0| 'Lincoln'  | 'Abraham'
     7|        0| 'Roosevelt'| 'Teddy'
     8|        0| 'Hayden'   | 'Rutherford'
     8|        1| 'Hays'     | 'Rutherford'
  
  select * from person_debug;
  
    id| revision| message
  ====================================
     1|        1| 'tried an update'
     1|        2| 'tried an update'
     5|        1| 'tried an update'
     8|        1| 'tried an update'
  </code></pre>
  
  <p>but instead I get</p>
  
  <pre><code>select * from person;
  
    id| revision| last_name  | first_name
  ====================================
     1|        0| 'Hamilton' | 'Mike'
     1|        1| 'Hamilton' | 'Alexis'
     1|        2| 'Hamilton' | 'Alexander'
     2|        0| 'Jefferson'| 'Thomas'
     3|        0| 'Nixon'    | 'Richard'
     4|        0| 'Ford'     | 'Gerald'
     5|        0| 'Reagan'   | 'Ronald'
     6|        0| 'Lincoln'  | 'Abraham'
     7|        0| 'Roosevelt'| 'Teddy'
     8|        0| 'Hayden'   | 'Rutherford'
     8|        1| 'Hays'     | 'Rutherford'
  
  select * from person_debug;
  
    id| revision| message
  ====================================
     1|        1| 'tried an update'
     1|        2| 'tried an update'
     8|        1| 'tried an update'
  </code></pre>
  
  <p>it's as if the trigger isn't executed when a row is to be added
  when the row comes in over the JDBC connection from the webapp.</p>
  
  <p>Any ideas?</p>
  
  <p>TIA,</p>
  
  <p>code_warrior</p>