sql触发器将列名存储在变量中并将其用于OLD.myvar时在使用SQL触发器时出错 [英] sql triggers I have error while storing column name in variable and use it for OLD.myvar in sql triggers
本文介绍了sql触发器将列名存储在变量中并将其用于OLD.myvar时在使用SQL触发器时出错的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
MySQL说:#1054-'OLD'中的未知列'TMPCOL'
MySQL said: #1054 - Unknown column 'TMPCOL' in 'OLD'
BEGIN
DECLARE TOTAL,I INT;
DECLARE CURRENT_CLOUMN VARCHAR(255);
DECLARE TRIGGER_ON_TABLE VARCHAR(255);
DECLARE TRIGGER_OP VARCHAR(255);
DECLARE olddd VARCHAR(255);
DECLARE newwww VARCHAR(255);
SET TOTAL=0;
SET I=0;
SET CURRENT_CLOUMN='atulbaldaniya.com';
SET TRIGGER_ON_TABLE='atulbaldaniya';
SET TRIGGER_OP='UPDATE';
SET olddd='';
SET newwww='';
SELECT COUNT(COLUMN_NAME) INTO TOTAL FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = TRIGGER_ON_TABLE ORDER BY ORDINAL_POSITION;
WHILE I < TOTAL DO
SELECT COLUMN_NAME INTO CURRENT_CLOUMN FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = TRIGGER_ON_TABLE ORDER BY ORDINAL_POSITION LIMIT I,1;
SET olddd=OLD.CURRENT_CLOUMN;
SET newwww=NEW.CURRENT_CLOUMN;
IF(olddd != newwww) THEN
INSERT INTO TMP(DATA1, DATA2)VALUES(TOTAL ,CONCAT('Change in ',olddd,' To ',newwww))
END IF;
SET I=I+1;
END WHILE ;
END
我不想
INSERT INTO TMP(DATA1, DATA2)VALUES(TOTAL ,OLD.ID);
一切正常.
I dont want to
INSERT INTO TMP(DATA1, DATA2)VALUES(TOTAL ,OLD.ID);
this is working fine.
我想将列名存储在变量中,并使用 OLD.MYVAR
I want to store column name in a variable and use like OLD.MYVAR
我们该怎么做?
推荐答案
您可以将查询构建为字符串,然后准备并执行它,类似于:
You would build your query as a string, then prepare it and execute it, something similar to:
SET @s = CONCAT('INSERT INTO TMP(DATA1, DATA2) VALUES ("DATA",OLD.',TMPCOL,');');
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
这篇关于sql触发器将列名存储在变量中并将其用于OLD.myvar时在使用SQL触发器时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文