在SQL触发器或存储过程中,可以获取程序登录的用户。下面我们就开始介绍,怎样实现在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
#p#
从上面的存储过程,用户相关的信息(应用程序的用户信息)已经在存储过程中存入临时表中,接下来,在触发器,怎样获取呢。可以参考下面的触发器代码:
插入触发器:
- 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
【编辑推荐】