SQL Trigger - Update table A with sum(tableB.col) WHERE tableA.userID = tableB.userID

Question

I'm a pretty new database student...

I have two tables. User, and Trip.

Whenever a new Trip is added to the database, I want it to trigger an update on the User table in the "totalMiles" column. I want it to take the Trip.userID and change the value in the "totalMiles" column of only the user with the same User.userID as the Trip.userID entry.

I want it to take the sum from "tripMiles" where the Trip.userID = User.userID. userID is a foreign key in the Trip table from the User table.

right now I just have:

CREATE DEFINER='root'@'localhost'TRIGGER 'DATABASE'.'Trip_AFTER_INSERT' 
AFTER INSERT ON 'Trip' FOR EACH ROW
BEGIN
UPDATE User
SET totalMiles=sum(tripMiles)
WHERE Trip.userID = User.userID;
END

The error I get is "unknown column 'Trip.userID' in 'where clause'.

I googled and searched through a couple of posts here but I didn't really find anything.

Possible duplicate of [How to program a MySQL trigger to insert row into another table?](http://stackoverflow.com/questions/4753878/how-to-program-a-mysql-trigger-to-insert-row-into-another-table) — Saravanan Sachi, Mar 21 '17 at 00:36

score 0 · Answer 1 · edited May 23 '17 at 12:32

0

Just change de "User.userID" on where clausule to "NEW.userID"

The "New" object means your new TRIP added...

Something like this:

CREATE TRIGGER 'Trip_AFTER_INSERT'
AFTER INSERT ON 'Trip' FOR EACH ROW
BEGIN
UPDATE User
SET totalMiles=sum(tripMiles)
WHERE User.userID = NEW.userID;
END

If you want more, see documentation here:

https://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

and here

How to program a MySQL trigger to insert row into another table?

edited May 23 '17 at 12:32

Community

1
1

answered Mar 21 '17 at 00:37

Lucas Mathioni

19
2

This changed it to a different error. Now it's #1111 - Invalid use of group function. I'll read the documentation as well though. I don't really want it to insert into a new row in the user table, I want it to update a column in the user table where the new trip.userID = user.userID. – Bob Heinbokel Mar 21 '17 at 00:44
is i understand that... but see the difference... the NEW object is the NEW TRIP added... so if u have the trip name, to access just write: New.tripName. edited: Try this: CREATE TRIGGER 'Trip_AFTER_INSERT' AFTER INSERT ON 'Trip' FOR EACH ROW BEGIN UPDATE User SET totalMiles=sum(tripMiles) WHERE userID = NEW.userID; END – Lucas Mathioni Mar 21 '17 at 00:55

score 0 · Answer 2 · answered Mar 21 '17 at 02:16

You have multiple problems with your statement, starting with the single quotes. One method is to recalculate the sum:

CREATE TRIGGER DATABASE.Trip_AFTER_INSERT 
AFTER INSERT ON Trip FOR EACH ROW
BEGIN
    UPDATE User u 
        SET totalMiles = (SELECT sum(t.tripMiles) FROM Trip t WHERE t.userId = u.userId)
        WHERE u.userID = new.userID;
END;

The is overkill, though. If totalMiles is previously correct, then just adjust the value:

CREATE TRIGGER DATABASE.Trip_AFTER_INSERT 
AFTER INSERT ON Trip FOR EACH ROW
BEGIN
    UPDATE User u 
        SET totalMiles = u.totalMile + new.tripMiles - old.tripMailes; 
        WHERE u.userID = new.userID;
END;

This worked perfectly. After looking through your code it seems pretty obvious. I was kind of thrown into a project after having only a basic database class so far so I'm very appreciative for the help! — Bob Heinbokel, Mar 21 '17 at 22:27

SQL Trigger - Update table A with sum(tableB.col) WHERE tableA.userID = tableB.userID

2 Answers2