How to get value from select query in trigger and insert that value in table?
The trigger event that initiates the trigger action can be an INSERT, DELETE, UPDATE, or a SELECT statement. The MERGE statement can also be the triggering event for an UPDATE, DELETE, or INSERT trigger.
MySQL 5.7 does not support triggers using FOR EACH STATEMENT .
To create a trigger, we need to change the delimiter. Inserting the row into Table1 activates the trigger and inserts the records into Table2. To insert record in Table1. To check if the records are inserted in both tables or not.
CREATE TRIGGER Update_estimate_from_line_items AFTER UPDATE ON estimate_line_items FOR EACH ROW BEGIN -- variable declarations DECLARE vPrev_amnt INT; DECLARE vNew_amnt INT; DECLARE nDiff INT; SET vPrev_amnt = OLD. price * OLD. quantity; SET vNew_amnt = NEW.
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 ;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With