Trigger FOR EACH ROW

To learn more, refer to the manual available at http://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html. I followed your guidance to create two tables, insert data, and write a trigger. The first table is created as Table 1 and the second as Table 2. The insertion is performed, followed by the trigger. Finally, an update is made. Upon viewing the ripo_off_bar table, you will notice that the beer_name information has been updated. My question is regarding the use of a trigger to update multiple rows in a Parts table when a field in another table changes.


Question:

My objective is to create a trigger that adds the bar’s name to the RIPOFFBAR table if the beer price increases by more than $1.

CREATE TRIGGER PriceTrig
 AFTER UPDATE ON Sells
 FOR EACH ROW
BEGIN
IF (NEW.price > OLD.price + 1.00)
THEN
INSERT INTO RipoffBars(bar)
 VALUES(NEW.bar);
 END IF;
 END;

Although I implemented an IF statement within FOR EACH ROW BEGIN, it appears to be malfunctioning, and I would appreciate any assistance.


Solution:

Your initial error was utilizing “trigger_time = AFTER” while attempting to update the row, which goes against the MySQL manual.

By using the UPDATE privilege, you can modify the value of a column in a BEFORE trigger with SET NEW.col_name = value. This allows you to use the trigger to adjust the values that will be inserted into a new row or used for updating an existing row.

(Such a SET statement has no effect in an AFTER trigger because the row change will have already occurred.)

To obtain more information, refer to the trigger syntax manual available at http://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html.

As per your guidance, I have generated two tables, added data and implemented the trigger.


CREATE TABLE :

Table 1 :

create table sells (beer_name varchar(200),beer_price int(20));

Table 2 :

create table ripo_off_bar (beer_name varchar(200));


INSERT :

Insert into sells values("Root Beer", 2);


TRIGGER :

DELIMITER $$
CREATE TRIGGER price_update BEFORE UPDATE ON sells
FOR EACH ROW 
BEGIN 
IF(NEW.beer_price > OLD.beer_price + 1) THEN  
INSERT INTO ripo_off_bar (beer_name) VALUES (NEW.beer_name);
END IF;
END;$$


UPDATE :

update sells SET beer_price=8 where beer_name="Root Beer";

Once you check the ripo_off_bar table, you will notice that your beer_name information has been updated.


VIEW :

SELECT * FROM ripo_off_bar;

Included in my message are screenshots of the code that may assist you in finishing your task. Best of luck with your programming!

Trigger Creation & Updated value

Frequently Asked Questions