Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2006
    Posts
    6

    Question Unanswered: Using Global Variables in Triggers ?

    We're using SQL Server 2000. I have a table with a couple fields I'd like to automatically updatre with a atrigger. One of the fields needs to log the current time the record has been modified. The other field needs to log the current user ID.

    Storing the current time is easy with UPDATE and GETDATE(), but obtaining the current user from my ASP.NET code isnt as pretty as I would have to pass in the user ID from my backend code into the server, perhaps as a stored procedure variable that will manually update the table with the new ID. I was hoping to perhaps set some global variable of the user ID for the current connection to the dbase so that once the trigger is made, it feeds off this connection-level global variable to plug in the needed User ID for the record.

    So for instance:
    SET @@MyCurrentUserID = 4; UPDATE MyTable SET myStuff='games';

    The MyTable trigger will be initiated and the LastModified field of MyTable will be auto update with the current datetime, but also the LastModifiedUser field of MyTable will be update with MyCurrentUserID.

    Is this possible with SQL Server 2000 for Windows 2K3?

    Tx
    Last edited by quantass; 03-20-07 at 11:23.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Have you thought about using any of the niladic or system functions?
    Code:
    select	SUSER_SNAME(),
    	Host_Name(),
    	session_user,
    	user,
    	session_user,
    	user_name(),
    	current_user
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by blindman
    Have you thought about using any of the niladic or system functions?
    Code:
    select	SUSER_SNAME(),
    	Host_Name(),
    	session_user,
    	user,
    	session_user,
    	user_name(),
    	current_user
    My guess is that he's using an application username/password (and thus these internal functions won't be particularly helpful). If you are using NT authentication, then blindman's suggestion is an easy one to implement.

    Typically we use stored procs with added parameters ModBy, ModDate (and all tables have columns Addby, AddDate, ModBy, ModDate). From there, it's easy to constuct a trigger that captures these values and stores them in a log/audit table.

    I seem to recall that there is a connection variable called "application name" (or something similar). This can be set at runtime to any value you wish. That might be a viable work-around, but it's not what I would recommend.

    I have also coded an application that authenticated each user to the database individually (not using Windows authentication). This requires a LOT of time and patience as you have to re-create the user provisioning tools. It also hurts scalability since you can't do connection pooling using this methodology. Still, we were able to support 1000+ users using a reporting application this way.

    Just some food for thought.

    Regards,

    hmscott
    Have you hugged your backup today?

  4. #4
    Join Date
    Sep 2006
    Posts
    6
    Geez you guys know your stuff. Yes, the user has logged in via NT Auth (we;re using Sharepoint). We map this information to additional details within the dbase and so i could use this to locate the actual user id i require. Seems good. I will use it. Im still very curious about the connection variable in case i dont have NT Auth in the future.

    Thanks a lot!!

  5. #5
    Join Date
    Sep 2006
    Posts
    6
    Ok i found some time to try it out SUSER_SNAME() from within the trigger. Now the sequence is the client-side browser makes an AJAX request to an ASP.NET page, this then makes the SQL Query into our Sql Server 2K dbase to update a table's record. What i was hoping for is to capture the client-side NT Authenticated user that is using the web page and to set this name within the modified record using SUDER_SNAME().

    Unfortuantly it seems to be returning the Sql server dbase login name rather than the client user name.

    Any additional help would be appreciated.

    Thanks again.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The database server only knows about the connection that is made to it. If you want information that is not contained within the connection, then that will need to be passed through as a parameter. In such a case, the parameter can be used to set the column value directly by the sproc, and there is no need to maintain this value using a trigger.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Sep 2006
    Posts
    6
    Makes a lot of sense.

    Thank you!

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If this is an intranet you can set the ASP.NET site to run on windows authentication and set up delegates (getting past the double hop problem) which means that ASP.NET will connect to the db using the client credentials. This depends, though, on whether this set up maps well with your security requirements. It is a bit of a ballache to put together too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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