Columns_Updated () and Update() functions

Hello all,

I'm trying to apply an audit DB trigger on a master-detail tables ,I do not need to insert record in the audit master every time an update happend in the audit detail,I tried to use columns_updated() > 0 but it didn't work the way I axpected it to be ..it stopped inserting into the audit master even if an update was applied against the master table ...any help please and I use the Update() function ? is their any major difference?

your help is appreciated

Thanks

Alaa M

COLUMNS_UPDATED() returns a value as a VARBINARY datatype, so for your comparison to work you need to convert the value to an INT first, like this:

CAST(COLUMNS_UPDATED() AS INT)

Chris

|||

Thanks CHRIS

Actually it didn't work ..

or may be i'm missing something here ,anyway this is the code I'm using if you or anyone can point the wrong thing that I'm doing here will be great.

this is the trigger for the master table ,and I only need to insert the changes applied against this table into Audit master no matter how many updates applied to the detail table.

ALTER Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d
LEFT JOIN [Master] A
ON d.MasterID = A.MasterID
)

if (CAST(COLUMNS_UPDATED() AS INT ) & 255) > 0

begin
INSERT INTO [dbo].[syslogAuditMaster](
[MasterID], [Name], [CodeIDType], [DateStarted], [DateDue], [CodeIDReminderType],
[CodeIDAuditStatus], [NoteID], [DeletedOnDate], [Event])
Select del.[MasterID],del.[Name],del.[CodeIDType],del.[Datestarted],del.[Datedue],del.[CodeIDReminderType],
del.[CodeIDAuditStatus], del.[NoteID], del.[DeletedOnDate], 'UPDATE'
FROM deleted del
WHERE del.MasterID = MastertId

end;

thank

|||

I get the feeling that you might be trying to over-complicate things. I can't see the point of explicitly checking for updated columns using the COLUMNS_UPDATED() function - the fact that at least one row has been updated is implied by the existence of rows in the 'deleted' virtual table.

I'm also confused by the WHERE clause in the INSERT statement:

WHERE del.MasterID = MastertId

Does the MastertId column exist in the 'deleted' virtual table? If so does MastertId ever equal del.MasterID?

Would the re-worked example below work for you?

Chris

ALTER TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

AS

IF EXISTS (SELECT 1 FROM deleted)

BEGIN

INSERT INTO [dbo].[syslogAuditMaster]

([MasterID],

[Name],

[CodeIDType],

[DateStarted],

[DateDue],

[CodeIDReminderType],

[CodeIDAuditStatus],

[NoteID],

[DeletedOnDate],

[Event])

SELECT del.[MasterID],

del.[Name],

del.[CodeIDType],

del.[Datestarted],

del.[Datedue],

del.[CodeIDReminderType],

del.[CodeIDAuditStatus],

del.[NoteID],

del.[DeletedOnDate],

'UPDATE'

FROM deleted del

END

|||

Thnaks for your replay,

I think you are missing the whole point of my question here ,which is to avoid the repetition in the Header or Master table when any update done against the Detailed table ..

I'm not sure if the "deleted and insered" tables hold a data record at the time or do they hold a set of records till the commit time .

it helped when I compared the "deleted " against the "inserted" data and by setting flag for any changes the insert to the Audit table was done.

although I'm not quite sure that is a good solution ,that I always think of using the functions will be more reliable and that was my resaon of asking about the Columns_Updated () and how far can I use it .

Thanks for your replies ..

|||

Just to clarify your requirement could you post examples of data in your Master table before and after the data has been updated, along with an example of the data that you would expect to be inserted into the syslogAuditMaster table as a result of the update?

Also if you could post the DDL statements used to create the Master and syslogAuditMaster tables then that would be extremely useful.

Thanks

Chris

|||

Hi Chris,

Thanks for your reply,

the DDL statements used to create the Master ,Detail and syslogAuditMaster are

--for the Master table

CREATE TABLE [dbo].[Master] (
[MasterID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CodeIDType] [int] NOT NULL ,
[DateStarted] [datetime] NULL ,
[DateDue] [datetime] NULL ,
[CodeIDReminderType] [int] NULL ,
[CodeIDAuditStatus] [int] NULL ,
[NoteID] [int] NULL ,
[DeletedOnDate] [datetime] NULL
) ON [PRIMARY]

