473,320 Members | 1,976 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 2743
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Michael Spiegel | last post by:
Hi, I have a stored procedure that needs to know the name of the table from which it is called. If you are familiar with a "this" pointer in Java or C++, that's very similar to what I need. I...
36
by: toedipper | last post by:
Hello, I am designing a table of vehicle types, nothing special, just a list of unique vehicle types such as truck, lorry, bike, motor bike, plane, tractor etc etc For the table design I am...
3
by: takilroy | last post by:
Hi, Does anyone know of a simple way to do this? I want to create an insert trigger for a table and if the record already exists based on some criteria, I want to update the table with the...
33
by: coosa | last post by:
I have a table: ---------------------------------------------------- CREATE TABLE CATEGORY ( CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL, CATEGORY_NAME VARCHAR(40) NOT NULL,...
7
by: yoyo | last post by:
Is there anyway to delay a trigger from firing for a few seconds? I have an after insert trigger on table1, which selects information from table2 and table3. Obviously it cannot fire unless the...
6
by: Jchick | last post by:
Im a newbie with a sql table in a MSDE database that contains fields of CustName, BrokerName, Type, Status. I am trying to write a trigger that does this: 1. When a new record in a table is...
7
by: Serge Rielau | last post by:
Hi all, Following Ian's passionate postings on problems with ALTOBJ and the alter table wizard in the control center I'll try to explain how to use ALTOBJ with this thread. I'm not going to get...
2
by: tolcis | last post by:
I have a trigger that should be execute on each row insert and only if appcode = 'I' and datasent = 0. It should execute a DTS package. The DTS package by itself runs about 6 seconds. Trigger...
0
by: Bo Holmberg | last post by:
Hi I am attemtpting to do a couple of inserts into an AS400page using a iSeries ODBC driver (CWBODBC.DLL 10.00.10.00). This works fine except on the last insert which is done on a table which has...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.