如何从mysql5触发器中的选择查询中获取值?

问题描述

如何从触发器中的选择查询中获取值并将该值插入表中?

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触发器中的选择查询中获取值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！