Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Send an http request in mysql trigger after insertion in a table

I am working in PHP.I have to created a mysql trigger which fires an http request after insertion on table.Below is the code.

DELIMITER @@
CREATE TRIGGER Test_Trigger
AFTER INSERT ON insertsms
FOR EACH ROW
BEGIN
  SET @tt_json = (SELECT json_object(id,addtime,title) 
                  FROM insertsms WHERE id = NEW.id LIMIT 1);
  SET @tt_resu = (SELECT http_put(CONCAT('--url localhost--')));
END;
@@
DELIMITER ;

But I am getting errors like

Message: SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION emg.json_object does not exist

Message: SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION emg.http_put does not exist

How to remove this error? I was not able to download the supporting files containing these functions.I have tested in localhost.Is there any other way to achieve my requirement? Please anyone help me..

like image 999
Elizabeth Joshy Avatar asked Dec 27 '14 06:12

Elizabeth Joshy


People also ask

How can you access the new value for the address inside the trigger in MySQL?

Answer: NEW. qty references the qty on the table that the trigger is set on, not the table that is being updated. CREATE TRIGGER after_sales_insert AFTER INSERT ON sales FOR EACH ROW BEGIN UPDATE products SET qty = qty - NEW.

Can a trigger include select statement?

The trigger event that initiates the trigger action can be an INSERT, DELETE, UPDATE, or a SELECT statement.


1 Answers

Although it's technically possible I'd strongly discourage you from going this route for several reasons:

  1. Using UDFs is a security risk on its own. UDFs are available to all database users - you cannot grant EXECUTE privileges for them.

  2. Doing any non-transactional operations in a trigger is simply wrong. Data changes made by DML statement (in your case it's an update) can and will be rolled back in a real world scenario. You won't be able to undo your http calls.

  3. You're prolonging the time for insert transaction possibly causing lock-wait-timeouts for other update/insert operations.

Highly recommended reading:

  • The Trouble with Triggers

Now most likely what you need is a work queue e.g. beanstalked. Using such specialized middleware is much better than organizing queues with database.

like image 145
peterm Avatar answered Sep 30 '22 07:09

peterm