database triggers – how use

  • Resolved lrydant

    (@lrydant)


    2 years, 5 months ago

    Read documentation and I’m not understanding how to get a trigger to work.
    1. I used your example, substituting one of my tables for dept.

    CREATE TRIGGER prevent_updates_on_member BEFORE UPDATE ON member
    FOR EACH ROW
    BEGIN
    SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘Update failed…’;
    END;
    /

    2. Saved the trigger.

    3. Was able to edit and save a record in member table.

    Thank you.

Viewing 7 replies - 1 through 7 (of 7 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi L,

    Did you execute the code? Or just save it?

    When you copy this code into the Query Builder and press the execute button, the trigger is created. But this works only if your DBMS supports triggers and you have the right to create them.

    Does this help?

    Best regards,
    Peter

    Thread Starter lrydant

    (@lrydant)

    I tried executing. Get error WordPress database error: You do not have SUPER privilege and binary logging is enabled. Web hosting support said they support triggers but I have all the privileges they grant and can’t do anything else. Support suggested that maybe SSL certificate (haven’t done yet) would solve issue but doubtful.

    So maybe I have to find new hosting service?

    Thank you.

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    That’s bad! Nothing you can do against missing privileges.

    SSL won’t solve this. Switching to another provider could, but for a price. How important is this for you? Are there any other solutions? Maybe you can hire a small linux server for a few dollars per month and manage your own database…?

    Best regards,
    Peter

    Thread Starter lrydant

    (@lrydant)

    After searches and inquiries, probably easier to ask. Any suggestions for hosting providers that permits the user to work with table triggers?

    Thank you.

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi L,

    I think you can do this with Google Cloud or AWS, but that’s a rather expensive solution. You could rent a small linux vps. But you need to manage the server yourself. Here are some examples: https://www.hostingadvice.com/how-to/cheap-linux-vps/

    Maybe there are other solutions? What is your goal?

    Thanks,
    Peter

    Thread Starter lrydant

    (@lrydant)

    Checked hosting services and A2 Hosting permits triggers.

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Good te know! ??

    Thanks for sharing,
    Peter

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘database triggers – how use’ is closed to new replies.