MySQL“插入后”触发器继续插入空值

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.

我真的希望有人可以帮我解决这个问题,因为它已经困扰了我好几天。我的users表上有一个后插入触发器,它在每次插入后将记录插入到审计表中。如果我在命令行上手动执行insert语句,这可以正常工作。但是,当我的Web表单插入表中时,插入审计表的所有值都为空。我想也许这是一个权限问题,所以我将CURRENT_USER()和USER()添加到要插入审计表的触发器中。它显示触发器正由“admin”用户执行,该用户是触发器的所有者。

Here is my 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
^^

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

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

And here are the permissions for the admin user:

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

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

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.

就像我说的,它从mysql命令行完美运行。但由于某种原因,从Web表单执行时,“NEW”值都为空。

Please help. Thanks in advance.

请帮忙。提前致谢。

UPDATE on 2/6/2013:

2013年2月6日更新:

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.

我尝试了一个类似的触发器,但“更新后”,它正常工作。所以这个问题与插入有关。这也证明了这不是一个许可问题。

2 个解决方案

#1

Looks like a problem with web form. Check values which are inserted into Users table. Are they NULL?

看起来像Web表单的问题。检查插入Users表中的值。他们是NULL吗?

You may change INSERT statement in the trigger -

您可以在触发器中更改INSERT语句 -

INSERT INTO `users_audit` (`username`, `address`, `email`)
VALUES (USER(), 'temp address', 'temp email');

and sure that trigger works properly.

并确保触发器正常工作。

#2

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
^^

#1