December 18, 2016 at 12:16 pm
Comments posted to this topic are about the item How to Capture Deleted Records
December 19, 2016 at 1:12 am
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
December 19, 2016 at 1:57 am
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;
December 19, 2016 at 2:10 am
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.
December 19, 2016 at 7:08 am
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.
December 19, 2016 at 8:50 am
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.
December 19, 2016 at 1:14 pm
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.
December 19, 2016 at 1:31 pm
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.
December 19, 2016 at 5:44 pm
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
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
December 20, 2016 at 12:15 am
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.
🙂
December 20, 2016 at 12:42 am
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.
December 24, 2016 at 9:13 am
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
December 24, 2016 at 1:12 pm
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 :-).
December 25, 2016 at 4:45 am
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' ? 🙂
December 25, 2016 at 10:26 am
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 18 total)
You must be logged in to reply to this topic. Login to reply