SQL trigger

Question

SQL trigger

hb21l6

SSCommitted

Points: 1647
More actions
January 9, 2009 at 8:21 am

#75394

Hi all,
I've got this trigger that i've somehow thumbled together.
I wanted to update a field called "Lastupdated" with the current date which it does do, but it's updating every record in the table. i'm not sure how to tell it to only update the particular row and not all of them
help!!
Thanks Dave
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER trig_updateQ1ans
ON tbl_Question1Answers
FOR UPDATE
AS
IF NOT UPDATE(questionAns)
BEGIN
RETURN
END
UPDATE tbl_Question1Answers set lastupdated = getdate()
GO

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The inserted table will contain all the rows that were updated. There may be more than one, SQL doesn't have row triggers.

So, what you need is something like this

UPDATE tbl_Question1Answers ...

WHERE <Primary key column> IN (SELECT <Primary key column> FROM inserted)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ryan C. Price Ten Centuries Points: 1184 More actions · Answer 2

Or something like:

UPDATE tbl_Question1Answers FROM

INSERTED JOIN tbl_Question1Answers ON INSERTED.<PrimaryKey> = tbl_Question1Answers.<PrimaryKey>

SET lastupdated = getdate()

For your scenario, I'm not aware of any significant practical difference between this and Gail's suggestion, I just like the join method because it allows you to do more than just a simple update of the base table (I have scenarios where there are multiple tables involved in a multi-step trigger, ending with some 'extra' stuff being done to the base table, other than just what's in the original insert).

/Ryan

EDIT: My original post lost some parts of the code (didn't like the angle brackets....)

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 3

I prefer the join conditions.

update x

set date = getdate()

from inserted i

where i.PK = x.PK

Gail, not sure if this is a language thing, seeing brackets in your post between the various items.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Steve Jones - Editor (1/11/2009)
Gail, not sure if this is a language thing, seeing brackets in your post between the various items.

Whoops. That's not right.

I usually use < > to denote a placeholder, much as BoL does. Just put them the wrong way around.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hb21l6 SSCommitted Points: 1647 More actions · Answer 5

Many thanks for the replies,

I'll implement these tomorrow

😀

Dave