469,960 Members | 2,660 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,960 developers. It's quick & easy.

table trigger just hangs

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
Jun 27 '08 #1
4 2578
On Apr 16, 3:32*pm, rcamarda <robert.a.cama...@gmail.comwrote:
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
Jun 27 '08 #2
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.
Jun 27 '08 #3
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?
Jun 27 '08 #4
Turns out it doesnt.
I've submitted an enhancement request to Cognos about that aspect of
API loads and triggers.

Jun 27 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Michael Spiegel | last post: by
36 posts views Thread by toedipper | last post: by
3 posts views Thread by takilroy | last post: by
33 posts views Thread by coosa | last post: by
7 posts views Thread by yoyo | last post: by
7 posts views Thread by Serge Rielau | last post: by
reply views Thread by rainxy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.