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 test@testemail.com 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.