error in SQL syntax!!

[Wrudra]

Hello all, I've some matters with triggers i.e.

CREATE TRIGGER maj_cumul BEFORE UPDATE ON planning
FOR EACH ROW
IF NEW.nbssc>nbssc THEN BEGIN
UPDATE prof SET nbc=nbc+NEW.nbssc WHERE planning.numprof=prof.numprof;
END;
ELSE
UPDATE prof SET nbc=nbc-NEW.nbssc WHERE planning.numprof=prof.numprof;
END;
END IF;

MySQL a réponduocumentation
#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 'UPDATE prof SET nbc=nbc+NEW.nbssc WHERE planning.numprof=prof.numprof' at line 4
Please help me to understand.
thank you. -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[Yadira]

As per me the BEGIN/END statement should be around the entire body:

CREATE TRIGGER maj_cumul BEFORE UPDATE ON planning
FOR EACH ROW
BEGIN
IF NEW.nbssc>nbssc THEN
UPDATE prof SET nbc=nbc+NEW.nbssc WHERE planning.numprof=prof.numprof;
ELSE
UPDATE prof SET nbc=nbc-NEW.nbssc WHERE planning.numprof=prof.numprof;
END IF;
END

Dont know how phpMyAdmin works, but at the command line you need delimiter statements to tell the client not to process the input when it sees the first ;, but look for a different delimiter. On the command line it would look something like:


DELIMITER //

CREATE TRIGGER maj_cumul BEFORE UPDATE ON planning
FOR EACH ROW
BEGIN
IF NEW.nbssc>nbssc THEN
UPDATE prof SET nbc=nbc+NEW.nbssc WHERE planning.numprof=prof.numprof;
ELSE
UPDATE prof SET nbc=nbc-NEW.nbssc WHERE planning.numprof=prof.numprof;
END -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --