title: Create insert trigger to auto increment int field of composite PK (String, int), restart numbering at 1 for new Strings
body: <p>I've read that this can be done without issue using MyISAM as it is the default behavior , but I'm using InnoDB so need a trigger for such.</p>
  
  <p>The two PK fields are <em>batch</em> and <em>lineItem</em>.  If a record is deleted I want the numbering to start from the largest integer for <em>batch</em>. Not fill in the holes. </p>
  
  <p>This is to set up a testing environment for a legacy system. So the schema is the way it is, I thought I'd mention that to avoid any discussion about whether it is good or not.</p>
  
  <p><strong>Edit</strong>: I want something like the following insert statement as a trigger</p>
  
  <pre><code>INSERT INTO payroll(`batch`,`lineItem`)
  (select 'T105',t1.lineItem + 1 from payroll as t1 where batch = 'T105' order by lineItem desc limit 1);
  </code></pre>
  
  <p>But where 'T105' (the batch id) is hard coded I want the trigger to pick that up from the insert.</p>
  
  <p>So I want to be able to say something like:</p>
  
  <pre><code>INSERT INTO payroll(`batch`)VALUES('T001','T001','T001', 'T002', 'T002', 'T002');
  </code></pre>
  
  <p>and I would expect to see in the table:</p>
  
  <pre><code>batch    lineItem
  T001     1
  T001     2
  T001     3
  T002     1
  T002     2
  T002     3
  </code></pre>
  
  <p><strong>Getting further</strong>: </p>
  
  <p>In trying to implement this I've come up with:</p>
  
  <pre><code>DELIMITER $$
  CREATE TRIGGER `co05_test`.`ins_lineItem`
  BEFORE INSERT ON `co05_test`.`my_table`
  FOR EACH ROW
  BEGIN
  
       select lineItem + 1 into @newLineItem from my_table where batch = NEW.batch order by lineItem desc limit 1;
       set NEW.lineItem = @newLineItem;   
  END$$
  </code></pre>
  
  <p>However when I try...</p>
  
  <pre><code>INSERT INTO `co05_test`.`my_table`(`batch`)VALUES('T001');
  </code></pre>
  
  <p>I get this error: Column 'lineItem' cannot be null</p>
  
  <p>Which is defined as not being nullable but I though the trigger should set the value!</p>
  
  <p><strong>Solution which I used</strong>:</p>
  
  <pre><code>-- Trigger DDL Statements
  DELIMITER $$
  
  USE `co05_test`$$
  
  CREATE TRIGGER `co05_test`.`ins_lineItem`
  BEFORE INSERT ON `co05_test`.`my_table`
  FOR EACH ROW
  BEGIN
       select count(*) into @batchCount from my_table where batch = NEW.batch;
       select lineItem + 1 into @newLineItem from my_table where batch = NEW.batch order by lineItem desc limit 1;
      if @batchCount &gt; 0 then
         set NEW.lineItem = @newLineItem;
       else
         set NEW.lineItem = 1;
       end if;
  END;
  $$
  </code></pre>