Support

Home Forums Event Espresso Premium MySQL trigger after INSERT on wp_esp_registration – requires two inserts to trig

MySQL trigger after INSERT on wp_esp_registration – requires two inserts to trig

Posted: May 22, 2019 at 12:11 am


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

  • Support Staff

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

  • Support Staff

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 1 month, 3 weeks 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

  • Support Staff

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

  • Support Staff

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.

The support post ‘MySQL trigger after INSERT on wp_esp_registration – requires two inserts to trig’ is closed to new replies.

Have a question about this support post? Create a new support post in our support forums and include a link to this existing support post so we can help you.

Support forum for Event Espresso 3 and Event Espresso 4.
Documentation for EE3 and EE4
Documentation for Event Espresso 3

Documentation for Event Espresso 4

Status: closed

Updated by  a608237 1 month, 3 weeks ago ago

Topic Tags

Notifications

This topic is: resolved
Do NOT follow this link or you will be banned from the site!