MYSQL Truncated incorrect DOUBLE while using double and single quotation marks

Question

I'm created a trigger that inserts a row in a table after a row is deleted in another table.

Here's my trigger

CREATE TRIGGER trigger_delete_log_animal AFTER delete ON animal
FOR EACH ROW
    INSERT INTO log_animais (momento, ocorrencia)
    VALUES (now(), "The register '" + old.nome_animal + "' was deleted from the animal table");

I want nome_animal to be between single quotation marks.

But I get the following error when I delete a row from the animal table:

Error Code: 1292. Truncated incorrect DOUBLE value: 'The register ''

I've tried changing it to

'The register "' + old.nome_animal + '" was deleted from the animal table'

And also to

"The register \'" + old.nome_animal + "\' was deleted from the animal table"

But it's no good.

What am I doing wrong?

Answer 1

Don't try to build strings with + in your SQL code.

Use CONCAT() instead:

 VALUES (now(), CONCAT(
           'The register \'', 
            old.nome_animal, 
            '\' was deleted from the animal table'));

And, escape the ' characters you want in your strings with \.

'Mrs. O\'Leary\'s cow.'