Sure understood. Here is an example script that shows what I want.

Basically, I want to only create an audit when a user changes data for the table "tableName". I use the output clause right now to determine if a change was made. The audit trail that this query outputs will ignore the initial inserts of null (which is what I want), and report the changes that each of the users made and when they made them. This should be a self-contained repo. The procedure is pretty similar to what is actually called.

I absolutely despise how verbose the way the is variable changed check is

convert(binary(1),isnull(nullif(inserted.col1,deleted.col1),nullif(deleted.col1,inserted.col1))),

[/sql]

It would be much simpler to have something like convert(binary(1),(select inserted.col1 intersect inserted.col2))

use tempdb;

GO

if (object_id('tableName') is not null)

drop table tableName

create table tableName

(

id int not null identity primary key clustered,

col1 int null,

col2 varchar(255) null,

isActive bit not null default (1)

)

if (object_id('auditName') is not null)

drop table tableName

create table audit

(

auditId int not null identity primary key clustered,

tableName sysname not null,

attribute sysname not null,

value sql_variant null,

tableId int not null,

auditUser sysname not null,

auditdate datetime not null

)

if(object_id('upsertTableName') is not null)

drop procedure upsertTableName

Go

create procedure upsertTableName

(

@Id int,

@col1 int,

@col2 varchar(255),

@isActive bit,

@userName varchar(255)

)

AS

BEGIN

declare @auditDate datetime = getdate();

declare @ai table(

id int,

col1 bit,

col2 bit,

isActive bit

)

begin tran

;merge tableName as target

using (select @Id [id],

@col1 [col1],

@col2 [col2],

@isActive [isActive]) src

on target.id=src.id

when matched then

update set

target.col1 = src.col1,

target.col2 = src.col2,

target.isActive = src.isActive

when not matched by target then

insert(col1,col2,isActive)

values(col1,col2,isActive)

output

inserted.$identity [id],

convert(binary(1),isnull(nullif(inserted.col1,deleted.col1),nullif(deleted.col1,inserted.col1))),

convert(binary(1),isnull(nullif(inserted.col2,deleted.col2),nullif(deleted.col2,inserted.col2))),

convert(binary(1),isnull(nullif(inserted.isActive,deleted.isActive),nullif(deleted.isActive,inserted.isActive)))

into @ai(id,col1,col2,isActive)

output inserted.id [id];

insert into audit(TableName,attribute,Value,TableId,auditdate,auditUser)

select 'TableName',target.attribute,target.value,z.id,@auditDate,@userName

from @ai z

cross apply (VALUES

('col1',convert(sql_variant,@col1),z.col1),

('col2',convert(sql_variant,@col2),z.col2),

('isActive',convert(sql_variant,@isActive),z.isActive)

) target (attribute,value,includeIfNotNull)

where target.includeIfNotNull is not null

commit

END

GO

declare @t table(id int)

declare @id int;

insert @t

exec upsertTableName null,null,null,1,'jim'

select @id = (select * from @t)

waitfor delay '00:00:00.5'

;exec upsertTableName @id,1,'a',1,'eric'

waitfor delay '00:00:00.5'

;exec upsertTableName @id,2,'a',1,'susan'

waitfor delay '00:00:00.5'

;exec upsertTableName @id,null,null,0,'john'

select attribute,value,audituser FROM audit

where tableId = @id

and tableName = 'tableName'

order by auditDate

/*

attributevalueaudituser

isActive1jim

col11eric

col2aeric

col12susan

col1NULLjohn

col2NULLjohn

isActive0john

*/

drop table audit

drop table tableName

drop procedure upsertTableName