|
a608237
|
May 22, 2019 at 12:11 am
Each time someone registers for an event (wp_esp_registration), I want to insert various user profile data (which I’ve compiled from a view [myView]) into a custom table (mytable). I created a trigger
CREATE DEFINER=wordpress@localhost TRIGGER write_to_mytable AFTER INSERT ON wp_esp_registration FOR EACH ROW BEGIN
INSERT INTO mytable (EventID,Event_Start,UserID,first_name,nickname,Ticket)
SELECT EVT_ID,Event_Start,ID,first_name,nickname,ANS_value
FROM myView
WHERE NOT EXISTS (
SELECT * FROM mytable
WHERE vs_mytable.EventID = myView.EVT_ID
AND mytable.UserID= myView.ID);
The idea is to insert UserID and EVT_IDs into mytable from myView where they don’t exist. It is not working though. In my test, it seems I need to register twice as the same user to the same event. In this situation, the data from the first registration is inserted (trigger) into mytable. And, the data from the second (duplicate) registration does not get inserted, presumbaly because that UserID, EventID combination now exists in MyTable. How can I get the trigger to work after the FIRST insert to wp-esp_registration?
|
|
a608237
|
May 22, 2019 at 12:15 am
Running the INSERT statement outside the trigger works everytime after each registration
|
|
Josh
|
May 22, 2019 at 7:45 am
Hi,
A topic like this one does fall outside the scope of support. That said, what’s the primary key on that table?
|
|
a608237
|
May 22, 2019 at 8:38 am
Hi Josh,
I appreciate your assist on this, it’s the longest I’ve ever been stuck on one problem.
The table with the trigger is wp_esp_registration. The primary key is REG_ID.
The view that contains data written into mycustomtable is a view with no primary key.
mycustomtable (where data from view is inserted) has a primary key ‘id’
|
|
Tony
|
May 22, 2019 at 9:35 am
Hi there,
In my test, it seems I need to register twice as the same user to the same event. In this situation, the data from the first registration is inserted (trigger) into mytable. And, the data from the second (duplicate) registration does not get inserted, presumbaly because that UserID, EventID combination now exists in MyTable.
My apologies, but I’m a little confused by this.
That’s what happens currently, or that’s the expected outcome? That sounds like your expected outcome.
And, the data from the second (duplicate) registration does not get inserted, presumbaly because that UserID, EventID combination now exists in MyTable.
Can you post how you’ve defined the columns in your view?
Something to note is the INSERT on esp_registration is done as soon as the user selects the tickets and submits the form, this is before they’ve entered ANY details into EE. So, on a new registration for a new user your definitely not going to have a value in ANS_value at that point as they wouldn’t been to that stage yet (assuming its an answer value from an EE question).
Running the INSERT statement outside the trigger works everytime after each registration
At what point are you running the INSERT then? After the registration is complete and you’ve hit the thank you page? (If so that’s way after the esp_registration insert)
-
This reply was modified 5 years, 6 months ago by
 Tony. Reason: Mangled reply!
