mysql动态创建触发器,在存储过程中创建MySQL动态触发器

I want to create the dynamic trigger when call the procedure, it give the mysql server version syntax error, when i select that query and execute in separate query editor it will be executed.

DROP TABLE IF EXISTS auditLog;

CREATE TABLE `auditlog` (

`tableName` VARCHAR(255) DEFAULT NULL,

`rowPK` INT(11) DEFAULT NULL,

`fieldName` VARCHAR(255) DEFAULT NULL,

`old_value` VARCHAR(255) DEFAULT NULL,

`new_value` VARCHAR(255) DEFAULT NULL,

`ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

) ENGINE=ARCHIVE;

CREATE TABLE country(countryname VARCHAR(100);

DROP PROCEDURE IF EXISTS addLogTrigger;

DELIMITER $

/*

call addLogTrigger('country','CountryID','CountryName')

*/

CREATE PROCEDURE addLogTrigger

(IN tableName VARCHAR(255), IN pkField VARCHAR(255),IN column_name VARCHAR(50))

BEGIN

SET @qry = CONCAT

(

'

DROP TRIGGER IF EXISTS ', tableName, '_AU ;

CREATE TRIGGER ', tableName, '_AU AFTER UPDATE ON ', tableName, '

FOR EACH ROW

','

INSERT INTO auditLog (',

'tableName, ',

'rowPK, ',

'fieldName, ',

'old_value, ',

'new_value'

') VALUES

( ''',

tablename, ''', NEW.',

pkField, ', ''',

column_name, ''', OLD.',

column_name, ', NEW.',

column_name,

');

'

, '

'

) ;

SELECT @qry;

PREPARE stmt FROM @qry;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END$

DELIMITER ;

解决方案

Karthikeyan,

You can not create a trigger inside stored procedure.

Trigger is a DDL statement which is not allowed inside procedures.

" ERROR 1303 (2F003): Can't create a TRIGGER from within another stored routine "

To solve your purpose you can do following:

Take current values from table inside variable.

Simply write a update query inside your procedure, then check whether value is updated or not by "ROW_COUNT()".

Compare new value with old value, of changed then manually insert into change_log table.