Not the answer you need?
Register and ask your own question!

MySQL NEW. values are empty in AFTER INSERT trigger

sparksdrsparksdr EntrantCurrent User Role Beginner
I've been searching for 2 days on this one. I have a simple MySQL AFTER INSERT trigger to insert the new data into a 2nd table. However, when I run the trigger the 2nd table has all blank fields, except the foreign key ID from the 1st table....all other fields are empty. It seems the NEW. values in the insert trigger are blank when they should not be.

FIRST TABLE WITH THE AFTER INSERT TRIGGER:

CREATE TABLE IF NOT EXISTS `joom_cck_store_form_smart_mail_forward` (
`id` int(10) unsigned NOT NULL,
`mailbox_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`domain_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`forward_email_address` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`mailbox_comment` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`forward_to_persons_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`MailboxName` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

AFTER INSERT TRIGGER:

DELIMITER //

Create TRIGGER joom_cck_store_form_smart_mail_forward_after_insert
AFTER INSERT
ON joom_cck_store_form_smart_mail_forward FOR EACH ROW

BEGIN

DECLARE zDBname varchar(100);

-- Insert record into main smart mail forward system table
INSERT INTO zEmailForward(source_id, mailbox_name, domain_name, comment_text, recipient_email_address, recipient_name)
VALUES (NEW.id, NEW.mailbox_name, NEW.domain_name, NEW.mailbox_comment, NEW.forward_email_address, NEW.forward_to_persons_name);

END; //

DELIMITER ;

RESULT AFTER INSERTING A NEW ROW IN THE PRIMARY TABLE

The row in the primary table saves fine with all values filled in. The AFTER INSERT trigger runs and inserts a row in the second table (zEmailForward). However, only the source_id field from the prmary table has a value in the zEmailForward table; all other columns are blank in zEmailForward. The NEW. values in the trigger are all blank, even though they should not be.

===================================================================
PRIMARY TABLE (joom_cck_store_form_smart_mail_forward) ROW:
===================================================================
id MailboxName domain_name forward_email_address mailbox_comment forward_to_persons_name
10 Test domain.com [email protected] this is a test Test Person

===================================================================
2nd TABLE (zEmailForward) ROW CREATED BY THE AFTER INSERT TRIGGER:
===================================================================
ID source_id MailboxName domain_name recipient_email_address comment_text recipient_name
1 10

So you can see the ID from joom_cck_store_form_smart_mail_forward copied to the source_id of zEmailForward field just fine....but all other fields in zEmailForward are blank.

===================================
Database server information

Server: Localhost via UNIX socket Server type: Percona Server Server version: 5.6.31-77.0-log - Percona Server (GPL), Release 77.0, Revision 5c1061c Protocol version: 10 Server charset: UTF-8 Unicode (utf8)

==================================================

Any ideas why this is happening?

Thank you.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.