MySQL触发器插入问题 [英] MySQL trigger issue on insert

问题描述

我正在向成员表中执行插入操作，并且当添加新行时，我想运行触发器以更新成员表的用户名字段，但由于可能的死锁情况而受到限制，它不会让我进入。

I'm running an insert into a members table and when a new row is added I want to run a trigger to update the username field of the members table but it wont let me due to constraints due to possible deadlock situations.

DELIMITER //
CREATE TRIGGER tr_add_member
AFTER INSERT ON td_members
FOR EACH ROW BEGIN
    IF mem_username = '' THEN
         SET mem_username = CONCAT('user' , mem_id);
    END IF;
END//
DELIMITER ;

我尝试使用OLD和NEW关键字，但是它们不起作用，因此我删除了

I've tried using the OLD and NEW keywords but they don't work, I've removed the NEW and OLD keywords above but get the below error with this trigger.

ERROR 1193 (HY000): Unknown system variable 'mem_username'

我应该从触发器中调用一个过程来执行我想要的事情，然后运行一个简单的过程中是否包含 UPDATE 语句？

Should I be calling a procedure from the trigger to do what I want it and just run a simple UPDATE statement from within the procedure?

推荐答案

您必须使用插入之前触发器，但不是后插入触发器。

You have to use BEFORE INSERT trigger, but not an AFTER INSERT.

如果 mem_id 是自动递增的主键字段，则从 information_schema.tables
下一个自动递增值。 c $ c>并使用它。

And if mem_id is auto incremented primary key field, then find its
next auto increment value from information_schema.tables and use it.

按如下所示更改触发代码：

DELIMITER //

DROP TRIGGER IF EXISTS tr_add_member //

CREATE TRIGGER tr_add_member
       BEFORE INSERT ON td_members
       FOR EACH ROW 
BEGIN
    DECLARE _mem_id INT DEFAULT 0;

    IF length( trim( NEW.mem_username ) ) = 0 THEN
         SELECT AUTO_INCREMENT INTO _mem_id
           FROM INFORMATION_SCHEMA.TABLES
          WHERE TABLE_NAME = 'td_members'
            AND TABLE_SCHEMA = DATABASE();

         SET NEW.mem_username = CONCAT( 'user', _mem_id );
    END IF;
END;

//

DELIMITER ;

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