MySQL Forums
Forum List  »  Triggers

Debug audit trigger design
Posted by: Nicola Farina
Date: January 07, 2021 11:07AM


I am an experienced pl/sql developer, now attempting to write a trigger in MySql 5.6 (on AWS) to debug a nasty problem.
In our system there is a table in which a couple of fields are updated to a non-legal value, but we do not understand which process is doing it (we have quite a few of them).
Putting a constraint on the field is not an option (it is a legacy system).
We would like to find out _which_ process is doing this faulty update.
In Oracle I would have written a before update trigger which would
1) collect information on the calling session (program_name, client etc) from the v$session table,
2) insert a record in a audit table, and finally
3) raise an exception to block the undesired update.

The only thing I was able to achieve was the 3) (raise an exception with signal state '45000'), but inserting into the audit table at the same time is not possible since MySql lacks the concept of autonomous transaction right?
I am looking for an advice, hoping the use case is clear.
What a experienced MySql admin/dev would do in this case?
Thanks a lot

Options: ReplyQuote

Written By
Debug audit trigger design
January 07, 2021 11:07AM
January 07, 2021 12:26PM
January 08, 2021 01:59AM
January 08, 2021 09:04AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.