代码之家 › 专栏 › 技术社区 › szeryf

用于将字段更新为id值的mysql触发器

triggers insert mysql

szeryf · 技术社区 · 15 年前

我希望有一个触发器对插入的记录执行以下操作:

 # pseudocode
 if new.group_id is null
    set new.group_id = new.id
 else
    # don't touch it
 end

更清楚地说:假设我有一张有三列的桌子: id 主键, group_id int value VARCHAR

当我插入 群标识 像这样:

INSERT INTO table(value, group_id) VALUES ('a', 10)

我想要:

id | group_id | value
---+----------+------
 1 |       10 | a

但当我省略 群标识 :

INSERT INTO table(value) VALUES ('b')

它应该自动设置为 身份证件 此记录:

id | group_id | value
---+----------+------
 2 |        2 | b

有没有可能用扳机?(我知道我可以在插入后更新记录,但使用触发器会更好。)

6 回复 | 直到 6 年前

Bill Karwin 15 年前

我不知道在一个语句中如何做到这一点,即使使用触发器也是如此。

@lucky建议的触发解决方案在mysql中如下所示:

CREATE TRIGGER MyTrigger BEFORE INSERT ON MyTable
FOR EACH ROW BEGIN
  SET NEW.group_id = COALESCE(NEW.group_id, NEW.id);
END

但是,有一个问题。在 BEFORE INSERT 相位,自动生成 id 尚未生成值。所以如果 group_id 为空,默认为 NEW.id 总是0。

但是如果你在 AFTER INSERT 阶段,因此您可以访问 新身份证 ,不能修改列值。

mysql不支持 DEFAULT 列,因此也不能在表定义中声明此行为。

唯一的解决办法是 INSERT ,然后立即执行 UPDATE 改变 群标识 如果没有设置。

INSERT INTO MyTable (group_id, value) VALUES (NULL, 'a');
UPDATE MyTable SET group_id = COALESCE(group_id, id) WHERE id = LAST_INSERT_ID();

Nabeel Ahmed 7 年前

我在这里回答,就像接受的回答一样 Bill Karwin 国家:

在插入前阶段,自动生成的ID值尚未生成。因此,如果group_id为空,则默认为new.id,即总是0。

我有一个答案-对于OP(和即将到来的访客),这里有几个要点: 您不能更新从中调用触发器的表,因为您将 错误1442 :

Error Code: 1442
Can't update table 'MyTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

1.更新新行 使用 BEFORE INSERT ON 触发器,这样您就可以更新新行的所有字段,这些字段可以通过新的运算符(即

set NEW.group_id = NEW.id

2.在插入前获取自动增量值 :

SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='MyTable'

综上所述,“d”的触发器SQL如下所示:

DELIMITER //
DROP TRIGGER IF EXISTS MyTrigger//
CREATE TRIGGER MyTrigger BEFORE INSERT ON MyTable
FOR EACH ROW BEGIN
    IF new.group_id IS NULL
        set @auto_id := (SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES
                         WHERE TABLE_NAME='MyTable' AND TABLE_SCHEMA=DATABASE() ); 
        set NEW.group_id = @auto_id;
    ENF IF;
END;
//
DELIMITER ;

Gabriel Bouyssou 6 年前

这对我有用

DELIMITER $$
CREATE TRIGGER `myTriggerNameHere`
BEFORE INSERT ON `table` FOR EACH ROW
BEGIN
    SET NEW.group_id = IF(NEW.group_id IS NULL, LAST_INSERT_ID()+1, NEW.group_id);
END;
$$
DELIMITER ;

Justin Giboney 15 年前

我相信这对你有用

我有两张桌子

test_b: a_id, value
test_c: a_id, value

在插入测试B时有一个触发器,它检查a_id是否为空,如果为空,则插入0

CREATE TRIGGER test_b AFTER INSERT ON test_b
  FOR EACH ROW 
    INSERT INTO test_c (a_id, value) VALUES (IFNULL(NEW.a_id, 0),NEW.value)

-2

Lucky 15 年前

这个触发器应该按您的要求执行。

   CREATE TRIGGER mytrigger BEFORE INSERT ON mytable
          IF new.group_id IS NULL
            SET new.group_id = new.id
          END IF
      END;

它是从 MYSQL documentation 页。

-3

Tarek Kamil 12 年前

在这种情况下,扳机似乎是多余的。只需应用默认值。

CREATE TABLE `test` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `value` varchar(100) NOT NULL,
    `group_id` TINYINT(3) UNSIGNED NOT NULL DEFAULT '2'
)