--for the Detail table

CREATE TABLE [dbo].[Detail] (
[DetailID] [int] IDENTITY (1, 1) NOT NULL ,
[MasterID] [int] NOT NULL ,
[Datestarted] [datetime] NULL , [NoteID] [int] NULL,[ColA] ...[ColB]..etc

) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Detail] ADD
CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED
(
[DetailID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AuditDetail] ADD
CONSTRAINT [Master_Detail_FK1] FOREIGN KEY
(
[MasterID]
) REFERENCES [dbo].[Master] (
[MasterID]
)GO

-- for the Audit table

CREATE TABLE [dbo].[syslogAuditMaster] (
[MasterID] [int] NOT NULL,
[Name] [nvarchar] (254) NOT NULL ,
[CodeIDType] [int] NOT NULL ,
[DateStarted] [datetime] NULL ,
[DateDue] [datetime] NULL ,
[CodeIDReminderType] [int] NULL ,
[CodeIDAuditStatus] [int] NULL ,
[NoteID] [int] NULL ,
[DeletedOnDate] [datetime] NULL ,
[ChangedOnDate] [datetime] NOT NULL DEFAULT GETDATE(),
[UserName] [nvarchar] (150) NOT NULL DEFAULT suser_sname(),
[HostName] [nvarchar] (150) NOT NULL DEFAULT HOST_NAME(),
[Event] [nvarchar] (150) NOT NULL
) ON [PRIMARY]

when the user applied anychanges to the detail table through the .NET application , the store proc sp_Update which has the UPDATE statement for both the Master and Detail tables will executed,and as a result the DB trigget will be fired .

--Currently

if the field [NoteId] for [MasterID] = 3344 and [DetailID]=1122 has been changed in the Detail table ,I'll get the following in the syslogAuditMaster

[MasterID] = 3344,with the event type 'UPDATE'

--what to excpect

I should not get anything in the syslogAuditMaster ,cuz there is nothing new with the Master table to be added only if I changed anything within the Master I should get a record inserted in the AuditMaster

this is the DB trigger I'm using currently

Create Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d inner join inserted i on i.MasterID = d.MasterID
where ltrim(rtrim(i.MasterID))+ltrim(rtrim(isnull(i.Name,'')))+ltrim(rtrim(isnull(i.CodeIDtype,'')))+ltrim(rtrim(isnull(i.DateStarted,'')))+ltrim(rtrim(isnull(i.DateDue,'')))+ltrim(rtrim(isnull(i.codeIDReminderType,'')))+ltrim(rtrim(isnull(i.CodeIDAuditStatus,'')))+ltrim(rtrim(isnull(i.NoteID,'')))+ltrim(rtrim(isnull(i.DeletedonDate,'')))
<> ltrim(rtrim(d.MasterID))+ltrim(rtrim(isnull(d.Name,'')))+ltrim(rtrim(isnull(d.CodeIDtype,'')))+ltrim(rtrim(isnull(d.DateStarted,'')))+ltrim(rtrim(isnull(d.DateDue,'')))+ltrim(rtrim(isnull(d.codeIDReminderType,'')))+ltrim(rtrim(isnull(d.CodeIDAuditStatus,'')))+ltrim(rtrim(isnull(d.NoteID,'')))+ltrim(rtrim(isnull(d.DeletedonDate,'')))
)
begin
INSERT INTO [dbo].[syslogAuditMaster](
[MasterID], [Name], [CodeIDType], [DateStarted], [DateDue], [CodeIDReminderType], [CodeIDAuditStatus], [NoteID], [DeletedOnDate], [Event])
Select del.[MasterID],del.[Name],del.[CodeIDType],del.[Datestarted],del.[Datedue],del.[CodeIDReminderType],
del.[CodeIDAuditStatus], del.[NoteID], del.[DeletedOnDate], 'UPDATE'
FROM deleted del
WHERE del.MasterID = MasterId

end;

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

hope this will help ..

Thank again

Ala'a M

|||

Right, I think I now understand where you are going with this.

Basically, even though you are issuing an UPDATE statement to update a row in the Master table, you might actually not be changing any of the values - in which case you don't want to write an audit row to the syslogAuditMaster table. You, therefore, only want to write a row to the syslogAuditMaster table if at least one of the columns values changes as a result of the update. Is this correct?

