Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

dbf2mysql parameter, WWWXXXAPC, wwwxxxAPC, How to unsubscrube from dategen spam, docmd.close 2585, WWWXXXDOCO, nu vot, dhcpd lease file "binding state", WWWXXXDOCO, how to setup procmail to process html2text

Links

XODOX
Impressum

#1: table trigger just hangs

Posted on 2008-04-16 21:32:25 by rcamarda

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

Report this message

#2: Re: table trigger just hangs

Posted on 2008-04-16 22:55:19 by rcamarda

On Apr 16, 3:32=A0pm, 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
> =A0 dbo.udf_CleanAlphaNum(Address1) as Address1
> from =A0Leads
>
> 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: =A0Trigger [dbo].[Clean_Q_Lead_Demographics] =A0 =A0Script=

> Date: 04/16/2008 15:32:15 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> CREATE TRIGGER [dbo].[Clean_Q_Lead_Demographics]
> =A0 =A0ON =A0[Strayer_Staging].[dbo].[Q_Lead_Demographics]
> =A0 for update, insert
> AS
> =A0 update Strayer_Staging.dbo.Q_Lead_Demographics
> =A0 set address1 =3D dbo.udf_CleanAlphaNum(inserted.Address1)
> from INSERTED =A0 ;
>
> when I try to update a record with
>
> update q_lead_demographics
> set address1 =3D '2 chestnut street'
> where leadid =3D 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 =A0a
> 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 =3D dbo.udf_CleanAlphaNum(inserted.Address1),
address2 =3D dbo.udf_CleanAlphaNum(inserted.Address2),
address3 =3D dbo.udf_CleanAlphaNum(inserted.Address3),
bad_email =3D dbo.ValidateEmailAddress(lower(replace(inserted.email,'
',''))),
City =3D dbo.udf_CleanAlphaNum(inserted.City)
from inserted
where q_lead_demographics.leadid =3D inserted.leadid

Report this message

#3: Re: table trigger just hangs

Posted on 2008-04-17 05:48:36 by rcamarda

I got the trigger to work, but what added to my confusion was the bulk
load.
I was using the API method of bulk loading as provided in Cognos' Data
Manager ETL tool. I discovered that the trigger would work when I used
a normal relational delivery, but not the API bulk load.

Report this message

#4: Re: table trigger just hangs

Posted on 2008-04-17 07:23:25 by Ed Murphy

rcamarda wrote:

> I got the trigger to work, but what added to my confusion was the bulk
> load.
> I was using the API method of bulk loading as provided in Cognos' Data
> Manager ETL tool. I discovered that the trigger would work when I used
> a normal relational delivery, but not the API bulk load.

http://msdn2.microsoft.com/en-us/library/ms171769.aspx indicates that
bulk loads ignore triggers unless called with FireTriggers = TRUE. Does
Data Manager have an option to activate that flag?

Report this message