Link to home
Start Free TrialLog in
Avatar of philipjonathan
philipjonathanFlag for New Zealand

asked on

Question about trigger in MySQL

Hi experts, this might be a long-winded question, but here it goes:

I'm developing an application with MySQL for it database end. One of the requirement for the app is to have built-in audit trail, which monitors for certain tables. For example, if a record is added to customer table, an audit log is appended into customer_history. Example provided here are for illustration. The credit for this idea goes to this author:
http://www.indywebshop.com/bestpractices/2006/07/28/leaving-an-audit-trail-in-your-database/

customer table:
id | name
--------------------
1  | John Doe
2  | Jane Doe

customer_history table:
id | name               | action   | action_by | action_time
----------------------------------------------------------------------
1  | John Doh         | insert   | Andy         | 2008-08-04 10:00:00
1  | John Doe         | update | Bob           | 2008-08-04 12:00:00
3  | Jonathan Doe  | delete  | Andy         | 2008-08-04 12:30:00

I've read about MySQL triggers and plan to use it. Btw, I'm using MySQL 5.0.41, which does support triggers. And after considering between using triggers and having the audit trail written at app level, I choose for the former option. The reason is to also log changes that are made directly against the database through Query Analyzer, etc. (Please feel free to voice out your opinions / insights about this design, although this is not the real question  :) )

Now, action and action_time columns are easy to handle. But how do I get the name of the user that makes the changes? I need to use the name of the user that is logged in to the web app, not the database user account. The login name is stored in another table.

Thanks in advance
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of philipjonathan

ASKER

anglelIII, hernst42, thanks for the super quick response. If that's the case, I think I need to go back to the drawing board ... *sigh*
>I need to use the name of the user that is logged in to the web app, not the database user account. The login name is stored in another table.

Since what I'm looking for is the app user name, and not the database name, it's hernst42's comment that really answers my doubt, though the answer is "not possible".
if the application account can be mapped to a sql login...
hernst42's comment that really answer my question, so I award him more points. I hope you guys understand. Thanks!
Avatar of Glauron
Glauron

Could you not create a temporary table upon user login that holds some session info including the application user's name?
Eg. when the application confirms the login of a user, or successfuly gathers the session info for a user that has already logged in (through cookies etc), you can "create temporary table session_info ..." and store the user's name there.

The temporary table will only be available to the CURRENT database connection, and will be destroyed when that connection is terminated. There it will work for CGI based apps (PHP, PERL, ...) which (usually) make a new connection each time a page is loaded, or other applications that supply a database connection from a pool each time code is run (you might need to manually drop the temp table when done with the connection this way though).

If you have a temporary table available, the trigger will be able to access that info when recording changs etc. There will be things to consider such as utils / maintenance programs that run w/o sessions. Maybe the trigger can do an IF session_info use name, otherwise name='SYSTEM' or something.

Just an idea, I haven't tried it but I'm researching building something similar & that's what I had in mind.