MySQL function/script to send data to a webhook when table XYZ is updated

The options are:

• Modify the client application code that updates the database. After the transaction commits, then notify the webhook from the client app. It's important to do it after the commit, or else you could notify the webhook about data that is subsequently rolled back.

• Use a binary log tailer, for example Debezium. This has a couple of advantages: changes are added to the binary log only on commit, so the binlog tailer never sees changes that were rolled back. This does not require you to modify your client code. It works even if data is updated by several clients, or even by a human using an interactive client.

You are correct that you cannot do this with pure MySQL: unless you add a somewhat dodgy extension to your MySQL server it has no way to originate any operation.

You could create a trigger on UPDATE (and perhaps another on INSERT) on that XYZ table. The trigger would INSERT a row into a new table called, maybe webhook_queue.

A separate webhook program would, running every few seconds, SELECT, then DELETE all rows from that webhook_queue table, then send each webhook. There's obviously a latency problem with this approach: webhooks won't be sent until the webhook program wakes up and does its work.

If that won't work for you, you probably have to modify your application code to invoke the webhook as it UPDATEs each row.

You can use MySQL PROCEDURE and cURL to send request to your script anywhere in web or on localhost webserver.

DELIMITER //

CREATE PROCEDURE notify_webhook()
BEGIN
  DECLARE url VARCHAR(255);
  SET url = 'http://example.com/webhook';

  SET @json = JSON_OBJECT('event', 'delete', 'timestamp', NOW());

  SET @result = curl('POST', url, @json);
END//

DELIMITER ;

And then create a TRIGGER in your table

CREATE TRIGGER trigger_after_delete
AFTER DELETE ON your_table
FOR EACH ROW
BEGIN
  CALL notify_webhook();
END;

If you need to provide id to procedure you can write this

CREATE PROCEDURE notify_webhook(IN deleted_id INT)

SET @json = JSON_OBJECT('event', 'delete', 'timestamp', NOW(), 'delete_id', deleted_id);

IN TRIGGER

CALL notify_webhook(OLD.id);

This is example for delete trigger, but you can ajust it to AFTER UPDATE