Mysql – Get previous field’s value in MySQL trigger

MySQLtrigger

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

IDRefHandler
1 x    299
2 x    300
3 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)

Best Answer

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 ...;

Related Solutions

MySQL trigger: Compare value in another table

DROP TRIGGER IF EXISTS post_queue_insert;
CREATE TRIGGER post_queue_insert AFTER INSERT ON posts
FOR EACH ROW BEGIN
    DECLARE found_it INT;
    IF (NEW.post_type != 'revision' AND NEW.post_status != 'auto-draft') THEN
        SELECT COUNT(1) INTO found_it FROM some_other_table
        WHERE post_type = NEW.post_type;
        IF found_it = 0 THEN
            INSERT INTO event_queue (action_id, action_do, action_key, action_value, action_event, action_timestamp, sync_complete, SITE_ID) VALUES (NEW.ID, 'post', NEW.post_type, NEW.post_status, 'insert', UNIX_TIMESTAMP(now()), 0, 1);
        END IF;
    END IF;
END;

If found_it = 0, then the row does not contain a value matching NEW.post_type.

Give it a Try !!!

Mysql – Trying to get this MySQL Trigger working

The way of declaring VARCHAR is correct
The error is that you have to specify the table, and the action. i.e. ON worker1 BEFORE {INSERT|UPDATE|DELETE}
"if inserting or updating Worker1 then" I think you need two triggers if you want the action to be performed BEFORE INERT and BEFORE UPDATE
Change the delimiter, and end your trigger with it.
If the provided code is not meant to be pseudopod, I recommend reading about MySQL triggers before start coding them.

Best Answer

Related Solutions

MySQL trigger: Compare value in another table

Mysql – Trying to get this MySQL Trigger working

Related Question