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.
精彩评论