在SQL触发器或存储过程中获取在程序登录的用户
程序员文章站
2023-12-14 19:30:46
实现一个auditlog的功能，是b/s结构专案。 每个用户可以登录系统，在程序中操作数据(添加，更新和删除）需要实现记录操作跟踪。是谁添加，更新和删除的，这些信息将会插入...
实现一个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
每段代码，有注释。
此问题有在某论坛发表让网友讨论过，但是效果不佳。如果你有另外见解，可以在讨论。谢谢。
每个用户可以登录系统，在程序中操作数据(添加，更新和删除）需要实现记录操作跟踪。是谁添加，更新和删除的，这些信息将会插入至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
每段代码，有注释。
此问题有在某论坛发表让网友讨论过，但是效果不佳。如果你有另外见解，可以在讨论。谢谢。