Modify trigger

Question

Modify trigger

4R4

Right there with Babe

Points: 726
More actions
June 9, 2011 at 7:02 am

#241836

Hi
I have added the following code on the modify trigger:
USE [Testdb]
GO
/****** Object: Trigger [dbo].[ModyfiedRecord] Script Date: 06/09/2011 14:04:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[ModyfiedRecord]
ON [dbo].[Testtable$Afsl_ Vejehoved]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE renovest.dbo.[Testtable$Afsl_ Vejehoved]
set
[Tilhørsforhold] = (select [Tilhørsforhold] from inserted ),
[Formål] = (select [Formål] from inserted ),
[Formål beskrivelse] = (select [Formål beskrivelse] from inserted ),
[Deklarationsnr_] = (select [Deklarationsnr_] from inserted ),
…
…
[BilledeID] = (select [BilledeID] from inserted )
where [Vejenummer] = (select[Vejenummer] from inserted )
END;
I would like to use the new MERGE function, but the customer runs 2005. The issue is that there are 200 fields I need to update and it simply takes too long time.
Any clue how I can speed up the update.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

toddasd SSCertifiable Points: 6298 More actions · Answer 1

It's taking a long time because you are running 200 subselects (which it looks like this trigger will break if more than one row is inserted.) Change the statement to the UPDATE...FROM syntax.

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

drew.allen SSC Guru Points: 76985 More actions · Answer 2

There are far deeper problems with the trigger than the multiple subselects.

If you are only inserting one record, this trigger does ABSOLUTELY NOTHING. If you update column A in Row 1 to X, the trigger reads that updated value, X, and reupdates column A in Row 1 to X. (Assuming that the field in the where clause contains a unique value.)

If you are inserting multiple records, it's even worse. You're essentially updating each of the rows with a random value from the inserted records.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Lowell SSC Guru Points: 323518 More actions · Answer 3

if this is updating a table in a different database, it might be OK...

i see it is updating renovest.dbo.[Testtable$Afsl_ Vejehoved] ...what database is the table with the trigger in? drew's observation might be spot on.

I would fix the trigger so that the body was this instead:(the UPDATE FROM syntax, as suggested.

UPDATE myTarget

set myTarget.[Tilhørsforhold] = INSERTED.[Tilhørsforhold] ,

myTarget.[Formål] = INSERTED.[Formål] ,

myTarget.[Formål beskrivelse] = INSERTED.[Formål beskrivelse] ,

myTarget.[Deklarationsnr_] = INSERTED.[Deklarationsnr_] ,

myTarget.[BilledeID] = INSERTED.[BilledeID]

FROM renovest.dbo.[Testtable$Afsl_ Vejehoved] myTarget

INNER JOIN INSERTED

ON myTarget.[Vejenummer] = INSERTED.[Vejenummer]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

toddasd SSCertifiable Points: 6298 More actions · Answer 4

Oh wow, I didn't notice the same table is being updated in the trigger. I assumed he was updating a second table because the first option doesn't make sense! :w00t:

4R4, what are you trying to accomplish?

EDIT: nevermind, Lowell got it sussed out.

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

4R4 Right there with Babe Points: 726 More actions · Answer 5

He he, it could look as it's the same due to my mistake. The company name was in the name and I did a search and replace.

It's an equal table on another database.

Thanks for your hawk eye:-)

4R4 Right there with Babe Points: 726 More actions · Answer 6

Lowell (6/9/2011)
if this is updating a table in a different database, it might be OK...
i see it is updating renovest.dbo.[Testtable$Afsl_ Vejehoved] ...what database is the table with the trigger in? drew's observation might be spot on.
I would fix the trigger so that the body was this instead:(the UPDATE FROM syntax, as suggested.
UPDATE myTarget
set myTarget.[Tilhørsforhold] = INSERTED.[Tilhørsforhold] ,
myTarget.[Formål] = INSERTED.[Formål] ,
myTarget.[Formål beskrivelse] = INSERTED.[Formål beskrivelse] ,
myTarget.[Deklarationsnr_] = INSERTED.[Deklarationsnr_] ,
myTarget.[BilledeID] = INSERTED.[BilledeID]
FROM renovest.dbo.[Testtable$Afsl_ Vejehoved] myTarget
INNER JOIN INSERTED
ON myTarget.[Vejenummer] = INSERTED.[Vejenummer]

Thanks a lot, that did the trick 🙂