Database Trigger message to WPDA

  • Resolved measy

    (@measy)


    2 years, 10 months ago

    Hi Peter,
    for my TEEST business case, I’m still “fighting” with the issue I mentioned in a previous topic of mine (sorry, but it’s crucial to the adoption of WPDA in my project).
    — BUSINESS REQ. RECAP:
    assume (sort of SMS demo) that you engage free-lance Teachers (Contract: 100$/h, 30h, 2yrs duration) multiple times via Agreements.
    As an accountant, each time you create a new Agreement, you need to check that your contractA is valid (from/to dates) and residual capacity is greater than the new agreement requirement.
    — MY TECH SOLUTION:
    I can create a DB trigger to reduce capacity after_insert of a new Agreement and to check dates, but:
    what if my last Agreement requirement is bigger than the residual Contract?
    I should prevent to save the new Agreement and to notify the User, but – due to my short knowledge of WP/Php/JScripting… – I don’t know how to notify the WPDA User (Back End Agreements project page) of the “ERROR: unable to save due to Contract…”.
    In other words, how to implement the same/similar way WPDA notify the users with “Data successfully saved…” or “Nothing to save”.

    Thanks in advance.
    Marco

Viewing 5 replies - 1 through 5 (of 5 total)

  • I wonder if you aren’t expecting too much of WPDA? There is another excellent plugin named Gravity Forms. It has some excellent add ins that interact with MySQL.

    you could create a form, pre-populate it with data, validate the form data with the pre-existing data (called conditional expressions) and then, if the form is correct it will update your databases and optionally send emails to any number of recipients. It is very powerful and manages “fill-in the form” well

    It’s not free but well worth the license fee for building great front-ends. It can even collect payment.

    https://www.gravityforms.com/

    Thread Starter measy

    (@measy)

    Hi Charles,
    thanks for your reply.
    Yes, I know gravityforms, but still prefer WPDA.

    I agree my Test is not “flat”, but what I’m looking for is simply a better communication between the Database and WPDA, which is Data-centric indeed.
    Any business-customer will pay the premium WPDA, accordingly.

    Regards.

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Marco, Charles,

    It is relatively simple to generate an error from a trigger and display it on a WPDA page. The following simple trigger will prevent updates on table dept:

    CREATE TRIGGER prevent_updates_on_dept BEFORE UPDATE ON dept
   FOR EACH ROW
   BEGIN
	SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Update failed...';
   END;
/

    On failure it return error: Update failed…

    You can change this error to anything you like. When you try to update a row in table dept with this trigger enable you will get the normal WPDA error message, extended with the error message from the trigger like this:
    ERROR: Saving changes to database failed [Update failed…]

    Your custom error message is added between the square brackets.

    Here are two screenshots showing the result:
    https://ibb.co/1QbzPW0
    https://ibb.co/h9f0jTn

    The first image shows the result on a production server where I presume you have debug mode disabled. The second image shows the result with debug mode on, which I have enabled by default on my development computer.

    Okay, I know this should be documented. Sorry! There are tons of small feature never documented. I just don’t have the time…

    I will add a page to the Query Builder documentation.

    Hope this helps,
    Peter

    Thread Starter measy

    (@measy)

    Peter,
    simply “music to my ears”.
    I’ll test it and let you know asap.

    Thank you so much!

    Thread Starter measy

    (@measy)

    Hi Peter/Charles,
    jut to let you know: IT WORKS!

    Thanks for your timely suggestion.
    Best regards,
    Marco

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Database Trigger message to WPDA’ is closed to new replies.