mysql trigger after insert should set available/not available

Question

i am learning mysql and cakephp since a few days. I building a small/simple movie rental database with 3 tables "movies,customers and rentals".

CREATE TABLE IF NOT EXISTS `videothek9`.`movies` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(45) NULL,
  `status` ENUM('available','not available') NULL DEFAULT 'available',
  PRIMARY KEY (`id`))

CREATE TABLE IF NOT EXISTS `videothek9`.`rentals` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `rental_date` DATE NOT NULL,
  `return_date` DATE NOT NULL,
  `movie_id` INT NOT NULL,
  `customer_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_movies_id_idx` (`movie_id` ASC),
  INDEX `fk_customers_id_idx` (`customer_id` ASC),
  CONSTRAINT `fk_movies_id`
    FOREIGN KEY (`movie_id`)
    REFERENCES `videothek9`.`movies` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_customers_id`
    FOREIGN KEY (`customer_id`)
    REFERENCES `videothek9`.`customers` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)

Now i need a trigger which updates the "status" field in movies everytime a new rental with the specific movie id is made.

DELIMITER $$
CREATE TRIGGER `rentals_AINS` AFTER INSERT ON `rentals` FOR EACH ROW
UPDATE movies
SET status = 'not available'
WHERE id = NEW.id

My last tries wont work ... Please help me.

score -1 · Accepted Answer · answered Jun 04 '14 at 21:01

Per your rentals table structure as below, movie_id referencing to movies (id).

  CONSTRAINT `fk_movies_id`
    FOREIGN KEY (`movie_id`)
    REFERENCES `videothek9`.`movies` (`id`)

So, in your trigger the UPDATE statement should look like below, cause id in rentals table may/may not match with ID in movies table cause that's not the referencing column

UPDATE movies
SET status = 'not available'
WHERE id = NEW.movie_id

mysql trigger after insert should set available/not available

1 Answers1