mysql trigger which affect multiple rows

[Adnan Šatri]

I have 2 tables as in the image below.



I have trgger AFTER INSERT on table "Passengers travel" which pick up Price from "Travel table", and calculate Price_with_discount with Discount.

Now I must to create trigger AFTER UPDATE on "Travels" table - in case that coustumer change Price on "Travels" table, it must be changed on all "Passenger travels" which have FK_id_travel key same as ID_travel in "Travels" table. (there will be multiple rows with that key)

I tried this:

---------------------

BEGIN

DECLARE a FLOAT;
DECLARE b FLOAT;
DECLARE c FLOAT;

SET a=(Select Discount from Passenger_travel where Passenger_travel.FK_ID_travel=new.ID_travel;

if new.Price != old.Price THEN

SET b=a/100*new.Price;
SET c=new.Price-b;

UPDATE Passengers_travel set Price_with_discount=c where Passenger_travel.FK_ID_travel=new.ID_travel;
END IF;
END

-------------------------------------


When I create trriger, I get this error: "Subquery returns more than 1 row"

How to create trigger which will affect multiple rows, is this possible?