The problem with the COLUMNS_UPDATED() and UPDATED() functions is that they will report a column as being updated even if the column's value doesn't actually change as a result of the UPDATE statement. For this reason, if the solution you are using works fine then I see no reason why you shouldn't continue to use it.

Personally, I would move away from the text-based comparison that you are doing and would have explicit checks for each of the columns, see the example below. Note that there is no need for the IF EXISTS statement in this example. Also, if you update several Master rows at a time then the example below will only write audit rows for the Master rows that have actually changed.

Chris

CREATE TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

AS

INSERT INTO [dbo].[syslogAuditMaster](

[MasterID],

[Name],

[CodeIDType],

[DateStarted],

[DateDue],

[CodeIDReminderType],

[CodeIDAuditStatus],

[NoteID],

[DeletedOnDate],

[Event])

SELECT del.[MasterID],

del.[Name],

del.[CodeIDType],

del.[Datestarted],

del.[Datedue],

del.[CodeIDReminderType],

del.[CodeIDAuditStatus],

del.[NoteID],

del.[DeletedOnDate],

'UPDATE'

FROM deleted del

INNER JOIN inserted ins ON ins.MasterID = del.MasterID

WHERE ((del.[Name] <> ins.[Name])

OR (del.CodeIDType <> ins.CodeIDType)

OR ((del.[DateStarted] <> ins.[DateStarted]) OR (del.[DateStarted] IS NULL AND ins.[DateStarted] IS NOT NULL) OR (del.[DateStarted] IS NOT NULL AND ins.[DateStarted] IS NULL))

OR ((del.[DateDue] <> ins.[DateDue]) OR (del.[DateDue] IS NULL AND ins.[DateDue] IS NOT NULL) OR (del.[DateDue] IS NOT NULL AND ins.[DateDue] IS NULL))

OR ((del.[CodeIDReminderType] <> ins.[CodeIDReminderType]) OR (del.[CodeIDReminderType] IS NULL AND ins.[CodeIDReminderType] IS NOT NULL) OR (del.[CodeIDReminderType] IS NOT NULL AND ins.[CodeIDReminderType] IS NULL))

OR ((del.[CodeIDAuditStatus] <> ins.[CodeIDAuditStatus]) OR (del.[CodeIDAuditStatus] IS NULL AND ins.[CodeIDAuditStatus] IS NOT NULL) OR (del.[CodeIDAuditStatus] IS NOT NULL AND ins.[CodeIDAuditStatus] IS NULL))

OR ((del.[NoteID] <> ins.[NoteID]) OR (del.[NoteID] IS NULL AND ins.[NoteID] IS NOT NULL) OR (del.[NoteID] IS NOT NULL AND ins.[NoteID] IS NULL))

OR ((del.[DeletedOnDate] <> ins.[DeletedOnDate]) OR (del.[DeletedOnDate] IS NULL AND ins.[DeletedOnDate] IS NOT NULL) OR (del.[DeletedOnDate] IS NOT NULL AND ins.[DeletedOnDate] IS NULL)))

GO

|||

Thanks alot for giving me the chance to think loudly and exchange thoughts helped me alot ..

thanx Chris

Columns_Updated () and Update() functions

Hello all,

I'm trying to apply an audit DB trigger on a master-detail tables ,I do not need to insert record in the audit master every time an update happend in the audit detail,I tried to use columns_updated() > 0 but it didn't work the way I axpected it to be ..it stopped inserting into the audit master even if an update was applied against the master table ...any help please and I use the Update() function ? is their any major difference?

your help is appreciated

Thanks

Alaa M

COLUMNS_UPDATED() returns a value as a VARBINARY datatype, so for your comparison to work you need to convert the value to an INT first, like this:

CAST(COLUMNS_UPDATED() AS INT)

Chris

|||

Thanks CHRIS

Actually it didn't work ..

or may be i'm missing something here ,anyway this is the code I'm using if you or anyone can point the wrong thing that I'm doing here will be great.

this is the trigger for the master table ,and I only need to insert the changes applied against this table into Audit master no matter how many updates applied to the detail table.

