Mysql TRIGGER ON UPDATE with Functions

Question

im trying to make an trigger that updates a table when i modify another table... Here is my code:

CREATE TRIGGER updpartido AFTER UPDATE ON partidos
ON EACH ROW
    BEGIN
        SET @vgls = SELECT vgoles(NEW.eqvis)
        SET @lgls = SELECT vgoles(NEW.eqloc)
        UPDATE equipos SET gf=@vgls WHERE id=NEW.eqvis
        UPDATE equipos SET gf=@lgls WHERE id=NEW.eqloc
    END

What it must do is, when i update an match, it must automaticly run this trigger and update the goals.

But it gives me an error. What im doing wrong? Thanks and have a nice day...!

@muistooshort [Err] 1064 - 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 'ON EACH ROW BEGIN DECLARE at vgls int; DECLARE at lgls int; SET at vgls' at line 2 — Enrique Benitez
– Enrique Benitez, Commented Oct 24, 2011 at 2:19

mu is too short · Accepted Answer · 2011-10-24 02:19:41Z

3

I notice that you don't have any statement terminators in your trigger, that will generate some complaints because, for example, SET @lgls = SELECT vgoles(NEW.eqloc) UPDATE equipos SET gf=@vgls WHERE id=NEW.eqvis doesn't make any sense. So you need some semicolons in the trigger but you also need to get those semicolons past the parser by temporarily changing the delimiter. You're also using ON EACH ROW when it should be FOR EACH ROW:

delimiter |
CREATE TRIGGER updpartido AFTER UPDATE ON partidos
FOR EACH ROW
    BEGIN
        SET @vgls = SELECT vgoles(NEW.eqvis);
        SET @lgls = SELECT vgoles(NEW.eqloc);
        UPDATE equipos SET gf=@vgls WHERE id=NEW.eqvis;
        UPDATE equipos SET gf=@lgls WHERE id=NEW.eqloc;
    END;
|
delimiter ;

answered Oct 24, 2011 at 2:19

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Enrique Benitez Over a year ago

Sorry, my bad, i forgot about the delimiters! It worked like a charm! Thanks so much @muistooshort ! Have a nice day!

Collectives™ on Stack Overflow

Mysql TRIGGER ON UPDATE with Functions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related