Update Trigger

Question

Update Trigger

Greg-488665

Newbie

Points: 2
More actions
June 27, 2008 at 3:12 pm

#75251

I have a field called ProjectPriority and when the number gets updated I want to incurment the numbers above the new number by one. So if 1 got changed to a 5 then the old 5 should be 6 and 6 should be 7 and so on. below is the trigger but when trying to execute it I get an error telling me "deleted.projectpriority" could not be found. Any help would be great.
ALTER TRIGGER [dbo].[u_priority]
ON [dbo].[tblProject]
FOR INSERT, UPDATE
AS
IF UPDATE(ProjectPriority)
update tblproject set ProjectPriority = projectpriority + 1
where projectpriority > deleted.projectpriority

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Greg (6/27/2008)
below is the trigger but when trying to execute it I get an error telling me "deleted.projectpriority" could not be found. Any help would be great.
ALTER TRIGGER [dbo].[u_priority]
ON [dbo].[tblProject]
FOR INSERT, UPDATE
AS
IF UPDATE(ProjectPriority)
update tblproject set ProjectPriority = projectpriority + 1
where projectpriority > deleted.projectpriority

"deleted" is a pseudotable and you have to treat it like a table. I this case that means that you have to join with it in order to use it:

ALTER TRIGGER [dbo].[u_priority]

ON [dbo].[tblProject]

FOR INSERT, UPDATE

AS

IF UPDATE(ProjectPriority)

Update tblproject

Set ProjectPriority = projectpriority + 1

From tblproject

Join deleted ON tblproject.PK = deleted.PK

Where projectpriority > deleted.projectpriority

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 2

There also won't be a deleted table on an insert. Be sure that's the behavior you are looking for.

goodguy SSCertifiable Points: 5456 More actions · Answer 3

Also, an update may include the target column without changing its original value, if the actual UPDATE statement contains all column names. Then you will end up incrementing it needlessly. So it is better to check if the new value has changed before incrementing it.

Just a side question: why are you incrementing it?

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Steve Jones - Editor (6/27/2008)
There also won't be a deleted table on an insert. Be sure that's the behavior you are looking for.

Heh, I missed that, Steve. Though technically it does still exist, it will just have 0 rows.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Greg-488665 Newbie Points: 2 More actions · Answer 5

thanks for the response, not sure exactly how its going to work yet but I ran across this error on my way to figuring it out.

basically we have a DB with our projects listed in it and the projects get assigned a priority, the priority can get changed at any time and No project can have the some priority. So rather then having to change each project priority when it changes I though I could change just the one project priority and then the rest of the project priorities could be changed programmatically. Hope this makes since.

Greg