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

Using SQL Server 2005

> 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.

Be aware that these functions do not indicate a column value has actually
changed as a result of an UPDATE statement. These functions simply
indicate that the column was specified in the SET clause.

> 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?

Note that this is an AFTER trigger so the columns will have already been
cleaned by the time the trigger fires. You will need an INSTEAD OF trigger
and check the inserted table columns against the existing table values and
separate UPDATE statements in the trigger in order to skip updates of
specific columns.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"rcamarda" <robert.a.camarda@gmail.com> wrote in message
news:6258c0ca-5d3c-4d65-a041-0978084a3bad@x41g2000hsb.googlegroups.com...
>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