junk.mail291276 - Thursday, January 19, 2017 3:41 PM

Hello,

I'm dealing with a database problem and I would like to know how to solve it.

The problem is that I have an ObjectDeletions table that tracks the deletions of records in other tables and it requires a username (i.e. the person who made the deletion). This username is stored in one of the columns of the ObjectDeletions table. The problem is that the user is known only to the application through which deletions are made. The database knows nothing about these users. When the user deletes a project, for example, the application runs the stored procedure DeleteProjectData. I can pass the username to this stored procedure as a parameter but then I need to store the username in some kind of global variable. The reason I need to store it in a global variable is because the actual inserts into the ObjectDeletions table happens only via a trigger on a few other tables. For example, on the Project table, I have a trigger that looks like this:

ALTER TRIGGER [dbo].[trg_Project_ObjectDeletions]
  ON [dbo].[Project]
  FOR DELETE
  AS
  BEGIN
   INSERT INTO [dbo].[ObjectDeletions]
        (ObjectId, ObjectType, Description, UserName, TimeStamp)
        SELECT d.ProjectId, 'Project', d.Name + ' - ' + d.AcmProject, SYSTEM_USER, SYSDATETIME()
        FROM deleted d
  END

In other words, it's not the stored procedure which inserts records into the ObjectDeletions table, but a trigger on the Project table.

If I can stored the username in a global variable as a first step in the stored procedure, how can I retrieve it in the trigger? Or is there a better way to accomplish this?