11

In setting up an audit trail I have no problem tracking who is updating or inserting records in a table, however, tracking who deletes records seems more problematic.

I can track Inserts/Updates by including in the Insert/Update the field "UpdatedBy". This allows the INSERT/UPDATE trigger to have access to the field "UpdatedBy" via inserted.UpdatedBy. However, with the Delete trigger no data is inserted/updated. Is there a way to pass information onto the Delete trigger such that it could know who deleted the record?

Here is an Insert/Update trigger

ALTER TRIGGER [dbo].[trg_MyTable_InsertUpdate] 
ON [dbo].[MyTable]
FOR INSERT, UPDATE
AS  

INSERT INTO AuditTable (IdOfRecordedAffected, UserWhoMadeChanges) 
VALUES (inserted.ID, inserted.LastUpdatedBy)
FROM inserted 

Using SQL Server 2012

  • 1
    See this answer. SUSER_SNAME() is the key to get who deleted record. – Kin Shah Nov 13 '14 at 20:23
  • 1
    Thanks Kin, however I don't think SUSER_SNAME() would work in a situation like a web application where a single user might be used for database communication for the entire application. – webworm Nov 13 '14 at 20:33
  • 1
    You did not mention that you were calling a web app. – Kin Shah Nov 13 '14 at 20:49
  • Sorry Kin, I should have been more specific to the application type. – webworm Nov 13 '14 at 20:49
10

Is there a way to pass information onto the Delete trigger such that it could know who deleted the record?

Yes: by using a very cool (and under utilized feature) called CONTEXT_INFO. It is essentially session memory that exists in all scopes and is not bound by transactions. It can be used to pass info (any info--well, any that fits into the limited space) to triggers as well as back and forth between sub-proc / EXEC calls. And I have used it before for this exact same situation.

Test with the following to see how it works. Notice that I am converting to CHAR(128) before the CONVERT(VARBINARY(128), ... This is to force blank-padding to make it easier to convert back to VARCHAR when getting it out of CONTEXT_INFO() since VARBINARY(128) is right-padded with 0x00s.

SELECT CONTEXT_INFO();
-- Initially = NULL

DECLARE @EncodedUser VARBINARY(128);
SET @EncodedUser = CONVERT(VARBINARY(128),
                            CONVERT(CHAR(128), 'I deleted ALL your records! HA HA!')
                          );
SET CONTEXT_INFO @EncodedUser;

SELECT CONTEXT_INFO() AS [RawContextInfo],
       RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())) AS [DecodedUser];

Results:

0x492064656C6574656420414C4C20796F7572207265636F7264732120484120484121202020202020...
I deleted ALL your records! HA HA!

PUTTING IT ALL TOGETHER:

  1. The app should call a "Delete" stored procedure that passes in the UserName (or whatever) that is deleting the record. I assume this is already the model being used since it sounds like you are already tracking Insert and Update operations.

  2. The "Delete" stored procedure does:

    DECLARE @EncodedUser VARBINARY(128);
    SET @EncodedUser = CONVERT(VARBINARY(128),
                                CONVERT(CHAR(128), @UserName)
                              );
    SET CONTEXT_INFO @EncodedUser;
    
    -- DELETE STUFF HERE
    
  3. The audit trigger does:

    -- Set the INT value in LEFT (currently 50) to the max size of [UserWhoMadeChanges]
    INSERT INTO AuditTable (IdOfRecordedAffected, UserWhoMadeChanges) 
       SELECT del.ID, COALESCE(
                         LEFT(RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())), 50),
                         '<unknown>')
       FROM DELETED del;
    
  4. Please note that, as @SeanGallardy pointed out in a comment, due to other procedures and/or ad hoc queries deleting records from this table, it is possible that either:

    • CONTEXT_INFO has not been set and is still NULL:

      For this reason I have updated the above INSERT INTO AuditTable to use a COALESCE to default the value. Or, if you don't want a default and require a name, then you could do something similar to:

      DECLARE @UserName VARCHAR(50); -- set to the size of AuditTable.[UserWhoMadeChanges]
      SET @UserName = LEFT(RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())), 50);
      
      IF (@UserName IS NULL)
      BEGIN
         ROLLBACK TRAN; -- cancel the DELETE operation
         RAISERROR('Please set UserName via "SET CONTEXT_INFO.." and try again.', 16 ,1);
      END;
      
      -- use @UserName in the INSERT...SELECT
      
    • CONTEXT_INFO has been set to a value that is not a valid UserName, and hence might exceed the size of the AuditTable.[UserWhoMadeChanges] field:

      For this reason I added a LEFT function to ensure that whatever is grabbed out of CONTEXT_INFO will not break the INSERT. As noted in the code, you just need to set the 50 to the actual size of the UserWhoMadeChanges field.


UPDATE FOR SQL SERVER 2016 AND NEWER

SQL Server 2016 added an improved version of this per-session memory: Session Context. The new Session Context is essentially a hash table of Key-Value pairs with the "Key" being of type sysname (i.e. NVARCHAR(128) ) and the "Value" being SQL_VARIANT. Meaning:

  1. There is now a separation of values so less likely to conflict with other uses
  2. You can store various types, no longer needing to worry about the odd behavior when getting the value back out via CONTEXT_INFO() (for details, please see my post: Why Doesn’t CONTEXT_INFO() Return the Exact Value Set by SET CONTEXT_INFO?)
  3. You get a lot more space: 8000 bytes max per "Value", up to 256kb total across all keys (compared to the 128 bytes max of CONTEXT_INFO)

