在MySQL中使用存储过程创建触发器 [英] Create a trigger with a stored procedure in MySQL

问题描述

是否可以构建一个在MySQL中创建触发器的存储过程？

我在下面有存储的proc，它工作正常，但它只输出创建触发器的代码，但没有

Is it possible to build a stored procedure that creates a trigger in MySQL?
I have the stored proc below, which works fine, but it only outputs the code to create the trigger, but does not actually create it.

DELIMITER $
CREATE PROCEDURE addCustomerLogTrigger()
BEGIN
    SELECT CONCAT(
        'CREATE TRIGGER customer_audit AFTER UPDATE ON customer FOR EACH ROW BEGIN ',
        GROUP_CONCAT(
            CONCAT(
                'IF OLD.', column_name, ' != NEW.', column_name, ' THEN INSERT INTO st_person_audit_log (',
                    'id, ',
                    'fieldName, ',
                    'old_value, ',
                    'new_value, ',
                    'time_stamp, ',
                    'person_id'
                    ') VALUES (
                    NEW.id,
                    ''', column_name, ''', 
                    OLD.', column_name, ',
                    NEW.', column_name, ',
                    NOW(),
                    NEW.last_updated_by
                ); END IF;'
            )
            SEPARATOR ' '
            ), ' END;$'
        )
        FROM
            information_schema.columns
        WHERE
            table_schema = database()
            AND table_name = 'customer';

END$
DELIMITER ;

是否可以一次性创建它？
我是从 http://thenoyes.com/littlenoise/?p=43并根据我的需要进行了一些调整。

Is there a way to create it in one go? I got it from http://thenoyes.com/littlenoise/?p=43 and adjusted it a little to my needs.

仅供参考，只要客户表中发生任何更改，此触发器就会在审计表中插入一个旧/新值对。

Just FYI, this trigger inserts a old/new value pair into an audit table, whenever something changes in the customer table.

推荐答案

不，这是不可能的，即使使用MySQL已准备好的语句。

No, it is not possible, even with MySQL prepared statements.

这篇关于在MySQL中使用存储过程创建触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！