locked
How to pass .net application's parameter to a TRIGGER? RRS feed

  • Question

  • How can i pass my .net application's Userid to the trigger? I have a audit trail trigger on myTable. I dont know how to pass the userid (not the sql server user) to the trigger when a user delete a record from the application(.NET Application).

    The trigger saves the modifications on the table including the userid of one who does the changes.

    Monday, May 29, 2006 3:08 PM

Answers

  • Wouldnt your tecnique rely on keeping a connection to the SQL Server alive all the time from the client? Most of the time you should get rid of your connection as soon as possible, so it will not use resources when it is not needed (accquire late, release early). So when the programm is reopening a Server connection it will most likely get a new sysprocess ID. Also it might be requierd to open multiple connections to the SQL Server at the same time (At least in pre SQL 2005 since 2000 and before dont support multiple active result sets)

    Actually, if you are doing a web app like this you would keep the connection open, most likely using connection pooling. Creating and destroying connections to the server can be costly if you do it too many times. Connection pools allow a number of connections to be kept alive and shared amongst users. I am not 100% sure if you can use integrated security like this, but if you are doing a web app for public usage this would not be possible anyhow. And it can be tricky to set up (or so I am told) so quite often people fall back to just using a single login.

    This is why you need some technique to identify the user who is connecting. Adding user as a parm to all procedures is the most straightforward way. Or I described another method earlier using context_info

    • Edited by Naomi N Wednesday, January 18, 2012 3:55 AM Fixing
    Monday, June 5, 2006 1:36 AM

