Tracking and Loging User Changes to Data using MySQL database

[clivevdmescht]

I would like to keep an audit trail of user activity on the system with regards to updates and inserts and desided to do this using triggers in MySQL 5.5

I have just realised that this would not be possible as the actual userid (PID) for the database is hardcoded in Alpha5 connection string....Am I missing something here and has anybody got an idea of how to do this?

I'm using the AlphaToGo web template and as far as I understand all access is linked to the web_users table, but this is not related to the actual database conenction.

Hope somebody is able to point me in the right direction.

[Steve Wood]

If you are knowledgable about triggers that is probably the best place for this because it is most central. But the database does not know the userid, etc. so you cannot capture WHO made the change (I could be wrong, not a trigger expert).

Personally (not being a trigger expert) I would place code in the CanInsertRecord and CanUpdateRecord events in the grids, storing audit info to a database or text file. In fact I might use one of the client side events like afterRowSubmit because you can get the OLD and NEW values for each field.

Note - website access is controlled by the table named websecurity_users, not web_users.

If you need to store the userid as part of your log, go to the login.a5w page an uncomment the code that looks like this:

Code: [Select]

<%a5
'Template Note - this code places the Username (userid) in a session variable.
'Typically used to filter records by the login value.
'Uncomment below only if you need this value in a session variable for your application
'if eval_valid("submitbutton")
' if eval_valid("userid")
' dim session.__protected__userid as c
' session.__protected__userid = userid
' end if
'end if
%>That will store the user id in a session variable named session.__protected__userid (if you are using v11 you can reduce this to session.userid).

[Glen Schild]

Another variation could be to use the CanInsertRecord and CanUpdateRecord events as Steve has stated to update the value of a "LastModifiedBy" field, this can grab a session protected variable with the logged in users name. When the update is committed your MySQL trigger will then have access to the username to create the audit record.

If you are using more than one component for updates to a record then that would save some coding effort.