在SQL觸發器或存儲過程中獲取在程序登錄的用戶

2024-08-31 00:45:20

字體：大中小

來源：轉載

供稿：網友

實現一個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