Create insert trigger to auto increment int field of composite PK (String, int), restart numbering at 1 for new Strings

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.

The two PK fields are batch and lineItem. If a record is deleted I want the numbering to start from the largest integer for batch. Not fill in the holes.

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.

Edit: I want something like the following insert statement as a trigger

INSERT INTO payroll(`batch`,`lineItem`)
(select 'T105',t1.lineItem + 1 from payroll as t1 where batch = 'T105' order by lineItem desc limit 1);

But where 'T105' (the batch id) is hard coded I want the trigger to pick that up from the insert.

So I want to be able to say something like:

INSERT INTO payroll(`batch`)VALUES('T001','T001','T001', 'T002', 'T002', 'T002');

and I would expect to see in the table:

batch    lineItem
T001     1
T001     2
T001     3
T002     1
T002     2
T002     3

Getting further:

In trying to implement this I've come up with:

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$$

However when I try...

INSERT INTO `co05_test`.`my_table`(`batch`)VALUES('T001');

I get this error: Column 'lineItem' cannot be null

Which is defined as not being nullable but I though the trigger should set the value!

Solution which I used:

-- 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 > 0 then
       set NEW.lineItem = @newLineItem;
     else
       set NEW.lineItem = 1;
     end if;
END;
$$

Have you tried declaring the variable instead?

DELIMITER $$
CREATE TRIGGER `co05_test`.`ins_lineItem`
BEFORE INSERT ON `co05_test`.`my_table`
FOR EACH ROW
BEGIN
DECLARE newLineItem INT; 
SELECT 
    lineItem + 1 into newLineItem 
FROM my_table 
WHERE batch = NEW.batch
ORDER BY lineItem DESC 
LIMIT 1;

SET NEW.lineItem = newLineItem;   
END$$