如何从mysql5的触发器中的选择查询中获取值? [英] how to get value from select query in trigger in mysql5?

问题描述

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

How to get value from select query in trigger and insert that value in table?

推荐答案

对于INSERT触发器查询，您将使用对象NEW
对于UPDATE触发器查询，您将使用对象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，而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的触发器中的选择查询中获取值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！