How to automatically add CURRENT_USER to the temporal table

Question

Hello guys, I am a newcomer to SQL SERVER. I would like to ask a question about temporal table

I want to be able to check who is the editor on the temporal table.

When executing update, the current_user is automatically filled in the column editby in the demo table.

When executing delete, the current_user is automatically filled in the column editby in the demo_archive table.

create the temporal table

The column createby in the Demo and Demo_Archive table stored which user inserts data

The column editedby in the Demo and Demo_Archive table stored which user updates or delete data

CREATE TABLE Temporal.Demo (
DemoID INT NOT NULL PRIMARY KEY Identity(1,1), 
DemoValue Int NOT NULL,
Createby SYSNAME DEFAULT CURRENT_USER NOT NULL,
Editedby NVARCHAR(10) NULL, 
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL, 
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL, 
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = Temporal.Demo_Archive));

insert two records to Demo table by user dbo

EXECUTE as USER = 'dbo';
Insert Into Temporal.Demo (DemoValue)
Values (100),(200)

There are two inserted records in Demo table There is no record in Demo_Archive table

update the first record and delete the second record by user fcu.user02

EXECUTE as USER = 'fcu.user02'
Update Temporal.Demo
Set DemoValue = 110
where DemoID = 1;

EXECUTE as USER = 'fcu.user02'
Delete Temporal.Demo
where DemoID = 2;

current result

keep only one record in Demo table There are two expired records in Demo_Archive table

result that i want

the Demo table the Demo_Archive table

When executing update, the current_user is automatically filled in the column editby in the Demo table.

When executing delete, the current_user is automatically filled in the column editby in the Demo_Archive table.

my effort on update

I try to execute the following code to create a trigger after creating the data table.

CREATE TRIGGER editby
on Temporal.Demo
For UPDATE 
AS
BEGIN  
Update Temporal.Demo
Set Editedby = CURRENT_USER
where ValidFrom = (SELECT 
   MAX(D.ValidFrom)
FROM Temporal.Demo as D
WHERE D.ValidFrom < GETUTCDATE());
END

Then insert the data and update the first piece of data, the result I got as follows.

current result after creating trigger

No problem in table Demo There is an additional record with the same time in column ValidFrom and ValidTo in the table Demo_Archive.

I try to edit the trigger with the following code

CREATE TRIGGER editby
on Temporal.Demo
For UPDATE 
AS
BEGIN  
Update Temporal.Demo
Set Editedby = CURRENT_USER
where ValidFrom = (SELECT 
   MAX(D.ValidFrom)
FROM Temporal.Demo as D
WHERE D.ValidFrom < GETUTCDATE());
ALTER TABLE Temporal.Demo 
SET (SYSTEM_VERSIONING = OFF);
DELETE Temporal.Demo_Archive
where ValidFrom = ValidTo
ALTER TABLE Temporal.Demo 
SET (SYSTEM_VERSIONING = ON);
END

But still can't delete the record with the same time in Demo_Archive

my effort on delete

I have no idea how to delete record and set editedby = current_user at the same time

I would like to ask if anyone has done the similar question like mine and can give me some directions or your code. Thanks for reading here.

Answer 1

You actually can use a computed column to archive your target:

Editedby AS COALESCE(SUSER_NAME(SUSER_ID()), USER_NAME()),

Answer 2

I am a newcomer to SQL SERVER.

And you are using commands like EXECUTE AS USER! That's an advanced newcomer! There are many experienced users who don't know about this command, I suspect.

As for your question, I'm afraid that it is not possible to achieve. The temporal table provides the different versions the rows have had during their lifetime. If Tina created the row, and Jake deleted, Jake was never in EditedBy, because, well, he never edited.

The best you can do is to have an INSTEAD OF DELETE that performs an update just before deletion, and then this will be the row that is deleted. I see that you tried an AFTER TRIGGER, but I think INSTEAD OF would have better chances

Answer 3

Hi @Jingyu

I have no idea how to delete record and set editedby = current_user at the same time

When using DML triggers, you could use the deleted and inserted logical (conceptual) tables. The deleted and inserted tables hold the old values or new values of the rows that may be changed by the user action. Refer to this article for more details: Use the inserted and deleted tables.

Check this sample below:

CREATE TRIGGER tri_InsteadDelete
ON TableName
Instead of Delete
AS
BEGIN
  UPDATE Demo_Archive SET Editedby = CURRENT_USER
  WHERE DemoID in (select ID from deleted)
  DELETE FROM TableName WHERE ID in (select ID from deleted)
END

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.