SQL Server Performance

Retrieve Variable From Stored Procedure Within Trigger

Discussion in 'T-SQL Performance Tuning for Developers' started by BDRichardson, Oct 2, 2007.

  1. BDRichardson New Member

    Is there any way in which it is possible to retrieve data, such as a variable, within a Trigger from an executed Stored Procedure?
    For example, I provide the UserId as a Parameter to a Stored Procedure which updates records within a table. I would like to use a Trigger to insert records with the UserId into an audit table.
    I am unable to obtain the UserId from SYSTEM_USER or CURRENT_USER functions because I am authenticating with a single user account from a web application.
  2. Adriaan New Member

    Your description is a little confusing ...
    Do you want to send a bit of data from the inserted rows as parameters to a stored procedure?
    Or do you call a stored procedure which should should return a bit of data, that your trigger then inserts into your log table, along with other data from the inserted 'magic table'?
  3. BDRichardson New Member

    I call a Stored Procedure which updates a record in one table. The table has a Trigger which responds to the update by inserting a new record in a different log table. The Trigger uses the data from the INSERTED table to populate the fields of the log table.
    All works fine, except I would like to record the UserId in the log table, which I am hoping I might be able to retrieve from the Stored Procedure parameter value.
    I suspect I am hoping for too much to be able to retrieve information from the Stored Procedure which fired the trigger! And if so, there might be an alternative solution.
    If every user of the application was assigned a user account within SQL security, and this was used to connect to the database, then I could simply use the SYSTEM_USER or CURRENT_USER function within the Trigger. However, I am only using one user account because the application is web based.
    Hopefully this clarifies my problem a little more!
  4. Adriaan New Member

    Your sp MySproc updates your table MyTable, and it has a way of establishing the UserId. If your MyTable has a column UserId, why not let MySproc insert the UserId?
    Your trigger can then use the UserId column from inserted, or you change MySproc to handle the logging itself.
  5. BDRichardson New Member

    Not exactly, I pass the UserId value to a parameter for the SP, only because I wish to use it for the logging. The table being updated within the SP does not have a UserId column. The UserId column only exists in the log table.
    I suspected the only way was to handle all of the logging from within the SP. Yes, it can be done, but it requires more complex programming, and is much more difficult to maintain.
  6. Adriaan New Member

    If adding a UserId column to the table is no option ....
  7. BDRichardson New Member

    Afraid not.
    Many thanks for your thoughts.
  8. Adriaan New Member

    What about this ...
    The sp inserts the data into the table, and logs the identity values for those rows into an intermediate table, along with the user id and perhaps a time stamp.
    Instead of using a trigger for the logging, you schedule a job that calls another sp, which reads the entries from the intermediate table, looks up the details from the table based on the identity values, and then writes out to the log table.
  9. BDRichardson New Member

    I have decided on a compromise of your suggestion. I execute the SP, which creates a temporary record with UserId, Timestamp and other information in a reference table, then performs the update. Consequently, the trigger fires, which retrieves the UserId from the temporary record from the reference table, appends records to the log table, and finally deletes the temporary record.
    I realise there is potential for another user to perform a similar action on the same table, and a potential risk that the incorrect UserId could be retrieved within the Trigger. I have attempted to eliminate this possibility by matching the other information from the reference table. For example, matching the TableId, EditTypeId (i.e. update action), etc and a Timestamp within the last few seconds.
    Finally, to eliminate storing records which have expired, the records for the user are deleted when the user logs into the application. And the table is truncated over night when users are unlikely to use the application.
    Many thanks for your assistance, and for providing me with the inspiration to achieving a much more satisfying solution.
  10. Adriaan New Member

    In a trigger, you always have the snapshot inserted that holds only the rows that have been inserted/updated. When two users cause the trigger to fire, the trigger will see different inserted snapshots, so there should not be any confusion.
    Glad to have been of help!

Share This Page