MySQL Trigger/Procedure Not Working -

MySQL Trigger/Procedure Not Working -

i have trigger , procedure should generate invoice number.

examples of numbers (format: type/number/month/year):

vat/5/05/2011 vat/6/05/2011 vat/7/06/2011

when inserting invoice year = year(curdate()) should increment number integer.

but, when inserting invoice new year's day (on 1st of jan) should generate new invoice number, , part of scheme not working.

procedure:

create procedure generate_vat_number(out invoicenumber varchar(50)) begin select concat( split_str(number, '/', 1), '/', count(number) + 1, '/', split_str(number, '/', 3), '/', year(curdate()) ) invoicenumber invoices split_str(number, '/', 4) = year(curdate()) limit 1; end$$

trigger:

create trigger add_invoice_number before insert on invoices each row begin phone call generate_vat_number(@out); if @out = null set new.number = concat(split_str(new.number, '/', 1), '/', '1', '/', split_str(new.number, '/', 3), '/', year(curdate())); else set new.number = @out; end if; end$$

function used split strings:

create function split_str( x varchar(255), delim varchar(12), pos int ) returns varchar(255) homecoming replace(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos -1)) + 1), delim, '');

when seek create new invoice error saying number field cannot null, true. i'm changing operating scheme year simulate year changes. according this, presume trigger not working correctly. however, have no thought why.

i can see 1 issue in trigger, seek using @out null instead of @out = null.

corrected trigger:

create trigger add_invoice_number before insert on invoices each row begin phone call generate_vat_number(@out); if @out null set new.number = concat(split_str(new.number, '/', 1), '/', '1', '/', split_str(new.number, '/', 3), '/', year(curdate())); else set new.number = @out; end if; end$$

let know if works you.

mysql