MySQL/MariaDB TRIGGER

Question

I get an error when I try to create the below trigger on MySQL/MariaDB

CREATE TRIGGER `ABC` BEFORE INSERT ON `TABLE1`
FOR EACH ROW BEGIN
DECLARE LCL_VAR INTEGER;
SET LCL_LCL_VAR = NEW.A - NEW.B;
SET NEW.D= V;   
END;

ERROR

CREATE TRIGGER Q_DUR_CALC BEFORE INSERT ON TASK_Q_SWH FOR EACH ROW BEGIN DECLARE LCL_Q_DUR INTEGER;

MySQL said: Documentation

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

I've got a couple other triggers that I used to build this one. I can't figure out what is wrong syntactically with this. Can I please request for help to spot the problem with this?

Abhik Chakraborty · Accepted Answer · 2015-07-02 06:55:29Z

4

When you write a trigger you must specify a delimiter so that mysql explicitly executes your trigger block within the specified delimiter. If the delimiter is not provided then when it encounters any ; within the trigger statement it will try to execute the command till that block and hence you may get errors.

If you are using any user interface tools for generating the trigger you may check if there is an option to set the delimiter like in PHPMyadmin.

In CLI the trigger needs to be having a delimiter and it becomes

delimiter //

create trigger Q_DUR_CALC before insert on TASK_Q_SWH
for each row
begin
 declare LCL_Q_DUR INTEGER;
 set LCL_Q_DUR = new.TQ_TASK_DUR - new.TQ_TASK_RUN_DUR;
 SET new.TQ_Q_DUR = LCL_Q_DUR;   
end;//

delimiter ;

answered Jul 2, 2015 at 6:55

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

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

1 Comment

User12345 Over a year ago

Thank you Abhik. Appreciate your explanation. Thank you :)

User12345 · Accepted Answer · 2015-07-02 05:32:48Z

1

The problem was with the ";" after END. Strange that it complained about line 3!!

Here is the corrected version

CREATE TRIGGER `Q_DUR_CALC` BEFORE INSERT ON `TASK_Q_SWH`
FOR EACH ROW BEGIN
DECLARE LCL_Q_DUR INTEGER;
SET LCL_Q_DUR = NEW.TQ_TASK_DUR - NEW.TQ_TASK_RUN_DUR;
SET NEW.TQ_Q_DUR = LCL_Q_DUR;   
END

answered Jul 2, 2015 at 5:32

User12345

3251 gold badge7 silver badges20 bronze badges

Collectives™ on Stack Overflow

MySQL/MariaDB TRIGGER

ERROR

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

ERROR

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

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related