Can I launch a trigger on select statement in mysql?
I am trying to run an INSERT statement on table X each time I SELECT any record from table Y is there anyway that I can accomplish that using MySQL only?
Something like triggers?
Short answer is No. Triggers are triggered with INSERT, UPDATE or DELETE.
Possible solution for this (weird) senario:
- First, write some stored procedures that do the SELECTs you want on table X.
- Then, restrict all users to use only these stored procedures and do not allow them to use SELECT on table X.
- Then alter the stored procedures to also call a stored procedure that performs the action you want (INSERT or whatever).
Nope - you can't trigger on SELECT - you'll have to create a stored procedure (or any other type of logging facility - like a log file or what ever) that you implicitly call on any query statement - easier if you create a wrapper that calls your query, calls the logging and returns query results.
If you're trying to use table X to log the order of SELECT queries on table Y (a fairly common query-logging setup), you can simply reverse the order of operations and run the INSERT query first, then run your SELECT query.
That way, you don't need to worry about linking the two statements with a TRIGGER: if your server crashes between the two statements then you already logged what you care about with your first statement, and whether the SELECT query runs or fails has no impact on the underlying database.
If you're not logging queries, perhaps you're trying to use table Y as a task queue -- the situation I was struggling with that lead me to this thread -- and you want whichever session queries Y first to lock all other sessions out of the rows returned so you can perform some operations on the results and insert the output into table X. In that case, simply add some logging capabilities to table Y.
For example, you could add an "owner" column to Y, then tack the WHERE part of your SELECT query onto an UPDATE statement, run it, and then modify your SELECT query to only show the results that were claimed by your UPDATE:
UPDATE Y SET owner = 'me' WHERE task = 'new' AND owner IS NULL; SELECT foo FROM Y WHERE task = 'new' AND owner = 'me';
...do some work on foo, then...
INSERT INTO X (output) VALUES ('awesomeness');
Again, the key is to log first, then query.