Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Feb 24 '12 at 17:05

This question came from our site for professional and enthusiast programmers.

1  
Who's reading the transaction log? –  Aaron Bertrand Feb 24 '12 at 16:16
1  
Hopefully nobody. But I will sleep better knowing this sensitive information isn't being stored anywhere (whether in the log or in a log backup). –  dmc Feb 24 '12 at 20:05

1 Answer 1

up vote 7 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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