Log User Activity

Radzen Studio Blazor Server
1

Hello,

I would like to log changes made by users in a table using database triggers that reference the UserName stored in the Radzen generated AspNetUsers table.

For example, if a SampleUser@email.com submits changes to a record I would like to insert a record into a log table that indicates the original data, the new data, and that SampleUser@email.com is the one who made the change.

Is there a method of passing the UserName as a parameter to my trigger or another approach I should take?

Any information or advice is appreciated.

Thanks!

Zach

2

Hi @Zach.E,

This thread might help you:

3

Thank you for the resource @enchev!

After some consideration I used a different method and thought I would share it here for future readers.

  • Add a LastEditedBy column to tables you want to track user changes for

  • On page load event for edit forms related to those tables set ${data.LastEditedBy} to whichever 'Current application user' field you want to track such as ${Security.User.Id}

  • Ensure the LastEditedBy field is hidden or disabled on your edit form so users cannot change it

  • Create a trigger on your table on UPDATE similar to this (add or remove fields as necessary this is just an example):

CREATE TRIGGER [dbo].[UpdateUserActivityLog] 

ON  [dbo].[Record]

AFTER UPDATE

AS 

BEGIN

SET NOCOUNT ON;

INSERT INTO dbo.[UserActivityLog]
(
	Datestamp
	, UserID
	, OldData
	, NewData
)

SELECT
	GETDATE()
	, ins.UserID AS UserID
	, del.RecordData AS OldData
	, ins.RecordData AS NewData
FROM inserted ins
INNER JOIN deleted del
ON ins.RecordID = del.RecordID

END
1 Like