使用自动增量字段插入触发器之前/之后 [英] Before / after insert trigger using auto increment field

问题描述

我遇到了一个应该修复表中列的插入触发器的问题:

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 触发器抛出合理的异常无法更新表，因为它已被语句使用.
• 您不能添加额外的自增字段

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

$$

Edit:为了修改当前记录的值，不使用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

这篇关于使用自动增量字段插入触发器之前/之后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！