mysql触发器获取条件表值,如何从mysql5的触发器中的选择查询中获取值？

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

解决方案

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

Example 1 : iF you ran INSERT INTO mytable (num) VALUES (10);

In the INSERT trigger, you reference the column as 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)

Example 3 : iF you ran DELETE mytable num = 104;

In the DELETE trigger, you reference 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 ;