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

原作者: [db:作者] 来自: [db:来源] 收藏邀请

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

每个用户可以登录系统，在程序中操作数据(添加，更新和删除）需要实现记录操作跟踪。是谁添加，更新和删除的，这些信息将会插入至AuditLog表中。
一般情况之下，在SQL的触发器中,只能取到(SQL验证sa；Windows验证Domain\xxx)。这些用户名，达不到效果，不能真正反映到是谁操作的。
下面是让你清楚，怎样实现在SQL触发器或存储过程中获取在程序登录的用户,是在插入，更新或删除的存储过程，把登录程序当前用户传入进去。在存储过程中，再把相关信息存入局部(#)临时表中,这样子，在触发器即可获取了。

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

复制代码代码如下:

 
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 

插入存储过程：

复制代码代码如下:

 
MemberSp_Insert 
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 

更新存储过程：

复制代码代码如下:

 
MemberSp_Update 
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 

删除存储过程：

复制代码代码如下:

 
MemberSp_Delete 
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 

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

复制代码代码如下:

 
MemberTr_Insert 
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 

更新触发器：

复制代码代码如下:

 
MemberTr_Update 
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 

删除触发器：

复制代码代码如下:

 
MemberTr_Delete 
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 