I read that this can be done without problems using MyISAM, as this is the default behavior, but I use InnoDB, so I need a trigger for this.
Two PK fields are batch and lineItem. If the record is deleted, I want the numbering to begin with the largest integer for the batch. Do not fill holes.
This is a test environment setup for an outdated system. So, the scheme is the way it is, I thought I mentioned it so as not to discuss whether it is good or not.
Change 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" (batch identifier) is hard-coded, I want the trigger to select this from the insert.
So, I want to say something like:
INSERT INTO payroll(`batch`)VALUES('T001','T001','T001', 'T002', 'T002', 'T002');
and I expect to see in the table:
batch lineItem T001 1 T001 2 T001 3 T002 1 T002 2 T002 3
Next :
When trying to implement this, I came 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
Defined as invalid, but although the trigger must set the value!
The solution 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; $$