ALTER Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d
LEFT JOIN [Master] A
ON d.MasterID = A.MasterID
)

if (CAST(COLUMNS_UPDATED() AS INT ) & 255) > 0

begin
INSERT INTO [dbo].[syslogAuditMaster](
[MasterID], [Name], [CodeIDType], [DateStarted], [DateDue], [CodeIDReminderType],
[CodeIDAuditStatus], [NoteID], [DeletedOnDate], [Event])
Select del.[MasterID],del.[Name],del.[CodeIDType],del.[Datestarted],del.[Datedue],del.[CodeIDReminderType],
del.[CodeIDAuditStatus], del.[NoteID], del.[DeletedOnDate], 'UPDATE'
FROM deleted del
WHERE del.MasterID = MastertId

end;

thank

|||

I get the feeling that you might be trying to over-complicate things. I can't see the point of explicitly checking for updated columns using the COLUMNS_UPDATED() function - the fact that at least one row has been updated is implied by the existence of rows in the 'deleted' virtual table.

I'm also confused by the WHERE clause in the INSERT statement:

WHERE del.MasterID = MastertId

Does the MastertId column exist in the 'deleted' virtual table? If so does MastertId ever equal del.MasterID?

Would the re-worked example below work for you?

Chris

ALTER TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

AS

IF EXISTS (SELECT 1 FROM deleted)

BEGIN

INSERT INTO [dbo].[syslogAuditMaster]

([MasterID],

[Name],

[CodeIDType],

[DateStarted],

[DateDue],

[CodeIDReminderType],

[CodeIDAuditStatus],

[NoteID],

[DeletedOnDate],

[Event])

SELECT del.[MasterID],

del.[Name],

del.[CodeIDType],

del.[Datestarted],

del.[Datedue],

del.[CodeIDReminderType],

del.[CodeIDAuditStatus],

del.[NoteID],

del.[DeletedOnDate],

'UPDATE'

FROM deleted del

END

|||

Thnaks for your replay,

I think you are missing the whole point of my question here ,which is to avoid the repetition in the Header or Master table when any update done against the Detailed table ..

I'm not sure if the "deleted and insered" tables hold a data record at the time or do they hold a set of records till the commit time .

it helped when I compared the "deleted " against the "inserted" data and by setting flag for any changes the insert to the Audit table was done.

although I'm not quite sure that is a good solution ,that I always think of using the functions will be more reliable and that was my resaon of asking about the Columns_Updated () and how far can I use it .

Thanks for your replies ..

|||

Just to clarify your requirement could you post examples of data in your Master table before and after the data has been updated, along with an example of the data that you would expect to be inserted into the syslogAuditMaster table as a result of the update?

Also if you could post the DDL statements used to create the Master and syslogAuditMaster tables then that would be extremely useful.

Thanks

Chris

|||

Hi Chris,

Thanks for your reply,

the DDL statements used to create the Master ,Detail and syslogAuditMaster are

--for the Master table

CREATE TABLE [dbo].[Master] (
[MasterID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CodeIDType] [int] NOT NULL ,
[DateStarted] [datetime] NULL ,
[DateDue] [datetime] NULL ,
[CodeIDReminderType] [int] NULL ,
[CodeIDAuditStatus] [int] NULL ,
[NoteID] [int] NULL ,
[DeletedOnDate] [datetime] NULL
) ON [PRIMARY]

--for the Detail table

