mysql trigger which affect multiple rows
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?
Subject
Views
Written By
Posted
mysql trigger which affect multiple rows
1832
April 01, 2017 02:50AM
685
April 01, 2017 03:02AM
733
April 01, 2017 03:06AM
820
April 01, 2017 12:55PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.