CODEWITHSUNDEEP
mysql
User12345
User12345

Reputation: 325

MySQL/MariaDB TRIGGER

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?

Upvotes: 2

Views: 2699

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

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 ;

Upvotes: 4

User12345
User12345

Reputation: 325

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

Upvotes: 1

Related Questions