CREATE TABLE [dbo].[Detail] (
[DetailID] [int] IDENTITY (1, 1) NOT NULL ,
[MasterID] [int] NOT NULL ,
[Datestarted] [datetime] NULL , [NoteID] [int] NULL,[ColA] ...[ColB]..etc

) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Detail] ADD
CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED
(
[DetailID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AuditDetail] ADD
CONSTRAINT [Master_Detail_FK1] FOREIGN KEY
(
[MasterID]
) REFERENCES [dbo].[Master] (
[MasterID]
)GO

-- for the Audit table

CREATE TABLE [dbo].[syslogAuditMaster] (
[MasterID] [int] NOT NULL,
[Name] [nvarchar] (254) NOT NULL ,
[CodeIDType] [int] NOT NULL ,
[DateStarted] [datetime] NULL ,
[DateDue] [datetime] NULL ,
[CodeIDReminderType] [int] NULL ,
[CodeIDAuditStatus] [int] NULL ,
[NoteID] [int] NULL ,
[DeletedOnDate] [datetime] NULL ,
[ChangedOnDate] [datetime] NOT NULL DEFAULT GETDATE(),
[UserName] [nvarchar] (150) NOT NULL DEFAULT suser_sname(),
[HostName] [nvarchar] (150) NOT NULL DEFAULT HOST_NAME(),
[Event] [nvarchar] (150) NOT NULL
) ON [PRIMARY]

when the user applied anychanges to the detail table through the .NET application , the store proc sp_Update which has the UPDATE statement for both the Master and Detail tables will executed,and as a result the DB trigget will be fired .

--Currently

if the field [NoteId] for [MasterID] = 3344 and [DetailID]=1122 has been changed in the Detail table ,I'll get the following in the syslogAuditMaster

[MasterID] = 3344,with the event type 'UPDATE'

--what to excpect

I should not get anything in the syslogAuditMaster ,cuz there is nothing new with the Master table to be added only if I changed anything within the Master I should get a record inserted in the AuditMaster

this is the DB trigger I'm using currently

Create Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d inner join inserted i on i.MasterID = d.MasterID
where ltrim(rtrim(i.MasterID))+ltrim(rtrim(isnull(i.Name,'')))+ltrim(rtrim(isnull(i.CodeIDtype,'')))+ltrim(rtrim(isnull(i.DateStarted,'')))+ltrim(rtrim(isnull(i.DateDue,'')))+ltrim(rtrim(isnull(i.codeIDReminderType,'')))+ltrim(rtrim(isnull(i.CodeIDAuditStatus,'')))+ltrim(rtrim(isnull(i.NoteID,'')))+ltrim(rtrim(isnull(i.DeletedonDate,'')))
<> ltrim(rtrim(d.MasterID))+ltrim(rtrim(isnull(d.Name,'')))+ltrim(rtrim(isnull(d.CodeIDtype,'')))+ltrim(rtrim(isnull(d.DateStarted,'')))+ltrim(rtrim(isnull(d.DateDue,'')))+ltrim(rtrim(isnull(d.codeIDReminderType,'')))+ltrim(rtrim(isnull(d.CodeIDAuditStatus,'')))+ltrim(rtrim(isnull(d.NoteID,'')))+ltrim(rtrim(isnull(d.DeletedonDate,'')))
)
begin
INSERT INTO [dbo].[syslogAuditMaster](
[MasterID], [Name], [CodeIDType], [DateStarted], [DateDue], [CodeIDReminderType], [CodeIDAuditStatus], [NoteID], [DeletedOnDate], [Event])
Select del.[MasterID],del.[Name],del.[CodeIDType],del.[Datestarted],del.[Datedue],del.[CodeIDReminderType],
del.[CodeIDAuditStatus], del.[NoteID], del.[DeletedOnDate], 'UPDATE'
FROM deleted del
WHERE del.MasterID = MasterId

end;

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

hope this will help ..

Thank again

Ala'a M

|||

Right, I think I now understand where you are going with this.

Basically, even though you are issuing an UPDATE statement to update a row in the Master table, you might actually not be changing any of the values - in which case you don't want to write an audit row to the syslogAuditMaster table. You, therefore, only want to write a row to the syslogAuditMaster table if at least one of the columns values changes as a result of the update. Is this correct?

The problem with the COLUMNS_UPDATED() and UPDATED() functions is that they will report a column as being updated even if the column's value doesn't actually change as a result of the UPDATE statement. For this reason, if the solution you are using works fine then I see no reason why you shouldn't continue to use it.

Personally, I would move away from the text-based comparison that you are doing and would have explicit checks for each of the columns, see the example below. Note that there is no need for the IF EXISTS statement in this example. Also, if you update several Master rows at a time then the example below will only write audit rows for the Master rows that have actually changed.

Chris

CREATE TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

AS

INSERT INTO [dbo].[syslogAuditMaster](

[MasterID],

[Name],

[CodeIDType],

[DateStarted],

[DateDue],

[CodeIDReminderType],

[CodeIDAuditStatus],

[NoteID],

[DeletedOnDate],

[Event])