For details, please see the following documentation pages:

  • The problem with this approach is that it is VERY volatile. Any session can set this, as such it can overwrite any previously set item. Want to really break your application? have a single dev overwrite what you expect. I would highly advise NOT to use this and have a standard approach which may require an architecture change. Otherwise, you're playing with fire. – Sean Gallardy Nov 14 '14 at 4:58
  • @SeanGallardy Can you please provide an actual example of this happening? Session == @@SPID. This is PER-Session/Connection memory. One session cannot overwrite another session's context info. And when the session logs off the value goes away. There is no such thing as a "previously set item". – Solomon Rutzky Nov 14 '14 at 5:07
  • 1
    I didn't say "another session's" I said any object in the session scope can do this. So, one dev writes a sproc to hold his own "contextual" information and now yours is overwritten. There was an application I had to deal with that used this same pattern, I've watched it happen... it was HR software. Let me tell you how happy people were to NOT be paid on time due to a "bug" by one of the devs writing a new SP that erroneously updated the context info for the session from what it was "supposed" to be. Just giving an example I've actually witnessed of why not to use this method. – Sean Gallardy Nov 14 '14 at 15:46
  • @SeanGallardy Ok, thanks for clarifying that point. But it is still only a partially valid point. In order for that situation to happen, that "other" proc would have to be called inside of this one. Or, if you are talking about some other proc that might be deleting from this table and kicking off the trigger, that is something that can be tested for. It is a race condition, which is something to be accounted for (just as they are in all multithreaded apps), and not a reason to not use this technique. And so I will make a minor update to do just that. Thank you for bringing this possiblity up. – Solomon Rutzky Nov 14 '14 at 15:55
  • 2
    I'm saying security as an after thought is the main problem and this isn't the tool to solve it. Memo structures or other uses that don't break the application, sure I have no problem. It absolutely is a reason to NOT use it. YMMV but I would never use something so volatile and unstructured for something that is important such as security. Using any type of shared user writable storage for security is a terrible idea overall. Proper design would remove the need for things such as this, for the most part. – Sean Gallardy Nov 14 '14 at 19:55
5

You can't that way, unless you are looking to record the SQL server user ID rather than an application level one.

You can do a soft delete by having a column called DeletedBy and setting that as needed, then your update trigger can do the real delete (or archive the record, I generally avoid hard deletes where possible and legal) as well as updating your audit trail. To force deletes to be done that way define an on delete trigger that raises an error. If you don't want to add a column to your physical table, you could define a view that adds the column and define instead of triggers to handle updating the base table, but that may be overkill.

  • I see your point. I would indeed be looking to log the application level user. – webworm Nov 13 '14 at 20:34
  • David, actually you can pass info to triggers. Please see my answer for details :). – Solomon Rutzky Nov 13 '14 at 22:32
  • Good suggestion here, I really like this route. Kills two birds by capturing Who in the same step as triggering the real delete. Since this column is going to be NULL for every record in this table, it seems like it would be a good use of the SQL Server SPARSE column? – Airn5475 Sep 21 '18 at 15:07
2

Is there a way to pass information onto the Delete trigger such that it could know who deleted the record?

Yes, apparently there are two ways ;-). If there are any reservations about using CONTEXT_INFO as I have suggested in my other answer here, I just thought of another way that has a cleaner functional separation from other code / processes: use a local temporary table.

The temp table name should include the table name being deleted from as that will help keep it separate from any other code that might happen to run in the same session. Something along the lines of:
#<TableName>DeleteAudit

One benefit to a local temp table over CONTEXT_INFO is that if someone in another proc--that is somehow call from this particular "Delete" proc--just happens to incorrect use the same temp table name, the subprocess will a) create a new local temp table of the requested name that will be separate from this initial temp table (even though it has the same name), and b) any DML statements against the new local temp table in the sub-process won't affect any data in the local temp table created here in the parent process, hence no overwriting of data. Of course, if a subprocess issues a DML statement against this temp table name without first issuing a CREATE TABLE of that same name, then those DML statements will affect the data in this table. BUT, at this point we are getting really edge-casey here, even more so than with the probability of overlapping uses of CONTEXT_INFO (yes, I know it has happened, which is why I say "edge-case" rather than "it'll never happen").

  1. The app should call a "Delete" stored procedure that passes in the UserName (or whatever) that is deleting the record. I assume this is already the model being used since it sounds like you are already tracking Insert and Update operations.

  2. The "Delete" stored procedure does:

    CREATE TABLE #MyTableDeleteAudit (UserName VARCHAR(50));
    INSERT INTO #MyTableDeleteAudit (UserName) VALUES (@UserName);
    
    -- DELETE STUFF HERE
    
  3. The audit trigger does:

    -- Set the datatype and length to be the same as the [UserWhoMadeChanges] field
    DECLARE @UserName VARCHAR(50);
    IF (OBJECT_ID(N'tempdb..#TriggerTestDeleteAudit') IS NOT NULL)
    BEGIN
       SELECT @UserName = UserName
       FROM #TriggerTestDeleteAudit;
    END;
    
    -- catch the following conditions: missing table, no rows in table, or empty row
    IF (@UserName IS NULL OR @UserName NOT LIKE '%[a-z]%')
    BEGIN
      /* -- uncomment if undefined UserName == badness
       ROLLBACK TRAN; -- cancel the DELETE operation
       RAISERROR('Please set UserName via #TriggerTestDeleteAudit and try again.', 16 ,1);
       RETURN; -- exit
      */
      /* -- uncomment if undefined UserName gets default value
       SET @UserName = '<unknown>';
      */
    END;
    
    INSERT INTO AuditTable (IdOfRecordedAffected, UserWhoMadeChanges) 
       SELECT del.ID, @UserName
       FROM DELETED del;
    

    I have tested this code in a trigger and it works as expected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.