如何从mysql5中的触发器中的select查询中获取值？

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

如何从触发器中的select查询中获取值并在表中插入该值?

1 个解决方案

#1

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

对于INSERT触发器查询,您将使用对象NEW对于UPDATE触发器查询,您将使用对象OLD和NEW对于DELETE触发器查询,您将使用对象OLD

Example 1 : iF you ran INSERT INTO mytable (num) VALUES (10);
In the INSERT trigger, you reference the column as NEW.num (10);

示例1:运行INSERT INTO mytable(num)VALUES(10)的iF;在INSERT触发器中,您将该列引用为NEW.num(10);

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)

例2:iF你跑了UPDATE mytable SET num = 41 WHERE num = 10;在UPDATE触发器中,您引用OLD.num(10)和NEW.num(41)

Example 3 : iF you ran DELETE mytable num = 104;
In the DELETE trigger, you reference OLD.num (104)

例3:你运行的iF DELETE mytable num = 104;在DELETE触发器中,您引用OLD.num(104)

Use something like this:

使用这样的东西:

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 ;