Triggers logic causes error

问题:

I'm making MySql database, where I have film table:

• id
• title
• amount
• available
• description

and copy table with foreign key film_id:

• id
• film_id

And now I've written after delete trigger on copy:

UPDATE `film`
            SET available = available - 1
WHERE OLD.film_id = id;

And now I would like to write before delete trigger on film, due to fact that film is contrained by copy so I write:

DELETE FROM copy WHERE copy.film_id = OLD.id;

Here error occurs:

Can't update table 'film' in stored function/trigger because it already used by statement which invoked this stored function/trigger.

I would like to delete film -> delete copy -> update film (ERROR)

回答1:

It looks like you do not need the trigger at all. Instead, you can configure FOREIGN KEY in such a way that entries in copy table will get deleted if a parent record in film gets deleted. Have a look at MySQL's documentation here. You need to use CASCADE option:

CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported.

This is how the FOREIGN KEY syntax will look like:

CONSTRAINT fk_film_id FOREIGN KEY (film_id) REFERENCES film(id) ON DELETE CASCADE;