Trigger FOR EACH ROW

Question

Hi guys I am trying to make a trigger so that if the price of a beer increased more than $1 then I add the name of the bar to the RIPOFFBAR table.

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;

and as you can see I used FOR EACH ROW BEGIN with IF statement But for some reason it is not working any help would be much appreciated

Answer 1

First mistake you have made that is using " trigger_time = AFTER " when you are trying to update the row. According mysql manual :

In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or used to update a row. (Such a SET statement has no effect in an AFTER trigger because the row change will have already occurred.)

For details check the manual from here : http://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html

I have created two tables, insert data and write the trigger following your information :

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";

After that if you VIEW the ripo_off_bar table you will see there has been added your updated beer_name information !:

VIEW :

SELECT * FROM ripo_off_bar;

I have added screenshot of coded also. Hope these will help you to complete your task. Happy coding !!!