Get only modified columns in update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get only modified columns in update

Author

Topic

Pepro
Starting Member

4 Posts

Posted - 2008-07-25 : 16:25:03

Let's say that we have a table

CREATE TABLE [dbo].[TestTable1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NULL,
[abc] [varchar](50) NULL,
[RType] [int] NULL)

When developers create Data Access stored procedures we create like this

create procedure AddUpdTestTable1
@name varvhar(50),
@abc varchar (50),
@Rtype int,
@id int
as
if @Id > 0 --Update
update TestTable1 set [name]=@name, [abc] = @abc, [Rtype]= @RType where Id = @id
else
insert into TestTable1 ([name],[abc],[Rtype]) values (@name,@abc,@Rtype)

That stored procedure is created on th sake of functionallity of to perform further validation on the data base side
But It was one down side, it updates every field event if it has the same value
But What if I want to create and audit log that shows me which columns and row has been modified on the db side?

I'd tried many approaches of course trying to avoid using dynamic sql construction, innecesary overhead and I came wih this solution

I added a trigger that detects changes and then stores changed column into a a given table like this:

create trigger AuditLog on TestTable1
with encryption
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON
declare @Columns varchar(max)
set @Columns = ''

select
@Columns = @Columns + case TT1.name when TT2.name then '' else 'nombre,' End,
@Columns = @Columns + case TT1.abc when TT2.abc then '' else 'abc,' End ,
@Columns = @Columns + case TT1.Rtype when TT2.Rtype then '' else 'Rtype,' End
from inserted TT1 inner join (select Id, [name], abc, RType from deleted) TT2
ON TT1.Id = TT2.Id

print @Columns
--Perfom audit log
End

Of course this trigger only works properly when updating one record it won't show an accurate result when the update is performed on many records

Does anyone have a better solution for this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-25 : 16:28:08

See my trigger tip blog: http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Pepro
Starting Member

4 Posts

Posted - 2008-07-25 : 16:51:32

something like:

alter trigger Prueba on TestTable1
with encryption
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON

declare @ColumnChange table (Id int, ColumnsChanged varchar(max))

insert into @ColumnChange
select TT1.id,
case TT1.nombre when TT2.nombre then '' else 'nombre,' End +
case TT1.abc when TT2.abc then '' else 'abc,' End +
case TT1.tipo when TT2.tipo then '' else 'tipo,' End
from inserted TT1 inner join deleted TT2
ON TT1.Id = TT2.Id

--perform some other logic and then sabe to audit log table
select * from @columnChange
End

But I want to know is there a way to do this in a generic kind of way .. I mean for every table without the need to write such TSQL but with one expression

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-25 : 17:04:59

There's really no generic way to do it, but you can use code like this to generate it for you:
http://weblogs.sqlteam.com/brettk/archive/2006/08/10/11126.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Pepro
Starting Member

4 Posts

Posted - 2008-07-25 : 18:05:34

I managed to create a script that can be used to dynamically create the trigger or to do it with the current table Trigger, I would like to know your opinion

alter trigger Prueba on TestTable1
with encryption
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON

declare @ColumnChange table (Id int, ColumnsChanged varchar(max))

declare @KeyDef nvarchar(128)
SELECT @KeyDef = isc.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS AS isc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcs ON isc.TABLE_NAME = kcs.TABLE_NAME AND isc.COLUMN_NAME = kcs.COLUMN_NAME AND LEFT(kcs.CONSTRAINT_NAME, 2) = 'PK' WHERE (isc.TABLE_NAME = 'TestTable1')

declare @Query varchar(max)
set @Query = 'select i.' + @KeyDef + ' as Id,'

select @Query = @Query + ' CASE i.' + column_name + ' WHEN d.' + column_name + ' then '''' else ''' + column_name + ','' End + '
from information_schema.columns where table_name= 'TestTable1' and column_name <> @KeyDef

set @Query = @Query + ' '''' from #inserted i inner join #deleted d on i.' + @KeyDef +' = d.' + @KeyDef

print @query
select * into #Inserted from inserted
select * into #Deleted from deleted
insert into @ColumnChange exec (@query)
drop table #Inserted
drop table #Deleted

--do audit log
select * from @ColumnChange
End

Too much overhead???

Pepro
Starting Member

4 Posts

Posted - 2008-07-25 : 18:08:55

I built the script with table schema in mind ... what happens if the table design changes? we'd need to update the trigger as well... of course it can be encapsulated in a function to return the query string to be executed dinamically

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources