CODEWITHSUNDEEP
asp.netsql-serverasp.net-mvcsql-server-2008t-sql
scc
scc

Reputation: 385

how to get username into sql trigger when multiple users signed on from asp membership

I am trying to log all the changes to database but was unable to find a way to get current username to the trigger. I have triggerData table which stores the information of user (guid (userid), data (username), logintime) these are inserted when user sign in.

Here is the trigger 

declare @UserIsOnlineTimeWindow DateTime
    declare @currenttime DateTime
    set @currenttime = GETDATE()
    set @UserIsOnlineTimeWindow = 10

    DECLARE @uname nvarchar(max)
    **set @uname = (SELECT T.UserName from (SELECT distinct u.UserName FROM aspnet_Users u
    WHERE IsAnonymous = 'FALSE' AND ((@currenttime - @UserIsOnlineTimeWindow) < u.LastActivityDate)) as t,
    TriggerData td, aspnet_Users au 
    WHERE t.UserName = td.Data and td.guid = au.UserId and td.logintime = (select MAX(td.logintime) from TriggerData td))**

    DECLARE @ComputerName nvarchar(50)
    DECLARE @IPAddr nvarchar(50)
    DECLARE @BroadcastIP nvarchar(50)
    DECLARE @auditInsert nvarchar(255)
    SET @ComputerName = (SELECT ComputerName FROM inserted)
    SET @IPAddr = (SELECT ISNULL(IPAddr,0) FROM inserted)
    SET @BroadcastIP = (SELECT ISNULL(BroadcastIP,0) FROM inserted)
    SET @auditInsert =  @ComputerName+' '+@IPAddr+' '+@BroadcastIP
    Begin

    INSERT INTO Audit(OldInfo ,NewInfo,[User],Date1,Type,TableName) VALUES('New Record',@auditInsert, @uname ,GETDATE(),'Added','LabIP')

    End

The query for the username does not give the currently modifying user instead it gives the recently logged in user. Any help is appreciated. Thanks!

Upvotes: 2

Views: 3422

Answers (1)

Kenneth Fisher
Kenneth Fisher

Reputation: 3812

Like marc_s said triggers can and will be applied once per batch. In your case however it sounds like your current application is only inserting once per batch so it's working fine. Your will have a problem however if anyone tries to insert more than one row. Say from another part of the application, or from a query window. It's really up to you if you want to fix it now or wait for it to break (if ever).

I've re-formated the query that is giving you problems. 

set @uname = (SELECT T.UserName FROM
        (SELECT DISTINCT u.UserName 
        FROM aspnet_Users u
        WHERE IsAnonymous = 'FALSE' 
          AND ((@currenttime - @UserIsOnlineTimeWindow) < u.LastActivityDate)) AS t
        JOIN TriggerData td
          ON t.UserName = td.Data 
        JOIN aspnet_Users au 
          ON td.guid = au.UserId 
        WHERE td.logintime = (select MAX(td.logintime) from TriggerData td))

You can see from this where your problem is. If you look in the WHERE clause you will see that you are specifically pulling the row that last logged in. If you are not using an application id (although it sounds like you are) you can use USER_NAME() or SUSER_SNAME() to pull the current user. Unfortunatly based on the information you have provided I don't think you will be able to pull the user that actually made the change, again assuming that you are using an application id.

You might try putting the SPID (id for the current connection) into your users table. You can retrieve this as @@SPID. That way while you are in the current connection you will always be able to tell who the current user is. Of course this will only work if you are holding onto the connection for the whole time the user is logged in (probably not in a web based application).

Last (and unfortunatly least) is rather than using triggers create stored procedures to do your inserts. Pass in the current user into the SP as part of the parameter list and do your logging there.

Sorry I couldn't help more.

Upvotes: 3

Related Questions