Can I launch a trigger on select statement in mysql?

Question

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?

Solution 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).

Solution 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.

Solution 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.

Comments

[ypercubeᵀᴹ]

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

[Jeff Lowery]

Updating every row on a select would cause performance headaches for bulk operations. Best to specifically update a last_accessed timestamp for operations where it makes sense to do so.

[Danny Holstein]

I'd like to quibble with the "weird" characterization. Specifically, if one is accessing controlled information (e.g. labels for manufacturing) where we want to control the number of times a document stored in the DB is accessed or printed, this is exactly a feature I want. If information control is wrong (weird), I don't want to be right.

[ypercubeᵀᴹ]

@DannyHolstein ok, "weird" was not the best choice. Does "rare" sound better?

[Danny Holstein]

I should put in a feature request for that ability.

[Danny Holstein]

I tried to implement the solution with a VIEW and stored function, when trying to do an INSERT into the table that tracks access, it can't, because that table is already open and is locked.

[ypercubeᵀᴹ]

@DannyHolstein you can post a new question with your implementation and the issue that happens (and add a link to this one).

[Danny Holstein]

Kinda, but I'm restricted in using multiple lines. This is a dumb printer driver which will only look at views. I did come up with a workaround to retire entries at scheduled intervals, but I found more bugs in the Bradly printer software that makes it completely unusable, and no one there wants to fix it.