在SQL触发器或存储过程中获取在程序登录的用户

实现一个AuditLog的功能，是B/S结构专案。

每个用户可以登录系统，在程序中操作数据(添加，更新和删除）需要实现记录操作跟踪。是谁添加，更新和删除的，这些信息将会插入至AuditLog表中。
一般情况之下，在SQL的触发器中,只能取到(SQL验证sa；Windows验证Domain\xxx)。这些用户名，达不到效果，不能真正反映到是谁操作的。

下面是让你清楚，怎样实现在SQL触发器或存储过程中获取在程序登录的用户,是在插入，更新或删除的存储过程，把登录程序当前用户传入进去。在存储过程中，再把相关信息存入局部(#)临时表中,这样子，在触发器即可获取了。

 

下面代码示例,以一个[Member]表作例，可以参详：

CREATE TABLE Member
(
    Member_nbr INT IDENTITY(1,1) PRIMARY EKY NOT NULL,
    [Name] NVARCHAR(30),    
    Birthday DATETIME,
    Email NVARCHAR(100),
    [Address] NVARCHAR(100)
)

GO

 

插入存储过程：

CREATE PROCEDURE MemberSp_Insert
(
    --Other parameter
    @Operater NVARCHAR(50) --带到此参数，可从程序的用户传至数据库
)
AS
BEGIN
    --处理插入事务
    ---INSERT INTO [dbo].[Member] (xxx) VALUES(xxx)    
    
    --把相关信息存入临时表，方便在触发器时取到。
    IF OBJECT_ID('#AuditWho') IS NOT NULL
    DROP TABLE [#AuditWho]    
    CREATE TABLE [#AuditWho] (PrimaryKey INT,Operater NVARCHAR(50))
    INSERT INTO [#AuditWho] VALUES(SCOPE_IDENTITY(),@Operater)
END

GO

 

更新存储过程：

CREATE PROCEDURE MemberSp_Update
(
    --Other parameter
    @Member_nbr INT,
    @Operater NVARCHAR(50) --带到此参数，可从程序的用户传至数据库
)
AS
BEGIN
    --处理更新事务
    ---UPDATE [dbo].[Member] SET [xxx] = xxx, ... WHERE [Member_nbr] = @Member_nbr
    
    
    --把相关信息存入临时表，方便在触发器时取到。
    IF OBJECT_ID('#AuditWho') IS NOT NULL
    DROP TABLE [#AuditWho]    
    CREATE TABLE [#AuditWho] (PrimaryKey INT,Operater NVARCHAR(50))
    INSERT INTO [#AuditWho] VALUES(@Member_nbr,@Operater)
END

GO

删除存储过程：

CREATE PROCEDURE MemberSp_Delete
(
    @Member_nbr INT,
    @Operater NVARCHAR(50) --带到此参数，可从程序的用户传至数据库
)
AS
BEGIN
    --处理删除事务
    ---DELETE FROM [dbo].[Member] WHERE [Member_nbr] = @Member_nbr
    
    
    --把相关信息存入临时表，方便在触发器时取到。
    IF OBJECT_ID('#AuditWho') IS NOT NULL
    DROP TABLE [#AuditWho]    
    CREATE TABLE [#AuditWho] (PrimaryKey INT,Operater NVARCHAR(50))
    INSERT INTO [#AuditWho] VALUES(@Member_nbr,@Operater)
END

GO

 

从上面的存储过程，用户相关的信息(应用程序的用户信息)已经在存储过程中存入临时表中，接下来，在触发器，怎样获取呢。可以参考下面的触发器代码：

插入触发器：

CREATE TRIGGER [dbo].[MemberTr_Insert] 
ON [dbo].[Member]
FOR INSERT
AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN
    
    SET NOCOUNT ON    
    --事务处理
    
    DECLARE @Operater NVARCHAR(50),@Member_nbr INT
    SELECT @Member_nbr = [Member_nbr] FROM inserted
    
    SELECT @Operater = [Operater] FROM [#AuditWho] WHERE [PrimaryKey] = @Member_nbr
    --插入Audit 表中
    --INSERT INTO ....
END

GO

 

更新触发器：

CREATE TRIGGER [dbo].[MemberTr_Update] 
ON [dbo].[Member]
FOR UPDATE
AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN
    
    SET NOCOUNT ON    
    --事务处理
    
    DECLARE @Operater NVARCHAR(50),@Member_nbr INT
    SELECT @Member_nbr = [Member_nbr] FROM deleted
    
    SELECT @Operater = [Operater] FROM [#AuditWho] WHERE [PrimaryKey] = @Member_nbr
    --插入Audit 表中
    --INSERT INTO ....
END

GO

删除触发器：

CREATE TRIGGER [dbo].[MemberTr_Delete] 
ON [dbo].[Member]
FOR DELETE
AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN
    
    SET NOCOUNT ON    
    --事务处理
    
    DECLARE @Operater NVARCHAR(50),@Member_nbr INT
    SELECT @Member_nbr = [Member_nbr] FROM deleted
    
    SELECT @Operater = [Operater] FROM [#AuditWho] WHERE [PrimaryKey] = @Member_nbr
    --插入Audit 表中
    --INSERT INTO ....    
END

GO

每段代码，有注释。

此问题有在某论坛发表让网友讨论过，但是效果不佳。如果你有另外见解，可以在讨论。谢谢。