chris85 chris85 - 1 year ago 104
MySQL Question

Finding Query that Triggered a Trigger

Is it possible with a trigger to get the query or primary id of the query that triggered it?

Currently we roughly have:

Delete from Table1 where id = 1

is the primary id of that table,
is just an example record)

I want to log the query or the
of the row being deleted (because someone is incorrectly deleting records (not malicious)). This seemed like it'd be an easy process with a
but I can't figure out how to address the parent query that caused the
to fire.

I planned to have:

CREATE TRIGGER Table1_Row_Being_Deleted
INSERT INTO deleted_Table1 (deleting_date, tableid) values(now(), ?);

But I don't know what to put for the
. All other threads and docs I've seen have had static values, or affected every row in the table.

Answer Source

There is no direct link between the trigger and the query that triggered the trigger. But you can use the performance schema to search for all active queries and log them. One of them should be the caller's query.

create trigger Table1_Row_Being_Deleted after delete on Table1 
for each row
  insert into deleted_Table1(id, dt, user, qry) 
  select, now(), user(), performance_schema.events_statements_current.sql_text
  from performance_schema.events_statements_current; 

This will log every active query, and thus a lot of noise, because the correct query is not known. Since it can be an indirect query (e.g. from a procedure), the correct query will not always have a distinctive part like 'delete from table1' in it. So look for common queries that will be in the logtable every time your problem occurs.

I used an after delete trigger here, so it will only log when delete succeeded, you might want to use a before delete trigger to log even if the delete will fail later (e.g. because of foreign key constraint). (and the whole row old) contains the values the row had before it was deleted (so you can still use it here to log the entry).

The performance schema and the events_statements_current-log are usually enabled by default. Check the result from

select * from performance_schema.events_statements_current;

It should at least contain a row with this select-query itself (since that was an active query at that time). If it is empty (or you have no permission to use that or it doesn't exists), you should check if show variables like 'performance_schema'; will show ON. And you might need to set permissions or some logging options, see Query Profiling Using Performance Schema.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download