Trigger created successfully but its not working as it should

Question

I have created this trigger that inserts a record into another table once a record has been inserted in another table

DELIMITER $$
CREATE TRIGGER logan AFTER INSERT ON messagein
FOR EACH ROW
  BEGIN
  DECLARE le_number INT;
  SET le_number = (select messagefrom from messagein where id=NEW.id);
    insert into messageout (MessageTo,MessageText) VALUES(le_number,"Thank you for contacting our company.Our sales representatives shall be in touch with you soon.");
  END $$
DELIMITER ;

This trigger is created successfully but no message is inserted into the messageout table.What is the reason for this?.

Answer 1

This trigger is wrong, it contains two errors:

• quotes are used in a string literal instead of apostrophes
• triggers cannot access a table (for reading or writing) that caused the trigger invovation (see this link: http://dev.mysql.com/doc/refman/5.7/en/faqs-triggers.html#qandaitem-B-5-1-9)

Try this code:

CREATE TRIGGER logan AFTER INSERT ON messagein
FOR EACH ROW
  BEGIN
    insert into messageout (MessageTo,MessageText) 
    VALUES(NEW.messagefrom,'Thank you for contacting our company.Our sales representatives shall be in touch with you soon.');
  END 

A link to working demo: http://sqlfiddle.com/#!2/bdbff/1