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.em ail,' ',''))) = 0
then 1 else 0 end end
from inserted
where Talisma_Lead_Raw_tbl.Student_Insight_ID =
inserted.Student_Insight_ID
END
END
