Is it possible a trigger on a select statement with MySQL?

Question

Hi.

I know that triggers can be used on insert, update and delete, but what about a trigger (or sort of) on a select statement. I want to use a trigger to insert data on a table B when it is selected an existent record on a table A, it could be possible?.

Thanks in advance.

Answer 1

It is not possible in the database itself.

However there are monitoring/instrumentation products for databases (e.g. for Sybase - not sure about MySQL) which track every query executed by the server, and can do anything based on that - usually store the query log into a data warehouse for later analysis, but they can just as well insert a record into table B for you, I would guess.

Answer 2

Not exactly a trigger, but you can:

CREATE FUNTION myFunc(...) BEGIN INSERT INTO myTable VALUES(...) END;

And then

SELECT myFunc(...), ... FROM otherTable WHERE id = 1;

Not an elegant solution, though.

Answer 3

You should design your application so that database access occurs only through certain methods, and add this monitoring you need in those methods.

Answer 4

You can write an application which will be monitoring the query log and doing something when a select occurs. A pretty crude way to solve the problem though...