SELECT del.[MasterID],

del.[Name],

del.[CodeIDType],

del.[Datestarted],

del.[Datedue],

del.[CodeIDReminderType],

del.[CodeIDAuditStatus],

del.[NoteID],

del.[DeletedOnDate],

'UPDATE'

FROM deleted del

INNER JOIN inserted ins ON ins.MasterID = del.MasterID

WHERE ((del.[Name] <> ins.[Name])

OR (del.CodeIDType <> ins.CodeIDType)

OR ((del.[DateStarted] <> ins.[DateStarted]) OR (del.[DateStarted] IS NULL AND ins.[DateStarted] IS NOT NULL) OR (del.[DateStarted] IS NOT NULL AND ins.[DateStarted] IS NULL))

OR ((del.[DateDue] <> ins.[DateDue]) OR (del.[DateDue] IS NULL AND ins.[DateDue] IS NOT NULL) OR (del.[DateDue] IS NOT NULL AND ins.[DateDue] IS NULL))

OR ((del.[CodeIDReminderType] <> ins.[CodeIDReminderType]) OR (del.[CodeIDReminderType] IS NULL AND ins.[CodeIDReminderType] IS NOT NULL) OR (del.[CodeIDReminderType] IS NOT NULL AND ins.[CodeIDReminderType] IS NULL))

OR ((del.[CodeIDAuditStatus] <> ins.[CodeIDAuditStatus]) OR (del.[CodeIDAuditStatus] IS NULL AND ins.[CodeIDAuditStatus] IS NOT NULL) OR (del.[CodeIDAuditStatus] IS NOT NULL AND ins.[CodeIDAuditStatus] IS NULL))

OR ((del.[NoteID] <> ins.[NoteID]) OR (del.[NoteID] IS NULL AND ins.[NoteID] IS NOT NULL) OR (del.[NoteID] IS NOT NULL AND ins.[NoteID] IS NULL))

OR ((del.[DeletedOnDate] <> ins.[DeletedOnDate]) OR (del.[DeletedOnDate] IS NULL AND ins.[DeletedOnDate] IS NOT NULL) OR (del.[DeletedOnDate] IS NOT NULL AND ins.[DeletedOnDate] IS NULL)))

GO

|||

Thanks alot for giving me the chance to think loudly and exchange thoughts helped me alot ..

thanx Chris

Column By Column Comparison

SQL Server 2000

I will to compare a normal table with a replicated audit table having
identical columns. I wish to report on the differences between the
data.

How can I loop though a "column list" in TSQL rather than explicitly
naming each column to compare ?

Thanks,
CraigDo you just want to know if there IS a difference or what sort of
difference ?

--For the Whole Table
SELECT checksum_agg(binary_checksum(*)) from SomeTable

--For just the rows
Select binary_checksum(*) from SomeTable

If you need more granualarity, then it would be more work.

HTH, Jens Suessmeyer.|||Jens,

This is not 100% reliable. If the checksum functions are
different, there must be a difference in the tables, but the
converse is not true. The checksum functions can return the
same values on different data, like any hash function.

The only sure way is to compare column by column values.

Steve Kass
Drew University

Jens wrote:
> Do you just want to know if there IS a difference or what sort of
> difference ?
> --For the Whole Table
> SELECT checksum_agg(binary_checksum(*)) from SomeTable
> --For just the rows
> Select binary_checksum(*) from SomeTable
> If you need more granualarity, then it would be more work.
> HTH, Jens Suessmeyer.|||Yes I agree with you, definitely. But as I said, to have more
granularity and more reliabilty you have to use another mechanism which
causes more coding. but as I said, I am with you.

Jens Suessmeyer.|||Thanks for the reply.

I have to interrogate each column and determine which is different and
then report on the difference. I was thinking more in the lines of
looping through the columns collection somehow without having to know
the name of each column ...

Thanks again !|||(csomberg@.dwr.com) writes:
> SQL Server 2000
> I will to compare a normal table with a replicated audit table having
> identical columns. I wish to report on the differences between the
> data.
> How can I loop though a "column list" in TSQL rather than explicitly
> naming each column to compare ?

You can get the columns for a table with

SELECT name FROM syscolumns WHERE id = object_name('tbl')

