MySQL – How to Reset Value Each Year with a Trigger

MySQLtrigger

I want to set up a trigger which will increment a value (based on previous record). But this will also reset the value if the year of date_creation is different of the current year.

ref        ref_handlerdate_creation           
x_0001 0001          2016-06-16 00:00:00
y_0002 0002          2016-06-17 00:00:00
z_0001 0001          2017-01-01 00:00:00

PS : I also have an id field which is the primary key and increment itself

I already wrote that

BEGIN

    DECLARE next_handler integer;
    DECLARE current_id integer;

    -- select the current highest id 
    SET @current_id := (select max(id) from billing_invoice );

    -- select the current highest ref_handler and increment it
    SET @next_handler := (select (ref_handler)+1 from billing_invoice WHERE id = @current_id);

    -- compare the current year ith the year of the creation_date of the previous record.
        -- true : do nothing
        -- false : reset the handler to 0001
     IF( (SELECT EXTRACT(YEAR FROM date_creation) FROM billing_invoice WHERE (id = @current_id)) = YEAR(CURDATE()) ,SET @next_id := @next_handler, SET @next_handler := 0001);

     -- if it is the first record
     IF ISNULL(@next_handler) THEN
         SET @next_handler := 0001;
     END IF;
 -- then set your new row accordingly
 SET NEW.ref_handler = @next_handler;
 SET NEW.ref= concat(NEW.case_id, '_', @next_handler, '_', DATE_FORMAT(NEW.date_creation, '%Y%m%d'));

END

But the statement which extract the the year is failing I do not know why.

Best Answer

But the statement which extract the the year is failing I do not know why.

Change the way it is assigning the the variables. Use SELECT...INTO. Below is an example,

SELECT IF( (SELECT EXTRACT(YEAR FROM date_creation) FROM billing_invoice 
WHERE (id = @current_id)) = YEAR(CURDATE()) ,@next_id := @next_handler, @next_handler := 0001) INTO @dummy;

This is not a perfect solution in that it creates the @dummy variable, but I can't comment so I just posted it.

Related Question