Is it possible to automate a workflow in a WordPress website such that when a new row is added to an underlying MySql database table an external WebHook is invoked?
I recently converted a website from SquareSpace to to WordPress because I needed to use plug-ins and more customization.
I’ve got a plug-in that creates new records in it’s MySql table and it sends out an email with each time.
Using Zapier email parser and Zapier integration via Webhook to a SaaS app, I’ve got everything working, but Zapier is expensive to use as I have more than 100 zaps per month.
Since the ultimate destination is a SaaS with a published Webhook interface, I would like to remove the email-to-Zapier part of the workflow and just have WordPress directly invoke the Webhook when a new record is added.
Haven’t done any MySql work in a long time, wondering if there is a straightforward way to create a trigger (or equivalent) in MySql itself to call the Webhook whenever a new row is added to a table.
Note: the plug-in I am using is commercial so I don’t have access to the source code to make the changes directly in the plug-in itself (which would be the ideal place for this automation).
MySQL has triggers you can add to tables, but depending on which table you may have unexpected results - Wordpress tends to add lines and remove them rather than update in some places.
Is it possible to execute more than SQL commands in a MySQL trigger? I was hoping to avoid doing anything in WP php and just have a script or stored procedure that could run as a trigger to make REST API calls with javascript or curl.
You could, but it’s not advised as then the entire database transaction has to wait until your script is finished - which is not ideal (read: a very bad idea). I’d personally make a script with a separate table. Use the MySQL trigger to add the IDs of all entries from the table you want to watch to your “script does stuff” table, then have your script run at a regular interval and either update the entries to mark them as “done”, or to remove the entries from the special table for this.
Using MySQL triggers is fairly complex compared to the rest of the WordPress PHP api.
I’d like to know which database table you need to interact with. If it’s one of the standard WordPress tables there’s a very big chance that the internal API have a trigger (action or filter) for it. And if so, there’s a fair chance that the free plugin WunderAutomation can solve the problem for you.
After considering various options, I decided to integrate using a Zapier workflow instead.
No programming and no intricate dependency on the internals of MySql and WordPress.
Downside was I had to upgrade from a free account, but at my client billing hourly rates, the $20/month I am now spending is peanuts compared to the time saved and potential technical debt I would be creating.