0

I am using a MySQL database and I have this kind of table.

IDRefHandler
x    299      
x    300      
x    301      

But I want to create a trigger (before insert) which will set automaticaly the Handler field. How to select the previous Handler value and just add one with a trigger ?

Is there any better way that storing the result of SELECT Handler FROM table order by Handler DESC LIMIT 1 and storing this in a VAR. Is'nt it more efficient to use a magic word like OLD ? Or do I have to use a specific trick for this case?

PS : OLD is not usable in this case (source)

4
  • Yes, it is possible
    – Philᵀᴹ
    Jun 7, 2016 at 14:57
  • @Phil I edited my question. Sorry ... I do not really know how to proceed and an exemple will be useful
    – Atnaize
    Jun 7, 2016 at 15:06
  • This post doesn't really show any effort or research on your part. What have you tried and found to not work?
    – dfundako
    Jun 7, 2016 at 15:32
  • @dfundako I projected to use OLD.table.handler but I also read this dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html. That say that OLD can not be used during INSERT trigger. So I'm kinda lost
    – Atnaize
    Jun 7, 2016 at 20:33

1 Answer 1

0

You are treating Handler like an AUTO_INCREMENT. Why not get rid of ID? Or use it instead of having an extra column?

Edit

Since you want to "reset to 0" the number at the start of the year... Assuming this is what you want, then converting it to a TRIGGER should work:

START TRANSACTION;  -- To keep other connections from overlapping
SELECT @year := ...,
       @next := MAX(Handler)+1
    FROM ...;
IF (YEAR(now() > @year) THEN
    SET @next := 0;
ENDIF
INSERT ... @next ...;
1
  • Because that's not all the explanation. I just explained what I wanted with the less data possible. In fact the ref_handler will reset itself to 0 for each new year
    – Atnaize
    Jun 23, 2016 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.