Re: COLUMNS_UPDATED() with triggers
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 29 Apr 2008 08:01:14 -0500
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@xxxxxxxxx> wrote in message news:6258c0ca-5d3c-4d65-a041-0978084a3bad@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
.
- References:
- COLUMNS_UPDATED() with triggers
- From: rcamarda
- COLUMNS_UPDATED() with triggers
- Prev by Date: Re: beginners question...
- Next by Date: Re: Select date in british format
- Previous by thread: Re: COLUMNS_UPDATED() with triggers
- Next by thread: beginners question...
- Index(es):
Relevant Pages
|
Loading
