在存储过程中创建MySQL动态触发器 [英] Mysql dynamic trigger creation in stored procedure

问题描述

我想在调用过程时创建动态触发器，它给出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，

Karthikeyan,

您无法在存储过程内创建触发器. 触发器是DDL语句，在过程内部是不允许的.

You can not create a trigger inside stored procedure. Trigger is a DDL statement which is not allowed inside procedures.

错误1303(2F003):无法从另一个存储的例程中创建触发器"

" 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.

只需在您的过程内编写一个更新查询，然后通过"ROW_COUNT()"检查值是否被更新.

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

将已更改的新值与旧值进行比较，然后手动将其插入change_log表中.

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

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