All we need is an easy explanation of the problem, so here it is.
Im testing a trigger for an assignment where i need to set an specific value to each row on a table.
I have 2 tables tbl_cursos and tbl_modalidad. After i insert an entry on tbl_cursos the trigger must take 1 value from that table and add it to tbl_modalidad. Also the trigger must insert on tbl_modalidad a specifica value to Tipo_modalidad column, the value is "Presencial", then insert the date.
This is what i have
CREATE TRIGGER bienvenida AFTER INSERT ON tbl_cursos FOR EACH ROW BEGIN INSERT INTO tbl_modalidad (id_modalidad, tipo_modalidad, fecha_registro) VALUES (new.id_modalidad, `Presencial`, now());
After running the trigger i got an error message with
Presencial. I’ve tried to use SET table = ‘message’ but is wrong. Everything works perfectly if i delete the "presencial" value.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
In MySQL backticks are used for table and column identifiers and are necessary when the identifier is a MySQL reserved keyword or when the identifier contains whitespace characters, even though it is recommended to avoid using reserved keywords as column/table names or column/table names with spaces.
If the ANSI_QUOTES SQL mode is enabled, string literals can be quoted only within single quotation marks because a string quoted within double quotation marks is interpreted as an identifier https://dev.mysql.com/doc/refman/8.0/en/string-literals.html.
In your case Presencial should be single quoted, full trigger should be as below:
DELIMITER // CREATE TRIGGER bienvenida AFTER INSERT ON tbl_cursos FOR EACH ROW BEGIN INSERT INTO tbl_modalidad (id_modalidad, tipo_modalidad, fecha_registro) VALUES (new.id_modalidad, 'Presencial', NOW()); END// DELIMITER ;
You will find better explanation here that will hekp you understand the use case of quotes
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