I would suggest that it is best to generate the SQL statement from client
code, since client languages are better apt for string manipulation. You
can build SQL strings in T-SQL as well, and exeute them with EXEC() or
sp_executesql, but this is bulkier.

If you want to be static, one option is to use a client-language to
generate a stored procedure.

Keep in mind that you cannot just say:

SELECT ...
FROM tbl a
JOIN audit_tbl b ON a.keycol = b.keycol
WHERE a.col1 <> b.col1
AND a.col2 <> b.col2
...

Correct is:

WHERE (a.col1 <> b.col1 OR
a.col1 IS NULL AND b.col1 IS NOT NULL OR
a.col1 IS NOT NULL AND b.col1 IS NULL)
AND

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 14 Oct 2005 21:51:02 +0000 (UTC), Erland Sommarskog wrote:

(snip)
>Keep in mind that you cannot just say:
> SELECT ...
> FROM tbl a
> JOIN audit_tbl b ON a.keycol = b.keycol
> WHERE a.col1 <> b.col1
> AND a.col2 <> b.col2
> ...
>Correct is:
> WHERE (a.col1 <> b.col1 OR
> a.col1 IS NULL AND b.col1 IS NOT NULL OR
> a.col1 IS NOT NULL AND b.col1 IS NULL)
> AND

Hi Erland,

This can be (slightly) simplified to

WHERE (NULLIF (a.col1, b.col1) IS NOT NULL OR
NULLIF (b.col1, a.col1) IS NOT NULL)
AND

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> This can be (slightly) simplified to
> WHERE (NULLIF (a.col1, b.col1) IS NOT NULL OR
> NULLIF (b.col1, a.col1) IS NOT NULL)
> AND

Simplified in the sense "few characters to type, yes". Else I'm not
sure that I find it simpler. Or it is just that it's Friday night after
a hard and long working week...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 14 Oct 2005 22:14:32 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> This can be (slightly) simplified to
>>
>> WHERE (NULLIF (a.col1, b.col1) IS NOT NULL OR
>> NULLIF (b.col1, a.col1) IS NOT NULL)
>> AND
>Simplified in the sense "few characters to type, yes". Else I'm not
>sure that I find it simpler. Or it is just that it's Friday night after
>a hard and long working week...

Hi Erland,

Well, okay. It's an acquired taste, I'll have to grant you that. :-)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||SELECT X.a, X.b, ... X.z
FROM
(SELECT a, .b, ... z
FROM AuditData
UNION ALL
SELECT a, b, ... z
FROM BaseData)
AS X (a, b, ... z)
GROUP BY X.a, X.b, ... X.z
HAVING COUNT(*) < 2;

or if we had SQL-92 set operators, you could write:

SELECT *
FROM (SELECT 'B', * FROM BaseData)
EXCEPT
(SELECT 'A' , * FROM AuditData);

or
SELECT *
FROM (SELECT 'A', * FROM AuditData)
EXCEPT
(SELECT 'B' , * FROM BaseData);|||if the table has a PK, there is no need to do tedios column-by-colmn
comparisons:

create table t1(i int identity, j int)
insert into t1(j) values(1)
insert into t1(j) values(2)
insert into t1(j) values(3)

create table t2(i int identity, j int)
insert into t2(j) values(1)
insert into t2(j) values(2)
insert into t2(j) values(4)

-- rows in t1 that do not have exact match in t2
select * from t1 t
-- there is a row in t2 with the same PK
where exists(select * from t2 where t2.i = t.i)
-- but some other columns are different
and
(select count(*) from
(
select * from t1
union
select * from t2
)t_both where t_both.i = t.i
) = 2

-- rows in t2 that do not have exact match in t1
select * from t2 t
-- there is a row in t1 with the same PK
where exists(select * from t1 where t1.i = t.i)
-- but some other columns are different
and
(select count(*) from
(
select * from t1
union
select * from t2
)t_both where t_both.i = t.i
) = 2

drop table t1
drop table t2|||Thank you all for the updates and I am sorry I did not get back sooner.

I need to do column bu column comparisons as the "report" has to show
which data elements have changed and from what to what ... yuk.

I do not think I can do this efficiently in SQL so I was thinking of
writing a quick little app to loop through column collections of the
recordset ...