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..
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.
The trigger event that initiates the trigger action can be an INSERT, DELETE, UPDATE, or a SELECT statement.
Although it's technically possible I'd strongly discourage you from going this route for several reasons:
Using UDFs is a security risk on its own. UDFs are available to all database users - you cannot grant EXECUTE privileges for them.
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.
You're prolonging the time for insert transaction possibly causing lock-wait-timeouts for other update/insert operations.
Highly recommended reading:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With