|
|
a608237
|
May 22, 2019 at 9:59 am
Here is the view I use to write into mycustomtable once a trigger fires from wp_esp_registration. It aggregates the user’s personal information and event information:
CREATE
ALGORITHM = UNDEFINED
DEFINER = <code>wordpress</code>@<code>localhost</code>
SQL SECURITY DEFINER
VIEW <code>evt_registrations_detail</code> AS
SELECT
<code>wp_esp_registration</code>.<code>REG_code</code> AS <code>REG_code</code>,
<code>wp_esp_registration</code>.<code>STS_ID</code> AS <code>Status</code>,
<code>wp_esp_datetime</code>.<code>DTT_EVT_start</code> AS <code>Event_Start</code>,
<code>wp_esp_attendee_meta</code>.<code>ATT_email</code> AS <code>ATT_email</code>,
<code>wp_users</code>.<code>ID</code> AS <code>ID</code>,
<code>user_detail</code>.<code>first_name</code> AS <code>first_name</code>,
<code>user_detail</code>.<code>nickname</code> AS <code>nickname</code>,
<code>user_detail</code>.<code>last_name</code> AS <code>last_name</code>,
<code>user_detail</code>.<code>Gym</code> AS <code>Gym</code>,
<code>wp_esp_registration</code>.<code>EVT_ID</code> AS <code>EVT_ID</code>,
<code>wp_posts</code>.<code>post_title</code> AS <code>post_title</code>,
<code>wp_esp_answer</code>.<code>ANS_value</code> AS <code>ANS_value</code>
FROM
((((((<code>wp_esp_answer</code>
JOIN <code>wp_esp_registration</code> ON ((<code>wp_esp_answer</code>.<code>REG_ID</code> = <code>wp_esp_registration</code>.<code>REG_ID</code>)))
JOIN <code>wp_esp_datetime</code> ON ((<code>wp_esp_registration</code>.<code>EVT_ID</code> = <code>wp_esp_datetime</code>.<code>EVT_ID</code>)))
JOIN <code>wp_posts</code> ON ((<code>wp_posts</code>.<code>ID</code> = <code>wp_esp_registration</code>.<code>EVT_ID</code>)))
JOIN <code>wp_esp_attendee_meta</code> ON ((<code>wp_esp_registration</code>.<code>ATT_ID</code> = <code>wp_esp_attendee_meta</code>.<code>ATT_ID</code>)))
JOIN <code>wp_users</code> ON ((<code>wp_esp_attendee_meta</code>.<code>ATT_email</code> = <code>wp_users</code>.<code>user_email</code>)))
JOIN <code>vsme_user_detail</code> ON ((<code>wp_users</code>.<code>ID</code> = <code>vsme_user_detail</code>.<code>user_id</code>)))
WHERE
(<code>wp_esp_registration</code>.<code>STS_ID</code> = 'RAP')
Here is the current edition of the trigger I’ve concocted. Rather than selecting from the view above, I’ve tried to re-create a simpler version with a derived table in the trigger itself:
CREATE DEFINER=<code>wordpress</code>@<code>localhost</code> TRIGGER <code>weigh_in_insert</code> AFTER INSERT ON <code>wp_esp_registration</code> FOR EACH ROW BEGIN
INSERT INTO mycustomtable (EventID,UserID,Ticket)
SELECT REG_code,EVT_ID, ID, ANS_value FROM
(SELECT wp_esp_registration.REG_code,
wp_esp_registration.EVT_ID as EVT_ID,
evt_registrations_detail.ID as ID,
wp_esp_answer.ANS_value as ANS_value
FROM wp_esp_answer INNER JOIN wp_esp_registration
ON wp_esp_answer.REG_ID = wp_esp_registration.REG_ID
INNER JOIN evt_registrations_detail
ON wp_esp_registration.REG_code=evt_registrations_detail.REG_code
) AS t1;
END
The desired behaviour is for new registration values to be written into mycustomtable after each registration. Before, it was writing after every second registration by the same user to the same event. BUT, with this current trigger posted above, it is INSERTing all the existing records from evt_registrations_detail into mycustomtable(undesired) and not INSERTing the recently added registration into mycustomtable (desired).
|
|
a608237
|
May 22, 2019 at 10:03 am
View:
CREATE
ALGORITHM = UNDEFINED
DEFINER = wordpress@localhost
SQL SECURITY DEFINER
VIEW evt_registrations_detail AS
SELECT
wp_esp_registration.REG_code AS REG_code,
wp_esp_registration.STS_ID AS Status,
wp_esp_datetime.DTT_EVT_start AS Event_Start,
wp_esp_attendee_meta.ATT_email AS ATT_email,
wp_users.ID AS ID,
user_detail.first_name AS first_name,
user_detail.nickname AS nickname,
user_detail.last_name AS last_name,
user_detail.Gym AS Gym,
wp_esp_registration.EVT_ID AS EVT_ID,
wp_posts.post_title AS post_title,
wp_esp_answer.ANS_value AS ANS_value
FROM
((((((wp_esp_answer
JOIN wp_esp_registration ON ((wp_esp_answer.REG_ID = wp_esp_registration.REG_ID)))
JOIN wp_esp_datetime ON ((wp_esp_registration.EVT_ID = wp_esp_datetime.EVT_ID)))
JOIN wp_posts ON ((wp_posts.ID = wp_esp_registration.EVT_ID)))
JOIN wp_esp_attendee_meta ON ((wp_esp_registration.ATT_ID = wp_esp_attendee_meta.ATT_ID)))
JOIN wp_users ON ((wp_esp_attendee_meta.ATT_email = wp_users.user_email)))
JOIN vsme_user_detail ON ((wp_users.ID = vsme_user_detail.user_id)))
WHERE
(wp_esp_registration.STS_ID = ‘RAP’)
Current Edition of trigger. Inserting existing records and not newly submitted registration record (undesired)
CREATE DEFINER=wordpress@localhost TRIGGER weigh_in_insert AFTER INSERT ON wp_esp_registration FOR EACH ROW BEGIN
INSERT INTO mycustomtable (EventID,UserID,Ticket)
SELECT REG_code,EVT_ID, ID, ANS_value FROM
(SELECT wp_esp_registration.REG_code,
wp_esp_registration.EVT_ID as EVT_ID,
evt_registrations_detail.ID as ID,
wp_esp_answer.ANS_value as ANS_value
FROM wp_esp_answer INNER JOIN wp_esp_registration
ON wp_esp_answer.REG_ID = wp_esp_registration.REG_ID
INNER JOIN evt_registrations_detail
ON wp_esp_registration.REG_code=evt_registrations_detail.REG_code
) AS t1;
END
|
|
a608237
|
May 22, 2019 at 10:29 am
inserting ANS_value doesn’t seem to be the issue, it inserts the ANS_value. The problem is that it is inserting all the existing records from evt_registrations_detail (view) except for the newly added registration
|
|
Tony
|
May 22, 2019 at 10:47 am
Current Edition of trigger. Inserting existing records and not newly submitted registration record (undesired)
Yeah, so I think the problem is that registrations are inserted into the database earlier than your expecting. They are inserted really early and updated as the user steps through the registration steps, also your registrations can only be approved once they been finalized at the end of the process, which is way after the INSERT call.
In order for a registration to have a status of Approve (wp_esp_registration.STS_ID = ‘RAP’) it’s been inserted early on, the attendee has run through all of the reg steps and finalized and the user paid (or they’ve finalized with a default registration status of Approved) but either way, you’ll only pull that registration on the NEXT insert which is why you have to register twice.
I’m not sure how you can work around that in the above, you will always be at least one registration behind.
|
|
a608237
|
May 22, 2019 at 10:50 am
Thanks Tony,
I think I may abort and use a stored procedure in that case. The sucky thing is it’ll require more manual intervention, whereas in a trigger, it’ll do what it needs after each registration (ideally)
|
|
a608237
|
May 22, 2019 at 10:54 am
you’re right.. i see that registrants have a status of “RIC” before the full registration process is completed.
|
|
Tony
|
May 22, 2019 at 10:56 am
You could hook into the thank you page, use the transaction object passed to the hooks on that page to pull pretty much everything you need from Event Espresso and then add the record there.
You’ll obviously need some additional checks to confirm the user hasn’t refreshed or revisits etc, but it’s doable.
Take a look at the AHEE__thank_you_page_overview_template__top hook, its pass the transaction object.
Then use our models to pull everything you need (or do it directly if you want to): https://github.com/eventespresso/event-espresso-core/tree/master/docs/G–Model-System
|
|
a608237
|
May 22, 2019 at 12:13 pm
just to put some finality into this, I was able to achieve my desired output by
1.changing the ON INSERT TRIGGER into an ON UPDATE TRIGGER
Within the trigger:
2. INSERTING REG_code into the writee table
3. UPDATE the columns on the writee table from myView (when User finalizes registration) WHERE myView.REG_code=writee.REG_code
When the User registers for an event but have not yet to finalize, EE inserts a row into wp_esp_registration with STS_ID=’RIC’. On finalization, EE updates RIC to RAP. During the update, the trigger fires and inserts the REG_code into the writee table then updates the same row with values from MyView (which now contains the new record because registration is finalized and ANS_Value has been submitted[see posts above])
Thanks for the insights.
|
