触发逻辑导致错误 [英] Triggers logic causes error

问题描述

我正在制作MySql数据库，其中有 film 表:

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

• 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;

出现错误:

无法更新存储的函数/触发器中的表"film"，因为它 已被调用此存储函数/触发器的语句使用.

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)

推荐答案

看起来您根本不需要trigger.相反，您可以这样配置FOREIGN KEY:如果删除film中的父记录，则将删除copy表中的条目.在此处.您需要使用CASCADE选项:

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:从父表中删除或更新该行，然后 自动删除或更新子表中的匹配行. 支持ON DELETE CASCADE和ON UPDATE CASCADE.

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.

FOREIGN KEY语法如下所示:

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

这篇关于触发逻辑导致错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！