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'?) :-)
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'?) :-)