如何获得用户名到SQL触发器当多个用户从ASP成员签署 [英] how to get username into sql trigger when multiple users signed on from asp membership

问题描述

我试图记录所有修改数据库，但无法找到一种方式来获得当前用户名的触发器。我有存储的用户信息的TriggerData 表
（GUID（用户ID），数据（用户名），logintime）这些被插入时，在用户登录。

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.

下面是触发

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

输入用户名查询不给当前修改用户，而不是它给最近登录的用户。任何帮助是AP preciated。谢谢！

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!

推荐答案

就像marc_s说触发器能够而且将会每批应用一次。你的情况，但它听起来像这样它的正常工作当前的应用程序只每批插入一次。如果有人试图插入多行您将有问题但是。说从应用程序的另一部分，或从查询窗口。这真的取决于你，如果你想立即修复它，要么等待它突破（如果有的话）。

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))

您可以在此看到您的问题。如果您在WHERE子句中看看，你会看到，你是专门拉在上次登录的行。如果你不使用的应用程序ID（尽管它听起来像你），你可以使用USER_NAME（）或SUSER_SNAME（）拉当前用户。不幸的是根据您所提供的，我不认为你将能够拉，实际上所做的更改，再假设你正在使用的应用程序ID用户的信息。

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.

您可以尝试把SPID（ID为当前连接）到您的用户表。您可以检索此为@@ SPID。这样，当你在当前的连接，你将永远是能够告诉当前用户是谁。当然，如果你持有到整个时间在用户登录的连接这只会工作（可能不是一个基于Web的应用程序）。

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).

最后（和最不幸的）是，而不是使用触发器创建存储过程做你的插入。通过在当前用户进入SP作为参数列表的一部分，做你的记录在那里。

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.

这篇关于如何获得用户名到SQL触发器当多个用户从ASP成员签署的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！