Auto assign value-message to rows in a table with MySQL trigger

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.

Method 1

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 ;

https://www.db-fiddle.com/f/6LMbhugzVigtcxJzVkCVLs/1

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply