Calling an url from a trigger in mysql

by

To trigger an external action, you have to use a UDF – it’s the only way for mysql to tell something to the “outside world”. The only alternative is an external agent polling the DB constantly – which is an inferior solution.

As for the choice of a UDF,

  • to minimize load on the DB, it should probably be something that finishes quickly (note that UDFs run synchronously).
  • So, unless the installation is sufficiently small-scale, it’s going to merely notify an external agent of the event. This also minimizes error handling at the DB side.
    • Otherwise, if you don’t (yet) care, you can e.g. just spawn curl for all it’s worth.

Ways that come to mind:

  • spawn a small program – e.g. touch some file which the agent watches. There’s an existing sys_exec that uses system() (with all due considerations).
  • IPC (signal is the simplest; with others, you can pass additional information but it requires more setup)

As the sys_exec‘s source shows, it’s not so hard to write a UDF, so you aren’t really limited to what’s already available (this may explain why lib_mysqludf_sys is so limited: if you need something better, it’s sufficiently easy to write a task-specific function). The current docs are at 26.4.2 Adding a New User-Defined Function – MySQL 5.7 Reference Manual.

More Related Contents:

  1. Throw an error preventing a table update in a MySQL trigger
  2. How to abort INSERT operation in MySql trigger?
  3. MySql Error: Can’t update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger
  4. MySQL Trigger after update only if row has changed
  5. mysql after insert trigger which updates another table’s column
  6. How to program a MySQL trigger to insert row into another table?
  7. TRIGGERs that cause INSERTs to fail? Possible?
  8. Use a trigger to stop an insert or update
  9. MySQL Fire Trigger for both Insert and Update
  10. MySQL trigger On Insert/Update events
  11. Can a MySQL trigger simulate a CHECK constraint? [duplicate]
  12. MySQL error 1436: Thread stack overrun, with simple query
  13. MySQL trigger to update a field to the value of id
  14. How can I set a maximum number of rows in MySQL table?
  15. mysql trigger stored trigger is already used by statement which invoked stored trigger
  16. My MySQL trigger doesn’t work, simple syntax, not complicated
  17. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  18. MySQL Trigger: Delete From Table AFTER DELETE
  19. Is it Possible to Enforce Data Checking in MySQL using Regular expression
  20. Can I launch a trigger on select statement in mysql?
  21. Using MySQL triggers to log all table changes to a secondary table
  22. MySQL: How do I use delimiters in triggers?
  23. MySQL listen notify equivalent
  24. MySQL disable all triggers
  25. how to delete data from table A besides the customer service and director data in table B?
  26. MySQL: multiple tables or one table with many columns? [closed]
  27. How to get first day of every corresponding month in mysql?
  28. MySQL LEFT JOIN 3 tables
  29. Accessing a mysql database from external host/ip? (ie: mysql workbench)
  30. MySQL table -> Can you return the same row multiple times, in the same query?

Leave a Comment