mysql存储过程触发器_在MySQL中使用存储过程创建触发器

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 ;

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.

解决方案

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