Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    16

    Unanswered: passing admin_id to trigger

    I want to have a trigger on a table, when a cell is updated it takes the current value and stores this in a audit table, for example:

    table.data_change
    id table primary_key column new_value old_value timestamp


    But is it possible to also pass an admin_id/user_id from the application to the database along with the update SQL query?

    The main reason for using triggers is to prevent round trips between the db and application servers.

    Any suggestions?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    If you create a trigger on a table with BEFORE UPDATE you can reference the NEW.fieldname and OLD.fieldname to get the value before and after the UPDATE. You can INSERT the OLD.fieldname value to your audit table.

    As for the user_id, is this an internal username or the username that you are connecting to the database with? If it is the latter then you can user CURRENT_USER(). If it is the internal identifier then you can always add this as part of the table so that during the UPDATE statement you also include this user_id. You can again reference the NEW.user_id and OLD.user_id in your trigger code.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jan 2010
    Posts
    16
    its an internal username, one that is stored in a user table. (not a mysql user).

    Yeh I can see what you mean, e.g having a last_update_admin_id, and then passing the admin_id along with the update SQL.

    This will work nicely, cheers.

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I have put together some sample trigger code based on what you are looking for above. Check out MySQL – Triggers IT Integrated Business Solutions
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Jan 2010
    Posts
    16
    Looks good, thanks for the code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •