MySQL syntax Error on Create Trigger

Question

Here is my trigger, I am getting the MySQL syntax error. I wanted to reduce the balance from sms_index table sms_count column value.

 CREATE TRIGGER sms_log_update AFTER UPDATE ON sms_index
  FOR EACH ROW
  BEGIN
    IF NEW.approved_status = '1' THEN
      UPDATE  sms_package SET  balance =  (balance - OLD.sms_count) WHERE  group_id = OLD.ins_group_id;
    END IF;
  END;

Error Message:

 #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 '' at line 5

You may not have set delimiters If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.-dev.mysql.com/doc/refman/5.7/en/stored-programs-defining.html — P.Salmon
– P.Salmon, Commented Feb 10, 2018 at 9:07

user47 · Accepted Answer · 2022-04-23 22:58:46Z

6

Your code looks correct, except for the possible problem of the delimiter. Try the following:

DELIMITER //
CREATE TRIGGER sms_log_update AFTER UPDATE ON sms_index
FOR EACH ROW
BEGIN
    IF NEW.approved_status = '1' THEN
        UPDATE  sms_package SET  balance =  (balance - OLD.sms_count)
        WHERE  group_id = OLD.ins_group_id;
    END IF;
END;//

DELIMITER ;

From the documentation:

However, just as for stored routines, if you use the mysql program to define a trigger that executes multiple statements, it is necessary to redefine the mysql statement delimiter so that you can use the ; statement delimiter within the trigger definition.

edited Apr 23, 2022 at 22:58

user47

1,2291 gold badge15 silver badges29 bronze badges

answered Feb 10, 2018 at 9:09

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

6 Comments

Muthamizhchelvan. V Over a year ago

now I am getting just "Error" no error id and error type. is it the problem with MySQL version?

Tim Biegeleisen Over a year ago

I see no errors in the trigger I wrote above. Try running an empty trigger and see what happens. If an empty trigger still generates an error message then something else is at work here.

Muthamizhchelvan. V Over a year ago

I generate the Trigger from PHPMyadmin its works, I'll check the generated query, it doesn't have the Begin and End comment. do you know why?

Tim Biegeleisen Over a year ago

Good question...I don't know why.

Muthamizhchelvan. V Over a year ago

oh my god, you are saying don't know, your Reputations says you are "G" :)

|

Collectives™ on Stack Overflow

MySQL syntax Error on Create Trigger

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related