Auditing: some useful (?) code and a question

I'm still trying to decide the "right" solution for auditing, but here's one
solution I've been looking at. It uses dynamic SQL in order to be able to
handle changes to the underlying tables without changes. There's also some
code (not included) that sets up the auditing table based on the underlying
table.
My question: I notice this results in lots of "updates" where nothing
changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
statement", which is not the same thing at all!
Any suggestions on the best way to say "insert this record, but only if
something really changed"?
-- start off the SQL
select @.sql = 'insert mauryaudit (transactionType, transactionUser,
transactionDate' + CHAR(13) + CHAR(10)
-- add the field list
select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close the field list and start the select statement
select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
@.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
-- continue the select statement
select @.field = 0
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close it off
select @.sql = @.sql + ' from #ins i'
exec (@.sql)
See
http://www.nigelrivett.net/AuditTrailTrigger.html
http://www.nigelrivett.net/GenerateTriggerForAudit.html
And probably all the articles unser triggers on www.nigelrivett.net.
To only log changes you will have to check the before and after values of
each field.
Not that you will have problems if there is a blob in the table.
http://www.nigelrivett.net/Columns_updated.html
And has bigger problems if the column order has moved due to an alter table.
columns_updated also gets a bit awkward for more than 32 columns
"Maury Markowitz" wrote:

> I'm still trying to decide the "right" solution for auditing, but here's one
> solution I've been looking at. It uses dynamic SQL in order to be able to
> handle changes to the underlying tables without changes. There's also some
> code (not included) that sets up the auditing table based on the underlying
> table.
> My question: I notice this results in lots of "updates" where nothing
> changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
> statement", which is not the same thing at all!
> Any suggestions on the best way to say "insert this record, but only if
> something really changed"?
> -- start off the SQL
> select @.sql = 'insert mauryaudit (transactionType, transactionUser,
> transactionDate' + CHAR(13) + CHAR(10)
> -- add the field list
> select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
> INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close the field list and start the select statement
> select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
> @.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
> -- continue the select statement
> select @.field = 0
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close it off
> select @.sql = @.sql + ' from #ins i'
> exec (@.sql)
|||"Nigel Rivett" wrote:

> See
> http://www.nigelrivett.net/AuditTrailTrigger.html
> http://www.nigelrivett.net/GenerateTriggerForAudit.html
If I am reading them correctly, the "trick" is to join on a per-column
basis, thereby generating no inserts for those fields that do not have an
actual change. However it is not so clear how to do this in my trigger, where
the entire change "set" is entered as a single row instead of looping.
I could add another loop to check to see if any columns actually changed
(comparing inserted and deleted as you do), but it would see that it would be
much more effeceint to do this in a single SQL statement. I could write a
loop/exec to do that, but I'm curious if you could suggest a better way to
handle that? Is there some SQL that will compare one entire row with another
without listing every column?
Maury
|||"Nigel Rivett" wrote:
I tried the obvious and expanded the loop to build three portions of the
insert...
1) the INSERT portion with the field list
2) the SELECT portion taking the values from inserted
3) the WHERE section comparing inserted and deleted
Sadly the result is >8000 chars, and fails. Is there some way to make the
statement significantly shorter? The checks for before/after nulls takes up
most of the room, is there some easier way to do this with the equality
check? Perhaps using ANSI/non-ANSI nulls?

Auditing: some useful (?) code and a question

