Trigger. Check update on multiple columns

3 Aug 2006 2:24 PM

davethomas92

Hi,

I have a table with 65 columns and a trigger to react one way when
column 3 is updated and react another way when column 3 and any of the
others are updated.
Is there a quick way to check what has been updated. I know I could do
If (Update(Col1) or Update(Col2) etc which seems long winded and I know
about the updated_bitmask (which I think is the solution) but I get
into problems as there are more than 8 columns in the table.

Any help or pointers will be gratefully received.

Thanks

Dave

3 Aug 2006 2:56 PM

Alexander Kuznetsov

Dave,

if

(select count(*) from(
select col1, col2, ... col50 from inserted
union
select col1, col2, ... col50 from deleted
) t) > (select count(*) from inserted)

then something in col1, col2, ... col50 has changed

3 Aug 2006 5:40 PM

Tracy McKibben

Alexander Kuznetsov wrote:

> Dave,
>
> if
>
> (select count(*) from(
> select col1, col2, ... col50 from inserted
> union
> select col1, col2, ... col50 from deleted
> ) t) > (select count(*) from inserted)
>
> then something in col1, col2, ... col50 has changed
>

Brilliant! I love learning new tricks from this group...

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

4 Aug 2006 8:21 AM

davethomas92

Alex,

Thanks for that but, and I apologise for probably being thick, how does
this reduce my code?

Using your example how would I write it generically for the following
examples:

1. col3 change only
2. col3, col21 and col 50 change
3. col 21 and col 50 change.

If example 1 was true then Process A would occur
If example 2 and 3 were true Process B would occur.

All the best

Dave

Alexander Kuznetsov wrote:
Show quote

> Dave,
>
> if
>
> (select count(*) from(
> select col1, col2, ... col50 from inserted
> union
> select col1, col2, ... col50 from deleted
> ) t) > (select count(*) from inserted)
>
> then something in col1, col2, ... col50 has changed

4 Aug 2006 8:35 AM

davethomas92

Alex,

True to form I was being thick!

Tested this and it works.

if (select count(*)
    from(select col3 from inserted
        union
        select col3 from deleted) t) > (select count(*) from inserted)

Thanks again for your help

Dave
davethoma***@hotmail.com wrote:
Show quote

> Alex,
>
> Thanks for that but, and I apologise for probably being thick, how does
> this reduce my code?
>
> Using your example how would I write it generically for the following
> examples:
>
> 1. col3 change only
> 2. col3, col21 and col 50 change
> 3. col 21 and col 50 change.
>
> If example 1 was true then Process A would occur
> If example 2 and 3 were true Process B would occur.
>
> All the best
>
> Dave
>
> Alexander Kuznetsov wrote:
> > Dave,
> >
> > if
> >
> > (select count(*) from(
> > select col1, col2, ... col50 from inserted
> > union
> > select col1, col2, ... col50 from deleted
> > ) t) > (select count(*) from inserted)
> >
> > then something in col1, col2, ... col50 has changed