带有SELECT语句的MySQL触发器 [英] MySQL Trigger with SELECT statement

问题描述

作为一个例子，假设我有一个这些表...

As an example, say I have a these tables...

人(id，当前年份)

personteam(ID，工作年，团队)

personteam (id, workyear, team)

一个人每年在公司工作时都会分配一个团队. "personteam"存储团队的记录.仅当"person"中的"currentyear"字段等于"personteam"中的"workyear"时，才允许插入"personteam".

A person is assigned a team for each year they work at a company. 'personteam' stores a record of the teams. The insert into 'personteam' is only allowed if the 'currentyear' field in 'person' equals the 'workyear' in 'personteam'.

我想根据用户存储在PERSON中的内容，在用户插入PERSON_TEAM时进行一些检查.因此，我正在使用select语句编写触发器.我对MySQL触发器的语法不是100％的确定，因此希望这是有道理的...

I want to do some checks when a user inserts into PERSON_TEAM, based on what is stored in PERSON. So I am writing a trigger with a select statement. I am not a 100% sure on the syntax of MySQL triggers so hopefully this makes sense...

在对personteam进行插入之前创建触发mytesttrigger

CREATE TRIGGER mytesttrigger BEFORE INSERT ON personteam

每行

SELECT currentyear AS @variablename
FROM person p
WHERE p.id = NEW.id

IF @variablename != NEW.workyear
ERROR 

END

有人可以为此操作提供经过修订的，更正的语法吗?

Could anyone provide a revised, corrected syntax for such an operation?

谢谢.

(这个例子很简单，所以我可以学习，所以请原谅听起来可能毫无意义)

(This example is trivial so I can learn so please excuse how meaningless it may sound)

推荐答案

根据MySQL标准，处理此问题的正确方法是将错误返回给客户端的SIGNAL命令.由于这是一个BEFORE触发器，因此触发器中的错误将阻止mysql继续运行并插入行.

According to the MySQL standard, the proper way to handle this would be the SIGNAL command to throw an error back to the client. since this is a BEFORE trigger, an error in the trigger will prevent mysql from moving on and inserting the row.

但是，mysql还不支持SIGNAL，因此我们必须想出一种方法来引起任意错误.

However, mysql does not yet support SIGNAL, so we have to come up with a way to cause an arbitrary error.

执行此操作的一种方法是调用不存在的过程，

One way to do this is to CALL a non-existent procedure, as demonstrated here.

一个更好的主意是从此表中删除INSERT权限，而使用存储过程为您处理插入.

A better idea would be to remove INSERT rights from this table and instead use a stored procedure to handle the insert for you.

这篇关于带有SELECT语句的MySQL触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！