Hi,

I want to create incrementing voucher numbers in mysql table as I insert voucher entries.

Table name is le
Column name is vouno (mediumint,not null)

There is another column which is auto incrementing, so I can not use auto increment for column "vouno".

As I do not want to lock the table, I tried to use a "before insert" trigger like this.

delimiter |
CREATE TRIGGER addvouno BEFORE INSERT ON le
FOR EACH ROW BEGIN
DECLARE NewINT MEDIUMINT;
SELECT MAX(vouno)+1 INTO NewINT from le;
if @newint is null then
set @newint=1;
end if;
SET NEW.vouno=@newint;
END;
|
delimiter ;



The first insert gives the value of 1 to vouno.
Strangely, even for all next inserts, value of vouno is inserted as 1 (instead of 2,3 etc.)

The variable newint is always returning value of 1. This means the statement,

"SELECT MAX(vouno)+1 INTO NewINT from le" is always returning null value.

What is wrong in above code?

Vivek