触发逻辑导致错误

问题描述

我正在制作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;

出现错误:

我想删除电影->删除副本->更新电影(错误)

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:

FOREIGN KEY语法如下所示:

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

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