Tony Rogerson's ramblings on SQL Server

Say you have a table and that table has a secondary history table so you can do windowing, for instance the settings on a specifc entity like the date when we started retaining fees on a debtor account say.

Ordinarily most people would resort to a large series of OR's to check the inserted and deleted tables to see what has changed.

Instead, you can use GROUP BY, DISTINCT and HashBytes to work out what rows (what key) has changed and actually requires sorting (updating the end date on the base table inserting a new row with the new settings).

The trigger below only shows the SELECT statement that gives you the complete logic I'm talking about, of course - this is test only - you should no longer be putting SELECT's in triggers but it highlights the behaviour...

create table some_base_table (

    batch_id        int not null primary key clustered,

    batch_name      varchar(250) not null,

    some_parm       int null,

    some_parm2      int null

)

insert some_base_table ( batch_id, batch_name, some_parm, some_parm2 )

    values( 1, 'hello fred', 1, 2 )

insert some_base_table ( batch_id, batch_name, some_parm, some_parm2 )

    values( 2, 'hello fred', 1, 2 )

insert some_base_table ( batch_id, batch_name, some_parm, some_parm2 )

    values( 3, 'hello fred', 1, 2 )

insert some_base_table ( batch_id, batch_name, some_parm, some_parm2 )

    values( 4, 'hello fred', 1, 2 )

insert some_base_table ( batch_id, batch_name, some_parm, some_parm2 )

    values( 5, 'hello fred', 1, 2 )

insert some_base_table ( batch_id, batch_name, some_parm, some_parm2 )

    values( 6, 'hello fred', 1, 2 )

create trigger trg_some_base_table_check_for_changes on some_base_table for update

as

begin

    select batch_id

    from (

        select distinct batch_id, hash_combined = hashbytes( 'sha1', combined )

        from (  select batch_id,

                       combined =(  select batch_id, batch_name, some_parm, some_parm2

                                    from deleted c       --  need old values

                                    where c.batch_id = d.batch_id

                                    for xml path( '' ) )

                from deleted d

                union all

                select batch_id,

                       combined =(  select batch_id, batch_name, some_parm, some_parm2

                                    from some_base_table c       --  need current values (could use inserted here)

                                    where c.batch_id = d.batch_id

                                    for xml path( '' ) )

                from deleted d

            ) as r

        ) as c

    group by batch_id

    having count(*) > 1

end

go

--  test

update some_base_table

    set batch_name = lower( batch_name )

where batch_id = 2

Update 20091022:

HashBytes only takes 8000 bytes so if you have a long row with lots of column you break this and get a binary truncation error; so - instead of using HashBytes use CHECKSUM like discussed earlier in the comments, CHECKSUM works across the varchar(max) the for xml gives you. The important thing here is the use of FOR XML to get around the incompatible data type problem.