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



  • 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.



  • In MySQL backticks are used for table and column identifiers and are necessary when the identifier is a https://dev.mysql.com/doc/refman/8.0/en/keywords.html 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 https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql that will hekp you understand the use case of quotes




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2