> 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@[EMAIL PROTECTED]
> wrote in message
news:6258c0ca-5d3c-4d65-a041-0978084a3bad@[EMAIL PROTECTED]
>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
|
