MySQL Trigger to update another table

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 e开发者_高级运维rror 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$$

---

try

AFTER INSERT ON `cinemax`.`bookings`

instead of

AFTER UPDATE ON `cinemax`.`bookings`

---

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.