On Apr 16, 3:32*pm, rcamarda <robert.a.cama...@gmail.com> wrote:
> I have a UDF that cleans a field of control characters and I use it
> like this
> select
> * dbo.udf_CleanAlphaNum(Address1) as Address1
> from *Leads
>
> It works great. I use it to clean several fields from a vendors SQL
> server. The downside is I have to first load the data into my database
> so I can use my function to clean the data THEN proceed to load it
> into the destination table. I thought I could create a trigger on the
> final table that calls this function via a trigger.
>
> This is my test CREATE TRIGGER
>
> USE [Strayer_Staging]
> GO
> /****** Object: *Trigger [dbo].[Clean_Q_Lead_Demographics] * *Script
> Date: 04/16/2008 15:32:15 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> CREATE TRIGGER [dbo].[Clean_Q_Lead_Demographics]
> * *ON *[Strayer_Staging].[dbo].[Q_Lead_Demographics]
> * for update, insert
> AS
> * update Strayer_Staging.dbo.Q_Lead_Demographics
> * set address1 = dbo.udf_CleanAlphaNum(inserted.Address1)
> from INSERTED * ;
>
> when I try to update a record with
>
> update q_lead_demographics
> set address1 = '2 chestnut street'
> where leadid = 1075789
>
> it looks like it updates all records becuase it take 4 minutes and I
> get this message:
> (1055538 row(s) affected)
>
> (1 row(s) affected)
>
> it works, but on all rows, not just the updated row.
>
> Is there a @@ variable that is the primary key so I should use *a
> WHERE, or am going about this all wrong?
> TIA

This seems to work better the result shows I updated 2 records. Is it
becuase an update is really a delete and an update?

ALTER TRIGGER [dbo].[Clean_Q_Lead_Demographics]
ON [Strayer_Staging].[dbo].[Q_Lead_Demographics]
for update, insert
AS
update Strayer_Staging.dbo.Q_Lead_Demographics
set
address1 = dbo.udf_CleanAlphaNum(inserted.Address1),
address2 = dbo.udf_CleanAlphaNum(inserted.Address2),
address3 = dbo.udf_CleanAlphaNum(inserted.Address3),
bad_email = dbo.ValidateEmailAddress(lower(replace(inserted.em ail,'
',''))),
City = dbo.udf_CleanAlphaNum(inserted.City)
from inserted
where q_lead_demographics.leadid = inserted.leadid