Help me! big problem with dynamic SQL

alibabasakura · #1 (**permalink**) 03-25-09, 05:26

Hi all,

I'm have a procedure:

Quote:

DROP PROCEDURE IF EXISTS sp_UpdateMetaArticle;
delimiter $$
CREATE PROCEDURE sp_UpdateMetaArticle(IN meta_key varchar(255), IN meta_value varchar(255), IN post_id integer)
BEGIN
SET @sql = concat('UPDATE do_articles SET ', meta_key, '=? WHERE PostId=?');
PREPARE stmp FROM @sql;
EXECUTE stmp USING @meta_value, @post_id;
DEALLOCATE PREPARE stmp;
END;

And a trigger

Quote:

DROP TRIGGER IF EXISTS tg_PostMetaUpdate;
delimiter $$
CREATE TRIGGER tg_PostMetaUpdate AFTER UPDATE ON wp_postmeta
FOR EACH ROW BEGIN
CALL sp_UpdateMetaArticle(NEW.meta_key, NEW.meta_value, NEW.post_id);
END;

........and my problem:

When have update in table postmeta => run tg_PostMetaUpdate

.. but no run dynamic SQL

Quote:

SET @sql = concat('UPDATE do_articles SET ', meta_key, '=? WHERE PostId=?');
PREPARE stmp FROM @sql;
EXECUTE stmp USING @meta_value, @post_id;
DEALLOCATE PREPARE stmp;

But, run procedure by phpMyAdmin

Quote:

CALL sp_UpdateMetaArticle('Description', 'Vnexpress.net', 12);

Is ok

I want run procedure in trigger

Help me!