MySQL Forums
Forum List  »  Triggers

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

Hello

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
Bye
Nicola

Options: ReplyQuote


Subject
Views
Written By
Posted
Debug audit trigger design
220
January 07, 2021 11:07AM
72
January 07, 2021 12:26PM
68
January 08, 2021 01:59AM
74
January 08, 2021 09:04AM


Sorry, only registered users may post in this forum.

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.