Description:
SHOW TRIGGERS and SHOW CREATE TRIGGER are inconsistent.
How to repeat:
DELIMITER //
CREATE
TRIGGER `test_trigger` BEFORE DELETE ON `test_table`
FOR EACH ROW BEGIN
DECLARE a VARCHAR(25);
SET a = 'I''m not happy';
END //
DELIMITER ;
SHOW TRIGGERS FROM `test` WHERE `Trigger`='test_trigger';
/* removes escapes
BEGIN
DECLARE a VARCHAR(25);
SET a = 'I'm not happy';
END
*/
-- whereas
SHOW CREATE TRIGGER `test_trigger`;
/* returns the SET statement in the body as
SET a = 'I''m not happy';
*/
Suggested fix:
Don't strip the escapes. The two SHOW statements are inconsistent IMO.
"a = 'I'm not happy';" is an invalid expression. For same reason the return from SHOW TRIGGERS cannot be re-used programmatically. Doing so will return a syntax error.
Workaround (in non-ANSI sql_mode) is to use doublequotes around the string like:
SET a = "I'm not happy";
.. a human user can do that (if he is aware of this issue) but programmatically it is not possible to know advance what kind of qoutes may appear in a string and what kind of quotes to use. And both kinds of quotes may actually appear in sames tring like this (unescaped) example *Peter's Pizzabar to open next week will be named "PizzaYog"*. What to do here? There is no solution for user how to write the TRIGGER and make the return from SHOW TRIGGERS usable for that TRIGGER.
-- Regards
-- Peter from 'Peter''s Programming Shop' (or was it 'Peter''s Pizzabar'?) :-)