I'm still trying to decide the "right" solution for auditing, but here's one
solution I've been looking at. It uses dynamic SQL in order to be able to
handle changes to the underlying tables without changes. There's also some
code (not included) that sets up the auditing table based on the underlying
table.
My question: I notice this results in lots of "updates" where nothing
changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
statement", which is not the same thing at all!
Any suggestions on the best way to say "insert this record, but only if
something really changed"?
-- start off the SQL
select @.sql = 'insert mauryaudit (transactionType, transactionUser,
transactionDate' + CHAR(13) + CHAR(10)
-- add the field list
select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close the field list and start the select statement
select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
@.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
-- continue the select statement
select @.field = 0
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close it off
select @.sql = @.sql + ' from #ins i'
exec (@.sql)See
http://www.nigelrivett.net/AuditTrailTrigger.html
http://www.nigelrivett.net/GenerateTriggerForAudit.html
And probably all the articles unser triggers on www.nigelrivett.net.
To only log changes you will have to check the before and after values of
each field.
Not that you will have problems if there is a blob in the table.
http://www.nigelrivett.net/Columns_updated.html
And has bigger problems if the column order has moved due to an alter table.
columns_updated also gets a bit awkward for more than 32 columns
"Maury Markowitz" wrote:
> I'm still trying to decide the "right" solution for auditing, but here's one
> solution I've been looking at. It uses dynamic SQL in order to be able to
> handle changes to the underlying tables without changes. There's also some
> code (not included) that sets up the auditing table based on the underlying
> table.
> My question: I notice this results in lots of "updates" where nothing
> changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
> statement", which is not the same thing at all!
> Any suggestions on the best way to say "insert this record, but only if
> something really changed"?
> -- start off the SQL
> select @.sql = 'insert mauryaudit (transactionType, transactionUser,
> transactionDate' + CHAR(13) + CHAR(10)
> -- add the field list
> select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
> INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close the field list and start the select statement
> select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
> @.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
> -- continue the select statement
> select @.field = 0
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close it off
> select @.sql = @.sql + ' from #ins i'
> exec (@.sql)|||"Nigel Rivett" wrote:
> See
> http://www.nigelrivett.net/AuditTrailTrigger.html
> http://www.nigelrivett.net/GenerateTriggerForAudit.html
If I am reading them correctly, the "trick" is to join on a per-column
basis, thereby generating no inserts for those fields that do not have an
actual change. However it is not so clear how to do this in my trigger, where
the entire change "set" is entered as a single row instead of looping.
I could add another loop to check to see if any columns actually changed
(comparing inserted and deleted as you do), but it would see that it would be
much more effeceint to do this in a single SQL statement. I could write a
loop/exec to do that, but I'm curious if you could suggest a better way to
handle that? Is there some SQL that will compare one entire row with another
without listing every column?
Maury|||"Nigel Rivett" wrote:
I tried the obvious and expanded the loop to build three portions of the
insert...
1) the INSERT portion with the field list
2) the SELECT portion taking the values from inserted
3) the WHERE section comparing inserted and deleted
Sadly the result is >8000 chars, and fails. Is there some way to make the
statement significantly shorter? The checks for before/after nulls takes up
most of the room, is there some easier way to do this with the equality
check? Perhaps using ANSI/non-ANSI nulls?

Attaching data at differnet levels in a heirchy

I don't think this is too complex, but I can't figure out what what the correct solution is.

I have 6 tables: Customer, Project, Rate, Hours, EquipmentType, Equipment

All projects belong to a customer, so customers have a PK CustomerId and Project has a PK {CustomerId, ProjectId} where CustomerId is a FK to Customer.

A rate can be defined as being at a project level or a Customer level. A project must specify if it is using the customer level rates or its own rates.

So what I wanted to do at first was creat the key in Rate as {CustomerId, ProjectId NULLABLE, EquipmentTypeId}. The idea being that a NULL ProjectId would mean that the Rate was Customer level since it didn't point to a project.

But of course that won't work. In addition to SQL Server not allowing a PK with a NULLABLE it would also mess up my relationship between Rate and Hours.

I wanted to specify in Hours the Rate, Project (and Customer), and Equipment and Equipment type.

I didn't want to create 2 rate tables, one for Customer level rates and one for Project level rates, but is that what I should have done?

I hope this is a clear explanation.

Does anyone have a better idea how to create what I want.It is hard to give you advice without seeing any data.
But I don't think Project table should have CustormerID at all.
Usually people create Customer table with CustormerID PK and
Project table with ProjectID PK and in between for multi-to-multi relationships you should have CustomerProgect table with CustomerID, ProjectID PK and CustomerID FK and ProjectID FK.
The same is with a Rate. Just create Rate table with column RateType where you can store C or P meaning Customer or Project type rate.
And in between just create Customer_Rate table with CustormerID, RateID PK and corresponding foreign keys.

Hope it helps.