I want to increment the loop_nb of a new row if task_id and case_id are already present in another row.
In this exemple, row 805 was added after row 804. Knowing that 805 is nearly the same than 800, the trigger increment the loop_nb before insert in the database.
I tried something, I am pretty new to trigger so sorry id I did it wrong
DECLARE loop_nb integer;
SET @loop_nb := (select loop_nb
where task_id = NEW.task_id
AND case_id = NEW.case_id
DESC LIMIT 1 );
IF ISNULL(@loop_nb) THEN
SET @loop_nb := @loop_nb+1;
SET NEW.loop_nb = @loop_nb;
But the problem is that my loop_nb is null by default. And I do not think I have the permission to change it.
Is there any alternatives?
Should my code be ok if I could change the default values of loop_nb to '0' ?