MySQL TRIGGER ON INSERT issue

Question

i'm going mad with a MySQL trigger. MySQL says there's an error in the code but i can't figure out what's the problem.

this is the TRIGGER

CREATE TRIGGER UPDATE_COUNTRY

AFTER INSERT 

    ON `dog`FOR EACH ROW

BEGIN

IF (NEW.export=1)
THEN
    IF (NEW.year > (SELECT MAX(`updated_year`) FROM `dog`  WHERE `code`=NEW.origin))
THEN 
UPDATE `dog` SET `updated_year`= NEW.year, `updated_month`= NEW.month WHERE `code`= NEW.origin;

ELSEIF (NEW.year = (SELECT MAX(`updated_year`) FROM `dog` WHERE `code`=NEW.origine)
        AND NEW.month > (SELECT MAX(`updated_month`) FROM `dog` WHERE `code`=NEW.origine AND `updated_year`=NEW.year))
    THEN UPDATE `dog` SET `updated_month`=NEW.month  WHERE `code`=NEW.origin;
ELSE
        RETURN NEW;
    END IF;

END IF;
RETURN NEW;
END;

My SQL says

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 13

Thank you all!

score 0 · Accepted Answer · answered Sep 25 '15 at 20:57

0

Your trigger is:

AFTER INSERT ON `dog`FOR EACH ROW

And your line 13 is

UPDATE `dog` SET `updated_year`= NEW.year, `updated_month`= NEW.month WHERE `code`= NEW.origin;

This kinda suck, but you cannot update/insert/delete with a trigger on the same table that you are currently inserting/updating/deleting data because the table is locked.

This is a limitation from MySQL (yeah and not all SGDB actually have this limitation...(

You will need to work around a different way if you want to update your "dog" table. You could use a store procedure to do both the insert and the update.

answered Sep 25 '15 at 20:57

Jonathan Parent Lévesque

1,902
1
25
35

it gives me an error also updating a different table – Alberto Lancellotti Sep 28 '15 at 07:04
Would it be possible for you to rephrase that? Was is giving you an error? Is it the trigger, the query or the stored procedure? What about the error message and code? Is it the same as your post? – Jonathan Parent Lévesque Sep 28 '15 at 12:26
The error is in the query, when i try do declare the trigger using the query, mySQL gives me the error. I think it's a sintax error. – Alberto Lancellotti Sep 28 '15 at 12:34
You could try to specified on the update query that the field to update are in the dog table, by example `BEGIN UPDATE users SET users.username=NEW.email, users.email = NEW.email, users.fname = NEW.firstname, users.lname = NEW.lastname WHERE users.email = OLD.email; END` But, like I said, you won't be able to update the dog table with a trigger (on the same table). Look at this: http://stackoverflow.com/a/12878145/4949388 I'm sorry, I faced that annoying limitation too when I tried to write a trigger to clean up my error log database. – Jonathan Parent Lévesque Sep 28 '15 at 13:56

MySQL TRIGGER ON INSERT issue

1 Answers1