问题描述
如何从触发器中的选择查询中获取值并将该值插入表中?
How to get value from select query in trigger and insert that value in table?
推荐答案
对于 INSERT 触发器查询，您将使用对象 NEW
对于 UPDATE Trigger 查询，您将使用对象 OLD 和 NEW
对于 DELETE 触发器查询，您将使用对象 OLD
For an INSERT Trigger query you would use the object NEW
For an UPDATE Trigger query you would use the object OLD and NEW
For a DELETE Trigger query you would use the object OLD
示例 1:如果您运行 INSERT INTO mytable (num) VALUES (10);
在 INSERT 触发器中，您将该列引用为 NEW.num (10);
Example 1 : iF you ran INSERT INTO mytable (num) VALUES (10);
In the INSERT trigger, you reference the column as NEW.num (10);
示例 2:如果您运行 UPDATE mytable SET num = 41 WHERE num = 10;
在 UPDATE 触发器中，您引用 OLD.num (10) 和 NEW.num (41)
Example 2 : iF you ran UPDATE mytable SET num = 41 WHERE num = 10;
In the UPDATE trigger, you reference OLD.num (10) and NEW.num (41)
示例 3:如果您运行 DELETE mytable num = 104;
在 DELETE 触发器中，您引用了 OLD.num (104)
Example 3 : iF you ran DELETE mytable num = 104;
In the DELETE trigger, you reference OLD.num (104)
使用这样的东西:
DELIMITER $$
create trigger my_trigger
AFTER UPDATE on my_update_table
for each row
begin
DECLARE P1,P2 VARCHAR(50);
SELECT PRICENAME INTO P1 FROM PRICEIES WHERE PRICEID=OLD.PRICEID;
SELECT PRICENAME INTO P2 FROM PRICEIES WHERE PRICEID=NEW.PRICEID;
INSERT INTO AUDITLOG(OLDVALUE, NEWVALUE) VALUES (P1,P2);
end $$
DELIMITER ;
这篇关于如何从mysql5触发器中的选择查询中获取值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！