MySQL Trigger to update another table

Question

I have the following two tables in a MySql database:

Bookings
BookingID | ClientID | SeatID

SeatAvailability
SeatAvailabilityID | BookingID | ShowID | Available

They are linked on SeatID/SeatAvailabilityID. I'm trying to write a trigger which updates the SeatAvailability table each time a row is inserted in Bookings. The trigger should change SeatAvailability.Available to 0 and also enter the BookingID from Bookings into the BookingID field in SeatAvailability with the same SeatAvailabilityID.

I've written this trigger, MySql accepts it but gives an error when inserting "ERROR 1054: Unknown column 'cinemax.bookings.SeatID' in 'where clause'".

DELIMITER $$

USE `cinemax`$$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `cinemax`.`update_available`
AFTER INSERT ON `cinemax`.`bookings`
FOR EACH ROW
UPDATE cinemax.seatavailability
SET cinemax.seatavailability.Availabe=0, cinemax.seatavailability.BookingID=cinemax.bookings.BookingID
WHERE cinemax.bookings.SeatID=cinemax.seatavailability.SeatAvailabilityID$$

You should use AFTER INSERT instead of AFTER UPDATE

Nishant
– Nishant

2011-03-31 15:10:12 +00:00
Commented Mar 31, 2011 at 15:10 — Nishant
– Nishant, Commented Mar 31, 2011 at 15:10

Nishant · Accepted Answer · 2011-03-31 15:10:47Z

1

try

AFTER INSERT ON `cinemax`.`bookings`

instead of

AFTER UPDATE ON `cinemax`.`bookings`

answered Mar 31, 2011 at 15:10

Nishant

56.1k13 gold badges119 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Matt Over a year ago

I get the following error when trying to insert a new row now "Unknown column 'cinemax.bookings.SeatID' in 'where clause'"

Matt Over a year ago

Bookings does have SeatID, I just called it SeatAvailabilityID in my question to avoid some confusion. I edited the question so that it's exactly like the database. Thanks for the help so far. You're definitely right about the INSERT ON instead of UPDATE ON.

Nishant Over a year ago

@Kentz there is something wrong with query -- and very likely a typo in query in name of column. try running the query independently.

Matt · Accepted Answer · 2011-05-07 14:56:48Z

It's a couple of months late, but I decided to give it a quick shot before handing in the overall assignment. In the meantime I switched to postgres as it seemed to offer more functionality (albeit not as user friendly). I first had to create a trigger function:

CREATE OR REPLACE FUNCTION updateseatavailable()
RETURNS trigger AS
$BODY$
BEGIN

            IF (TG_OP = 'INSERT') THEN
                UPDATE "SeatAvailability"
            SET "Available"='FALSE' AND "BookingID"=NEW."BookingID" WHERE "SeatAvailabilityID"=NEW."SeatID";

            ELSIF (TG_OP = 'DELETE') THEN
            UPDATE "SeatAvailability"
            SET "Available"='TRUE'  WHERE "SeatAvailabilityID"=OLD."SeatID";

            END IF;

            RETURN NEW;
        END;
    $BODY$
      LANGUAGE plpgsql VOLATILE

and then simply call the function/procedure from a trigger:

CREATE TRIGGER UpdateSeatAvailable
AFTER INSERT OR DELETE ON "Bookings"
FOR EACH ROW
EXECUTE PROCEDURE updateSeatAvailable();

I wasn't able to get the BookingID in SeatAvailability to update for some reason (on Insert nothing happened and on Delete I got an error telling me Available cannot be null, even though I was changing the BookingID) so I omitted that in postgres,and implemented it with Java instead. It's not the best way but still better than nothing.

I decided to post my solution just in case someone has a similar problem and stumbles upon this question.

Collectives™ on Stack Overflow

MySQL Trigger to update another table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related