MySQL "After Insert" Trigger keeps inserting nulls

Question

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.

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

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.

Please help. Thanks in advance.

UPDATE on 2/6/2013:

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.

Could be a problem with users rights...do you use the same MySQL-user on the command line and in the web application? — Jan Petzold
– Jan Petzold, Commented Feb 6, 2013 at 7:29
I also saw this post and checked "autocommit" and found that it's turned on by default on my system, so that didn't help: stackoverflow.com/questions/2921546/… — Amito
– Amito, Commented Feb 6, 2013 at 18:10

Devart · Accepted Answer · 2013-02-06 08:53:58Z

0

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

You may change INSERT statement in the trigger -

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

and sure that trigger works properly.

answered Feb 6, 2013 at 8:53

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Amito Over a year ago

Thanks for the reply. Your statement works fine just as i described in my original post. Notice in my post that the value for "USER()" was inserted to the audit table. The problem is with using the values provided by "NEW".

Devart Over a year ago

If NEW values are NULL, then you are trying to insert NULL values into Users table. Select data from Users table.

Amito · Accepted Answer · 2013-02-06 19:49:13Z

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

Collectives™ on Stack Overflow

MySQL "After Insert" Trigger keeps inserting nulls

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related