Can I launch a trigger on select statement in mysq

问题:

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?

回答1:

Short answer is No. Triggers are triggered with INSERT, UPDATE or DELETE.

Possible solution for this. rather rare scenario:

• 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 directly 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).

回答2:

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.

回答3:

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.