MySQL“插入后"触发器不断插入空值 [英] MySQL "After Insert" Trigger keeps inserting nulls

问题描述

我真的希望有人可以帮我解决这个问题，因为它困扰了我好几天了.我的用户表上有一个插入后触发器，可在每次插入后将一条记录插入审核表中.如果我在命令行上手动执行insert语句，则此方法效果很好.但是，当我的Web表单插入到表中时，插入到审计表中的所有值都为null.我在想这可能是权限问题，因此我将CURRENT_USER()和USER()添加到触发器中，以插入到审计表中.它表明触发器由"admin"用户执行，该用户是触发器的所有者.

I really hope someone can help me out with this as it's been bugging me for days. I have an after insert trigger on my users table that inserts a record into an audit table after every insert. This works fine if i'm on the command line manually executing the insert statement. However, when my web form inserts into the table then all the values inserted into the audit table are nulls. I'm thinking maybe it's a permission issue so i added the CURRENT_USER() and also USER() to the trigger to be inserted into the audit table. It's showing that the trigger is being executed by the "admin" user, which is the owner of the trigger.

这是我的触发器:

DELIMITER ^^
CREATE TRIGGER UsersInsert AFTER INSERT ON Users
FOR EACH ROW
BEGIN

INSERT INTO `users_audit` (`username`, `address`, `email`)
VALUES
(USER(), NEW.address, NEW.email);

END
^^

这将在审核表中插入以下内容:admin @ localhost，null，null

This will insert the following in the audit table: admin@localhost, null, null

以下是管理员用户的权限:

And here are the permissions for the admin user:

GRANT ALL PRIVILEGES ON `MyDB`.* TO 'admin'@'localhost' WITH GRANT OPTION

就像我说的那样，它可以从mysql命令行完美地工作.但是出于某种原因，从Web表单执行时，"NEW"值全为空.

Like i said, it works perfectly from the mysql command line. But for some reason the "NEW" values are all nulls when executing from a web form.

请帮助.预先感谢.

2013年2月6日更新:

UPDATE on 2/6/2013:

我尝试了类似的触发器，但是使用了"AFTER UPDATE"，它可以正常工作.因此，该问题与插入无关.这也证明这不是权限问题.

I tried a similar trigger but with "AFTER UPDATE" and it worked correctly. So the issue is isolated to inserting. And that also proves that it's no a permission problem.

推荐答案

好吧，在创建了不同类型的触发器并进行了试验之后，我发现插入触发器由于某种原因可能多次触发，实际上触发了insert和更新触发器，并且最后一行始终为null，这是唯一要插入的记录.真的很奇怪因此，无论如何，为了解决这个问题，我将代码更改为此，并且到目前为止，它没有任何问题:

Well, after creating different types of triggers and experimenting, i figured that the insert trigger might have been firing more than once for some reason, actually firing both insert and update triggers, and the last row always had nulls which was the only record being inserted. It's really strange. So anyway, to get around this i changed my code to this and it's working with no issues thus far:

DELIMITER ^^

CREATE TRIGGER UsersUpdate AFTER UPDATE ON users
FOR EACH ROW
BEGIN

IF NEW.username IS NOT NULL THEN

IF EXISTS (SELECT 1 FROM users_audit WHERE username = NEW.username) THEN
UPDATE users_audit SET `username` = NEW.username,  `FirstName` = NEW.FirstName, `LastName` = NEW.LastName, `address` = NEW.address, `email` = NEW.email WHERE username = OLD.username;
ELSE
INSERT INTO users_audit (`username`, `FirstName`, `LastName`, `address`, `email`)
VALUES (NEW.username, NEW.FirstName, NEW.LastName, NEW.address, NEW.email);
END IF;

END IF;

END
^^

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