使用自动增量字段在插入触发器之前/之后

问题描述：

我遇到了应该用于修复表中列的插入触发器的麻烦:

I'm having troubles with an insert trigger that is supposed to fix a column in a table:

id        - auto increment int
thread_id - int [NULL]

我要实现的是将thread_id设置为id(如果插入为NULL).我失败了，因为:

What I want to achieve is to set the thread_id to id if it's inserted as NULL. I have failed because:

使用before insert触发器仍然没有id的新值，并且神秘地将thread_id设置为0
使用after insert - update触发器会引发合理的异常can't update table because it is already used by the statement.
您不能添加其他自动增量字段

using before insert trigger still does not have the new value for id and mysteriously sets thread_id to 0
using after insert - update trigger throws reasonable exception can't update table because it is already used by the statement.
you can not add additional auto increment field

该问题的解决方案是什么?

What is the solution to this problem?

答

DELIMITER $$

CREATE TRIGGER mytrigger BEFORE INSERT ON yourtable 
FOR EACH ROW BEGIN

SET NEW.thread_id = IF(ISNULL(NEW.thread_id), 0, NEW.thread_id);

END;

$$

为了修改当前记录的值，您不使用UPDATE语句，而是使用NEW.columname

in order to modify values of the current record, you don't use UPDATE statement, you access them by using NEW.columname

使用自动增量字段在插入触发器之前/之后

相关推荐