Update trigger not firing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update trigger not firing

Author

Topic

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2008-08-02 : 15:34:55

I have searched and read many of the similar posts on this topic, but I am still missing the solution. Why does this trigger (enabled) not update the identified columns?

TRIGGER [BuildPnewname]
ON [dbo].[Updates]
FOR UPDATE, INSERT
AS
SET NOCOUNT ON;
update dbo.Updates
set Pnewname = a.D1id+' - '+b.Desc+' <re> '+c.Desc
from dbo.master a
inner join dbo.cust1 b
on a.C1id = b.C1id
inner join dbo.cust2 c
on a.C2id = c.C2id
where a.C2id = pnum

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-02 : 16:02:09

Trace it in profiler to find out why.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-08-02 : 21:49:32

That trigger is going to update the entire table, you know.
You aren't joining to the INSERTED table.

e4 d5 xd5 Nf6

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2008-08-03 : 08:58:37

quote:
Originally posted by blindman

That trigger is going to update the entire table, you know.
You aren't joining to the INSERTED table.

e4 d5 xd5 Nf6

That is ok. These values are dynamic, so updating them again will only refresh any recent changes also.

quote:
Trace it in profiler to find out why.

I didn't realize it was that easy to use Profiler. I will try this. Thanks for the suggestion.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-08-03 : 11:03:30

quote:
Originally posted by youruseridistoxic

quote:
Originally posted by blindman

That trigger is going to update the entire table, you know.
You aren't joining to the INSERTED table.

e4 d5 xd5 Nf6

That is ok. These values are dynamic, so updating them again will only refresh any recent changes also.

Sure. Why bother doing things right?

e4 d5 xd5 Nf6

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-08-03 : 11:40:25

because otherwise there would be no unintended side-effects!

elsasoft.org

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2008-08-04 : 09:04:00

quote:
Originally posted by blindman

quote:
Originally posted by youruseridistoxic

quote:
Originally posted by blindman

That trigger is going to update the entire table, you know.
You aren't joining to the INSERTED table.

e4 d5 xd5 Nf6

That is ok. These values are dynamic, so updating them again will only refresh any recent changes also.
Sure. Why bother doing things right?

e4 d5 xd5 Nf6

Please exalted one, bestow upon me your divine knowledge of how to "correctly" tune my SQL environment.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-08-04 : 10:15:35

Start by removing those lazy single-character table aliases, which serve no purpose beyond code obfuscation.
Then try this, substituting the primary key of your Updates table:


CREATE TRIGGER	[BuildPnewname]
ON [dbo].[Updates]
FOR UPDATE, INSERT
AS
SET NOCOUNT ON;
update dbo.Updates
set	Pnewname = 
	(select	master.D1id+' - '+cust1.Desc+' <re> '+cust2.Desc
	from	dbo.master
		inner join dbo.cust1 on master.C1id = cust1.C1id
		inner join dbo.cust2 on master.C2id = cust2.C2id
	where	master.C2id = pnum)
from	dbo.Updates
where	Updates.[PrimaryKey] = Inserted.[PrimaryKey]

e4 d5 xd5 Nf6

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2008-08-04 : 10:21:57

quote:
Originally posted by blindman

Start by removing those lazy single-character table aliases, which serve no purpose beyond code obfuscation.
Then try this, substituting the primary key of your Updates table:

CREATE TRIGGER [BuildPnewname]
ON [dbo].[Updates]
FOR UPDATE, INSERT
AS
SET NOCOUNT ON;
update dbo.Updates
set Pnewname =
(select master.D1id+' - '+cust1.Desc+' <re> '+cust2.Desc
from dbo.master
inner join dbo.cust1 on master.C1id = cust1.C1id
inner join dbo.cust2 on master.C2id = cust2.C2id
where master.C2id = pnum)
from dbo.Updates
where Updates.[PrimaryKey] = Inserted.[PrimaryKey]

e4 d5 xd5 Nf6

Thanks for the quick reply and constructive input.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-08-04 : 11:01:55

Thanks for acknowledging my Exalted status.

e4 d5 xd5 Nf6

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2008-08-05 : 09:58:48

quote:
Originally posted by blindman

Thanks for acknowledging my Exalted status.

e4 d5 xd5 Nf6

Anytime :)

Running this statement produces the following error;

The multi-part identifier "Inserted.[PrimaryKey]" could not be bound

Do the aliases still apply, outside the select statement?

X002548
Not Just a Number

15586 Posts

Posted - 2008-08-05 : 10:13:33

quote:
Originally posted by blindman

Start by removing those lazy single-character table aliases, which serve no purpose beyond code obfuscation.

Why aren't you using braille then?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-08-05 : 10:14:24

did you miss this part:

>> Then try this, substituting the primary key of your Updates table

elsasoft.org

X002548
Not Just a Number

15586 Posts

Posted - 2008-08-05 : 10:18:36

Seems like the long way around the block to track data changes to me

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources