Create trigger in mysql on insert where it compares fields of added row

Question

I am new with mysql triggers, I have 2 tables in a database, one is called tasks and the other is task_rules.

Once a new task_rule is inserted, I want to compare the field time (which is a time object) to the current time.

if it is greater than the current time, I want to add a new row in tasks and set rid (in tasks) to id of the newly added rule, and the time field in tasks to the time field of the newly added row.

I am getting many syntax errors and i didnt know how to create this trigger.

BEGIN
DECLARE @time TIME
DECLARE @freq VARCHAR(400)
@time = NEW.time
@freq = NEW.frequency
IF (@time > NOW()) AND (@freq == 'daily') THEN
INSERT INTO task_rules ('rid', 'time') VALUES (NEW.id, @time)
END IF
END

Im doing it using phpmyadmin

Please learn hoe to include a [verifiable example](https://www.stackoverflow.com/help/mvce) in your question. How are we supposed to fix your code if you don't include it in your post? — Simo, Sep 17 '18 at 11:13

P.Salmon · Accepted Answer · 2018-09-19T07:09:52.700

1) user defined variable (those preceded with @) should not be declared see How to declare a variable in MySQL? 2) to assign a value to a variable you have to use the SET statement 3) every statement must be terminated - if you are using phpmyadmin and the default terminator is set to ; change it and terminate your statements in the trigger with ; see - https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html 4) null safe equals in mysql is not == from memory this should be <=> see https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html 5) you should probably set delimiters before and after the trigger 6) column names should be escaped with back ticks not single quotes. 7) for each row clause missing before begin statement.

try this

drop trigger if exists t;
delimiter $$
create trigger t after insert on task
for each row
BEGIN
DECLARE vtime TIME;
DECLARE vfreq VARCHAR(400);
set time = NEW.time;
set freq = NEW.frequency;
IF (vtime > NOW()) AND (vfreq <=> 'daily') THEN
    INSERT INTO task_rules (`rid`, `time`) VALUES (NEW.id, vtime);
END IF;
END $$

delimiter ;

And do review https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

ERROR 1064 (42000): 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 ''rid', 'time') VALUES (NEW.id, time); — Charbel Hanna, Sep 17 '18 at 12:04
It look like you failed to change the single quotes around rid , time to back ticks. If its easier for you just lose them altogether, — P.Salmon, Sep 17 '18 at 12:07
One other thing it's not a good idea to have variable names that are the same as column names, — P.Salmon, Sep 17 '18 at 12:09

Create trigger in mysql on insert where it compares fields of added row

1 Answers1