MySQL Forums
Forum List  »  Triggers

mysql trigger which affect multiple rows
Posted by: Adnan Šatri
Date: April 01, 2017 02:50AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
mysql trigger which affect multiple rows
1832
April 01, 2017 02:50AM


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.