All replies

  • There is no way to send a value to a trigger directly other than in the inserted table by adding a column to the table.  This is proably your best way to do it anyhow, to add the userid to the table of the person who last updated the row, then you will have it for the delete.

    There is one (sort of tricky) way that I know of, using the CONTEXT_INFO global variable.  It allows you to set a value that you can then read in the trigger.  I actually use it for some of our applications where we might use integrated or use an application login.

    declare @context_info varbinary(30)
    set @context_info = cast('domainname\loginname' as varbinary(30))
    set context_info @context_info

    Then in the trigger:

    select cast(context_info() as varchar(30)) 

     

    Monday, May 29, 2006 3:17 PM
  • Hello

     

    You can rertieve the username by using "CURRENT_USER" This will return the name of the user thats currently connected to the DB. But for this to work you would need to grant the users Access to the DB with their Windows Accounts for example. If they are using SQL authentication for the Application, then you will get the same name for all users. Another option would be to pass the username as a field in the table that will fire the trigger. You need to do this since you cant pass any Parameters into a trigger (Okok... You COULD create a temporary table that will hold this information and extract it inside the trigger... But i would call that a bad design and you will be vulnerable to side effects since your trigger would rely on external data to do its work which is bad)

     

    Hope this helps

    Monday, May 29, 2006 3:25 PM
  • Good point, I assumed that the user wasn't known by the actual logged in user (like a web app).  A temp table (or a permanent table with a row per spid) would be ok too.  Might be a bit heavy on resources.

    Monday, May 29, 2006 3:28 PM
  • Well..

     

    I think the best way to do it is to add the field to the table.

    If you rely on anything from the "outside" then your trigger is "messed up". Those are a pain to debug... Or you need to make sure you trigger also works with multi-lines Inserts and updates that are called from the QA or from a DTS Package... If you MUST rely on this "outside Parameter" make sure you check for it and give it a meaningfull value if it is not present (DONT fail the trigger)

     

    P.s: Thanks for "SET CONTEXT_INFO" Never knew it existed

    Monday, May 29, 2006 3:34 PM
  • Mr Davidson what are the disadvantages of using the context_info?
    Wednesday, May 31, 2006 1:54 PM
  • You can only store 128 bytes of information in the context info. Since it is a varbinary stream, you will have to always convert it to the actual value type.  There are other restrictions depending on how you access the context information (DMV or sysprocesses or new built-in CONTEXT_INFO()). See Books Online for the details.
     
     
    If your security model has unique login (SQL/Windows that has access to SQL Server) for each user in the application then you can use the built-ins like SYSTEM_USER to get the user information. Otherwise, it is probably easier & clean to send userid/name as part of the DML statement or SP that manipulates the data. This way you don't have to do the extra things about setting context information and modifying all the SPs etc.
    Wednesday, May 31, 2006 10:26 PM
  • i am using a single userid and password in my connection string. Is it possible if the application users have their own userid to access the sql server? how can i configure my connection string to such a way?
    Saturday, June 3, 2006 5:03 AM
  • create table Logins

    (

    -- Your system's user ID

    userid integer,

    -- SQL server process ID

    sid integer

    )

    when your program starts write this info into this table.

     

    when the trigger fires, it knows the current sid from the @@spid, and gets the user id from this table.

     

     

    Sunday, June 4, 2006 11:40 AM
  • Hi DBT,


    Wouldnt your tecnique rely on keeping a connection to the SQL Server alive all the time from the client? Most of the time you should get rid of your connection as soon as possible, so it will not use resources when it is not needed (accquire late, release early). So when the programm is reopening a Server connection it will most likely get a new sysprocess ID. Also it might be requierd to open multiple connections to the SQL Server at the same time (At least in pre SQL 2005 since 2000 and before dont support multiple active result sets) 

    And you also need to think about a reliable way to remove the invalid logins from the login table, since you cant count on the application to sign out propperly all the time... (Crashed software, User who switch of their PC, network problems caused by evil cleaning staff  )

    Another option would be chainging the software to work with integrated authentication. This would allow the users to sign in with their own windows accounts. This right can be assigned to a user role that all users share which use your software. But since this would basically require a domain it might not be possible in all cases... On the other hand it would be the smales change on your software, since you would only need to change the connection string to use integrated security...

     

    Hope this helps

     

    Sunday, June 4, 2006 4:28 PM
  • Wouldnt your tecnique rely on keeping a connection to the SQL Server alive all the time from the client? Most of the time you should get rid of your connection as soon as possible, so it will not use resources when it is not needed (accquire late, release early). So when the programm is reopening a Server connection it will most likely get a new sysprocess ID. Also it might be requierd to open multiple connections to the SQL Server at the same time (At least in pre SQL 2005 since 2000 and before dont support multiple active result sets)

    Actually, if you are doing a web app like this you would keep the connection open, most likely using connection pooling. Creating and destroying connections to the server can be costly if you do it too many times. Connection pools allow a number of connections to be kept alive and shared amongst users. I am not 100% sure if you can use integrated security like this, but if you are doing a web app for public usage this would not be possible anyhow. And it can be tricky to set up (or so I am told) so quite often people fall back to just using a single login.

    This is why you need some technique to identify the user who is connecting. Adding user as a parm to all procedures is the most straightforward way. Or I described another method earlier using context_info

    • Edited by Naomi N Wednesday, January 18, 2012 3:55 AM Fixing
    Monday, June 5, 2006 1:36 AM
  • So what do you think the best way? Using the Context_info or creating a temporary log table?
    Tuesday, June 13, 2006 1:11 PM
  • my2cents - If you have a central object that get's a connection from the pool for the application, then having this function set context_info for the connection would be far simpler than having all parts of the application have to know about and deal with sending user id/name via the insert, update or sp call.
    Friday, July 14, 2006 3:09 PM
  • If you put the code that get's a connection at the start of a transaction into a common object then having this object set context_info with the application user name/id would be far simpler than having all parts of the application know about sending a user id as part of the insert, update or SP call.  The trigger should be setup to use the @@CURRENT_USER if context_info is not set.
    Friday, July 14, 2006 3:14 PM
  • Henry,

    Did you ever find an answer to your problem?  It sounds like you are trying to accomplish the same thing as me.

    Aaron
    Tuesday, July 17, 2007 6:21 PM
  • Hi - is the inserted table available to a delete trigger?  If so, how can the application set the last update user-id value when performing a delete?
    Tuesday, January 17, 2012 7:26 PM
  • Hi - is the inserted table available to a delete trigger?  If so, how can the application set the last update user-id value when performing a delete?


    The following special tables are available in a trigger and the OUTPUT clause:

    INSERT -> inserted table

    DELETE -> deleted table

    UPDATE -> deleted, inserted tables (old image, new image)

    MERGE -> deleted and / or inserted

    You need to set the last update userid in an audit trail table when deleting a record.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    • Edited by Kalman Toth Sunday, September 30, 2012 8:27 AM
    Wednesday, January 18, 2012 7:39 PM