COLUMNS_UPDATED() with triggers

I wish to make my trigger more efficient in that it only processes
columns that have actually changed.
I am currently processing 9 columns unconditionally.
I found UPDATE() function that returns true if a column has changed,
but then I found COLUMNS_UPDATED() which returns a bit mask of the
columns that changed.
So far, I have the trigger only clean the columns only if ANY of the
fields change, but I want to get a bit smarter and only clean a field
if it changes. I cant think of a way to conditionally use the set nor
use combinations of tests.
Any Ideas?


CREATE TRIGGER [dbo].[Clean_Talisma_Lead_Raw]
ON [dbo].[Talisma_Lead_Raw_tbl]
for insert,update
AS

BEGIN
if update(first) or
update(last) or
update(address2) or
update(address2) or
update(address3) or
update(city) or
update(state) or
update(email) or
update(zip)
BEGIN
update Strayer_Staging.dbo.Talisma_Lead_Raw_tbl
set
first = dbo.udf_CleanAlphaNum(inserted.first),
last = dbo.udf_CleanAlphaNum(inserted.last),
address1 = dbo.udf_CleanAlphaNum(inserted.Address1),
address2 = dbo.udf_CleanAlphaNum(inserted.Address2),
address3 = dbo.udf_CleanAlphaNum(inserted.Address3),
City = dbo.udf_CleanAlphaNum(inserted.City),
state = dbo.udf_CleanAlphaNum(inserted.state),
email = dbo.udf_CleanAlphaNum(inserted.email),
Zip = dbo.udf_CleanAlphaNum(inserted.Zip),
bad_email = case when rtrim(inserted.email) = '' or
inserted.email is null then null else case when
dbo.ValidateEmailAddress(lower(replace(inserted.email,' ',''))) = 0
then 1 else 0 end end
from inserted
where Talisma_Lead_Raw_tbl.Student_Insight_ID =
inserted.Student_Insight_ID
END
END
.

Relevant Pages

  • Re: Highlighter Problem
    ... I think it is probably best to remove Office 2k3 completely and clean up any ... Then do a fresh installation, ... > dozen or so passages ranging from several characters to complete paras and ... > was unable to trigger the same behavior. ...
    (microsoft.public.word.docmanagement)
  • Re: How do I clean out a dualshock 3?
    ... It's not hindering, but ... Is there any way to clean the trigger so it doesn't have this behavior? ...
    (rec.games.video.sony)
  • sp_OACreate stops working and then wakes up again!
    ... stored procedures which dump information to text files. ... fires for each records or my stored procedures doesn't "clean up" their calls ... after the session and doesn't it end when the trigger ends?). ... Remember initially it works, then stops working for an hour or two, then ...
    (microsoft.public.data.oledb)
  • sp_OACreate stops working and then wakes up again!
    ... stored procedures which dump information to text files. ... fires for each records or my stored procedures doesn't "clean up" their calls ... after the session and doesn't it end when the trigger ends?). ... Remember initially it works, then stops working for an hour or two, then ...
    (microsoft.public.win32.programmer.ole)
  • sp_OACreate Scripting.FileSystemObject stops working for a while
    ... stored procedures which dump information to text files. ... fires for each records or my stored procedures doesn't "clean up" their calls ... after the session and doesn't it end when the trigger ends?). ... Remember initially it works, then stops working for an hour or two, then ...
    (microsoft.public.win32.programmer.ole)
Loading