Obtain the user logging on to the program in the SQL trigger or stored procedure

An AuditLog is a B/S structure project.

Each user can log on to the system, and the operation data (ADD, update, and delete) in the program must be recorded for operation tracking. The information will be inserted into the AuditLog table.
Generally, only SQL triggers can be obtained (SQL verification sa; Windows verification Domain \ xxx ). These user names cannot achieve the effect and cannot really reflect who performed the operation.
The following describes how to obtain the user logged on to the SQL trigger or stored procedure as a stored procedure in which the user is inserted, updated, or deleted, pass in the current user of the logon program. During the storage process, the related information is stored in a local (#) temporary table. In this way, the information can be obtained from the trigger.

The following code example uses a [Member] table as an example. The parameters are as follows:
Copy codeThe Code is as follows:
Member
Create table Member
(
Member_nbr int identity (1, 1) primary eky not null,
[Name] NVARCHAR (30 ),
Birthday DATETIME,
E-mail NVARCHAR (100 ),
[Address] NVARCHAR (100)
)
GO


Insert stored procedure:
Copy codeThe Code is as follows:
MemberSp_Insert
Create procedure MemberSp_Insert
(
-- Other parameter
@ Operater NVARCHAR (50) -- this parameter can be taken from the user of the program to the database.
)
AS
BEGIN
-- Process insert transaction
--- Insert into [dbo]. [Member] (xxx) VALUES (xxx)
-- Save the relevant information to the temporary table for easy retrieval during the trigger.
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


Update the stored procedure:
Copy codeThe Code is as follows:
MemberSp_Update
Create procedure MemberSp_Update
(
-- Other parameter
@ Member_nbr INT,
@ Operater NVARCHAR (50) -- this parameter can be taken from the user of the program to the database.
)
AS
BEGIN
-- Process update transactions
--- UPDATE [dbo]. [Member] SET [xxx] = xxx,... WHERE [Member_nbr] = @ Member_nbr

-- Save the relevant information to the temporary table for easy retrieval during the trigger.
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

Delete stored procedure:
Copy codeThe Code is as follows:
MemberSp_Delete
Create procedure MemberSp_Delete
(
@ Member_nbr INT,
@ Operater NVARCHAR (50) -- this parameter can be taken from the user of the program to the database.
)
AS
BEGIN
-- Process deletion transactions
--- Delete from [dbo]. [Member] WHERE [Member_nbr] = @ Member_nbr

-- Save the relevant information to the temporary table for easy retrieval during the trigger.
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


From the above stored procedure, user-related information (Application User information) has been stored in the temporary table in the stored procedure. Next, how can we obtain the information in the trigger. Refer to the following trigger code:
Insert trigger:
Copy codeThe Code is as follows:
MemberTr_Insert
Create trigger [dbo]. [MemberTr_Insert]
ON [dbo]. [Member]
FOR INSERT
AS
BEGIN
IF @ ROWCOUNT = 0 RETURN
SET NOCOUNT ON
-- Transaction Processing
DECLARE @ Operater NVARCHAR (50), @ Member_nbr INT
SELECT @ Member_nbr = [Member_nbr] FROM inserted
SELECT @ Operater = [Operater] FROM [# AuditWho] WHERE [PrimaryKey] = @ Member_nbr
-- Insert an Audit table
-- Insert ....
END
GO


Update trigger:
Copy codeThe Code is as follows:
MemberTr_Update
Create trigger [dbo]. [MemberTr_Update]
ON [dbo]. [Member]
FOR UPDATE
AS
BEGIN
IF @ ROWCOUNT = 0 RETURN
SET NOCOUNT ON
-- Transaction Processing
DECLARE @ Operater NVARCHAR (50), @ Member_nbr INT
SELECT @ Member_nbr = [Member_nbr] FROM deleted
SELECT @ Operater = [Operater] FROM [# AuditWho] WHERE [PrimaryKey] = @ Member_nbr
-- Insert an Audit table
-- Insert ....
END
GO

Delete trigger:
Copy codeThe Code is as follows:
MemberTr_Delete
Create trigger [dbo]. [MemberTr_Delete]
ON [dbo]. [Member]
FOR DELETE
AS
BEGIN
IF @ ROWCOUNT = 0 RETURN
SET NOCOUNT ON
-- Transaction Processing
DECLARE @ Operater NVARCHAR (50), @ Member_nbr INT
SELECT @ Member_nbr = [Member_nbr] FROM deleted
SELECT @ Operater = [Operater] FROM [# AuditWho] WHERE [PrimaryKey] = @ Member_nbr
-- Insert an Audit table
-- Insert ....
END
GO

Each code segment contains comments.
This issue was posted in a forum for discussion by netizens, but the effect was not good. If you have other insights, you can discuss them. Thank you.