How to Capture Deleted Records

  • Darko Martinović

    Hall of Fame

    Points: 3535

    Comments posted to this topic are about the item How to Capture Deleted Records

  • Senchi

    Ten Centuries

    Points: 1124

    simple trigger and an audit table...

    CREATE TRIGGER tr_audit ON twitter FOR INSERT, UPDATE, DELETE

    AS

    DECLARE @bit INT ,

    @field INT ,

    @maxfield INT ,

    @char INT ,

    @fieldname VARCHAR(128) ,

    @TableName VARCHAR(128) ,

    @PKCols VARCHAR(1000) ,

    @sql VARCHAR(2000),

    @UpdateDate VARCHAR(21) ,

    @UserName VARCHAR(128) ,

    @Type CHAR(1) ,

    @PKSelect VARCHAR(1000)

    --You will need to change @TableName to match the table to be audited

    SELECT @TableName = 'twitter'

    -- date and user

    SELECT @UserName = SYSTEM_USER ,

    @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112)

    + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)

    -- Action

    IF EXISTS (SELECT * FROM inserted)

    IF EXISTS (SELECT * FROM deleted)

    SELECT @Type = 'U'

    ELSE

    SELECT @Type = 'I'

    ELSE

    SELECT @Type = 'D'

    -- get list of columns

    SELECT * INTO #ins FROM inserted

    SELECT * INTO #del FROM deleted

    -- Get primary key columns for full outer join

    SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')

    + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME

    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

    INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

    WHERE pk.TABLE_NAME = @TableName

    AND CONSTRAINT_TYPE = 'PRIMARY KEY'

    AND c.TABLE_NAME = pk.TABLE_NAME

    AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

    -- Get primary key select for insert

    SELECT @PKSelect = COALESCE(@PKSelect+'+','')

    + '''<' + COLUMN_NAME

    + '=''+convert(varchar(100),

    coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''

    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

    INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

    WHERE pk.TABLE_NAME = @TableName

    AND CONSTRAINT_TYPE = 'PRIMARY KEY'

    AND c.TABLE_NAME = pk.TABLE_NAME

    AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

    IF @PKCols IS NULL

    BEGIN

    RAISERROR('no PK on table %s', 16, -1, @TableName)

    RETURN

    END

    SELECT @field = 0,

    @maxfield = MAX(ORDINAL_POSITION)

    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName

    WHILE @field < @maxfield

    BEGIN

    SELECT @field = MIN(ORDINAL_POSITION)

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = @TableName

    AND ORDINAL_POSITION > @field

    SELECT @bit = (@field - 1 )% 8 + 1

    SELECT @bit = POWER(2,@bit - 1)

    SELECT @char = ((@field - 1) / 8) + 1

    IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0

    OR @Type IN ('I','D')

    BEGIN

    SELECT @fieldname = COLUMN_NAME

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = @TableName

    AND ORDINAL_POSITION = @field

    SELECT @sql = '

    insert Audit ( Type,

    TableName,

    PK,

    FieldName,

    OldValue,

    NewValue,

    UpdateDate,

    UserName)

    select ''' + @Type + ''','''

    + @TableName + ''',' + @PKSelect

    + ',''' + @fieldname + ''''

    + ',convert(varchar(1000),d.' + @fieldname + ')'

    + ',convert(varchar(1000),i.' + @fieldname + ')'

    + ',''' + @UpdateDate + ''''

    + ',''' + @UserName + ''''

    + ' from #ins i full outer join #del d'

    + @PKCols

    + ' where i.' + @fieldname + ' <> d.' + @fieldname

    + ' or (i.' + @fieldname + ' is null and d.'

    + @fieldname

    + ' is not null)'

    + ' or (i.' + @fieldname + ' is not null and d.'

    + @fieldname

    + ' is null)'

    EXEC (@sql)

    END

    END

  • sean redmond

    SSCertifiable

    Points: 5740

    Why bother with a trigger?

    Why not use OUTPUT DELETED.* INTO «audit table» as a part of your delete, update or merge statement?

    create table dbo.a( a int identity not null primary key, b varchar(30) );

    go

    insert into dbo.a( b ) select top 100 «textfield» from «table»;

    go

    create table dbo.a_audit( a int not null primary key, b varchar(30) );

    go

    delete a output deleted.* into dbo.a_audit from dbo.a where b like 'S%';

    go

    select * from dbo.a;

    select * from dbo.a_audit;

  • Darko Martinović

    Hall of Fame

    Points: 3535

    It means a change of the client code. Usually software vendors measured your code in GB. It means there are a lot of applications. So, your idea is good 🙂 but it is not option.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    The course we took on a very limited number of tables was to create a deleted table and a trigger to populate it when the original table deleted a record.

  • Lynn Pettis

    SSC Guru

    Points: 442180

    Only issue I really have is that the user can control if the trigger fires or not. Since this is basically an audit feature the users should not have this capability.

  • Darko Martinović

    Hall of Fame

    Points: 3535

    Hi,

    Thank you for your observation :-).

    I haven't emphasized enough scenario for using this utility.

    Visibility of this button should depends of your application rights.

    Usually only user with highest privileges can do this.

    In my case, that means some of my colleagues who work in customer support.

    There are many more details I could have added in my article, but I made it shorter in order to be more readable.

  • Lynn Pettis

    SSC Guru

    Points: 442180

    Darko Martinovic (12/19/2016)


    Hi,

    Thank you for your observation :-).

    I haven't emphasized enough scenario for using this utility.

    Visibility of this button should depends of your application rights.

    Usually only user with highest privileges can do this.

    In my case, that means some of my colleagues who work in customer support.

    There are many more details I could have added in my article, but I made it shorter in order to be more readable.

    I can't think of any valid reason why you'd want to give anyone a way to disable auditing of DELETEs of your data if management wants to know who and when data was deleted from audited tables. Defeats the reason for auditing in the first place.

  • Br. Kenneth Igiri

    SSCarpal Tunnel

    Points: 4522

    Great Article. Thanks so much. I particularly like the fact that you transform the row to XML. Neat.

    maybe it can even be extended... store the audit data in a separate database entirely... thinking...

    Br. Kenneth Igiri
    www.scribblingsage.com
    All nations come to my light, all kings to the brightness of my rising

  • Darko Martinović

    Hall of Fame

    Points: 3535

    Thanks Lynn for your response :-),

    When you have a lot of customers and start capturing deleted records on many tables, there is situation when you noticed that customer „A“ does not require such functionality on table „B“. In that case it is nice to have hidden button, that could be used by you or by yours customer support to switch off capturing.

    Initial setup goes like this. You receive request from your customer „C“, to start capturing deleted records on table „D,E and F“. Some of that table is part of standard database model, some not. The Standard model means table like „Customers“, „Employees“. You made DDL changes and setup initial state of capturing to off. This ensure that capturing will not start to other customers on these particular tables.

    Then, again it is nice to have a hidden button that sees only high level of application users. It means someone who work on support in your company, who has less technical expirience but know reading manual and know how to click on button.

    🙂

  • Darko Martinović

    Hall of Fame

    Points: 3535

    Thank you Br. Kenneth Igiri for your response :-),

    Yes, solution could be extended in many ways. To be honest, in this article I show just a part of solution. The reason is obvious, in that case the article will be 10x larger. It means not so readable.

    At first it seems that the problem is trivial, but obviously is not.

    Naming convention is very important.

    It means, separate table of deleted records in a new scheme. Otherwise, in systems with a thousand or even more tables you, as author, are completely lost.

    Separation tables of deleted record to other database is a good idea :-), but is connected to the rights of database users. Also, means more time spent on management.

  • TheSQLGuru

    SSC Guru

    Points: 134017

    To the OP: You state "does not cause much system load". I challenge you to actually do a benchmark to prove that assertion. You may be surprised by the results.

    To Senchi: That is a STUNNINGLY inefficient auditing trigger. Among the issues are the fact that it fires for ALL DML and thus carries a lot of wasted effort to decide which event type is firing and you copy all of inserted and deleted into temp tables.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Darko Martinović

    Hall of Fame

    Points: 3535

    Thank you Kevin for your response :-),

    I accept the challenge and if the editor find that it will be interesting I'll be happy to publish benchmark.

    First, I'd like to say this article is about some kind of luxury.

    The term luxury is the right term because it illustrates the fact that the customer can see the contents of deleted records enriched with additional information in real time.

    There are several moments that may burden the system. The first is the fact that the trigger occurs.

    The real question: Is there is an alternative to this? Log readers? But this is potentially an even greater overhead and it involves a third party tools, which means licensing.

    Using service broker? In my opinion implementation and debugging is much more difficult.

    So, I will always choose triggers.

    The physical design of the table on which the deletion occurs is next important element. If it poorly designed, certainly will cause additional overhead. But then the same must also be present in the daily work with this table.

    So, before implementing capturing of deleted records, I should ask myself what column types I'm using. If I have a lot of columns with type nvarchar(max), maybe is not bad idea to do vertical partitioning.

    Alternatively, we can consider the fact that we do not store all but only some columns. In this sense, we need to replace "universal" trigger and apply special in that case.

    In this article is included .net test application. Loading the windows form certainly is overhead at very large tables . Well, here we can use additional criteria.

    We can do paging, it means load a portion of table. Further more we can include another criteria for purging records.

    So in addition to the number of days we can include number of records.

    I would say finally, after this lengthy comments that this article talks about a luxury that for a large number of tables works smoothly.

    It means with minimal overhead.

    Naturally for special cases, the special solutions should be considered.

    As I said at the beginning, benchmark, why not, if the editor find this interesting.

    Merry Christmas :-).

  • Senchi

    Ten Centuries

    Points: 1124

    To TheSQLGuru:

    Inefficient ? Why ? The trigger only fires on the table you use it on.

    You can always see what was changed and when and by who But, is not that the general idea?

    Second, the logging table is pure text so it can grow without any worries.An its contents get regularly deleted. So why are you so 'stunned' ? 🙂

  • TheSQLGuru

    SSC Guru

    Points: 134017

    I didn't dig into your code closely, but two things:

    1) Auditing should always be three separate triggers IMHO. Each is then coded to do just what it needs to do. No wasted effort checking what type of action is firing.

    2) It seems you put all of inserted and deleted into temp tables just to get the schema? If so why don't you just use the schema of the existing known table the trigger is based on? If for some reason you can't do that, then can't you just create the temp tables with no rows (WHERE 1 = 0) if you just need the schema?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply