Insert triggers to eliminate sensitive information

Question

A vendor system (i.e. outside of my control) is inserting a row into a table into a database that I do control. One column in this insert will have sensitive information that I do not want in my database.

If I use an INSTEAD OF INSERT trigger to blank out this column, will the sensitive information still end up in the transaction log?

Answer 1

Yes this will prevent the information showing up in the log. The transaction log only needs to record sufficient information to recover the row that was actually written to the data page. You can see this from using sys.fn_dblog as below.

CREATE TABLE dbo.T
  (
     X      int CONSTRAINT PK PRIMARY KEY,
     String varchar(50) NULL
  )

GO

CHECKPOINT;

INSERT INTO dbo.T
VALUES      (1,
             'Private and Confidential')

/*Without INSTEAD OF trigger this phrase found in log record*/
SELECT CASE
         WHEN [Log Record] LIKE '%Private and Confidential%' THEN 1
         ELSE 0
       END,
       [RowLog Contents 0]
FROM   sys.fn_dblog(NULL, NULL)
WHERE  Operation = 'LOP_INSERT_ROWS'
       AND AllocUnitName = 'dbo.T.PK'

GO

CREATE TRIGGER TR
ON dbo.T
INSTEAD OF INSERT
AS
  BEGIN
      SET NOCOUNT ON;

      INSERT INTO dbo.T
                  (X)
      SELECT X
      FROM   INSERTED
  END

go

CHECKPOINT;

INSERT INTO dbo.T
VALUES      (2,
             'Private and Confidential')

GO

/*With INSTEAD OF trigger this phrase no longer found in log record*/
SELECT CASE
         WHEN [Log Record] LIKE '%Private and Confidential%' THEN 1
         ELSE 0
       END,
       [RowLog Contents 0]
FROM   sys.fn_dblog(NULL, NULL)
WHERE  Operation = 'LOP_INSERT_ROWS'
       AND AllocUnitName = 'dbo.T.PK' 

The construction of the INSERTED pseudo table takes place in tempdb so this information may appear in the data files there in passing however.