table trigger just hangs

[rcamarda]

I have a UDF that cleans a field of control characters and I use it
like this
select
dbo.udf_CleanAl phaNum(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_De mographics] Script
Date: 04/16/2008 15:32:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFI ER ON
GO


CREATE TRIGGER [dbo].[Clean_Q_Lead_De mographics]
ON [Strayer_Staging].[dbo].[Q_Lead_Demograp hics]
for update, insert
AS
update Strayer_Staging .dbo.Q_Lead_Dem ographics
set address1 = dbo.udf_CleanAl phaNum(inserted .Address1)
from INSERTED ;

when I try to update a record with

update q_lead_demograp hics
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

[rcamarda]

Re: table trigger just hangs

On Apr 16, 3:32 pm, rcamarda <robert.a.cama. ..@gmail.comwro te:

I have a UDF that cleans a field of control characters and I use it
like this
select
  dbo.udf_CleanAl phaNum(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_De mographics]    Script
Date: 04/16/2008 15:32:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFI ER ON
GO
>
CREATE TRIGGER [dbo].[Clean_Q_Lead_De mographics]
   ON  [Strayer_Staging].[dbo].[Q_Lead_Demograp hics]
  for update, insert
AS
  update Strayer_Staging .dbo.Q_Lead_Dem ographics
  set address1 = dbo.udf_CleanAl phaNum(inserted .Address1)
from INSERTED   ;
>
when I try to update a record with
>
update q_lead_demograp hics
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_De mographics]
ON [Strayer_Staging].[dbo].[Q_Lead_Demograp hics]
for update, insert
AS
update Strayer_Staging .dbo.Q_Lead_Dem ographics
set
address1 = dbo.udf_CleanAl phaNum(inserted .Address1),
address2 = dbo.udf_CleanAl phaNum(inserted .Address2),
address3 = dbo.udf_CleanAl phaNum(inserted .Address3),
bad_email = dbo.ValidateEma ilAddress(lower (replace(insert ed.email,'
',''))),
City = dbo.udf_CleanAl phaNum(inserted .City)
from inserted
where q_lead_demograp hics.leadid = inserted.leadid

[rcamarda]

Re: table trigger just hangs

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.

[Ed Murphy]

Re: table trigger just hangs

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?

[rcamarda]

Re: table trigger just hangs

Turns out it doesnt.
I've submitted an enhancement request to Cognos about that aspect of
API loads and triggers.