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.
Author |
Topic |
Ex
Posting Yak Master
166 Posts |
Posted - 2004-12-19 : 22:24:21
|
Hey all,this cursor is a beast and i am sure it can be changed into set basedat the moment it lives in a instead of update trigger and i know it wont work with mutliple updates if anyone can help turn this into set based i would fly to your country and buy you a beer :) PRIMARY KEY ENTITY_ITEM_VIEW PK = (ENTITYITEMID, MAJORBUILDNO, MINORBUILDNO) DECLARE entityCursor CURSOR FAST_FORWARD FOR SELECT EntityItemId FROM dbo.dimension WHERE defineid = (SELECT defineid FROM inserted) AND MajorBuildNo = 0 AND MinorBuildNo = 0 OPEN entitycursor DECLARE @entityID int, @NewEvaluated int,@OldEvaluated int FETCH NEXT FROM entityCursor INTO @entityID WHILE @@fetch_status = 0 BEGIN IF ((SELECT ValueIsInt FROM inserted) = -1) BEGIN SET @OldEvaluated = (SELECT Evaluated FROM deleted) SET @NewEvaluated = (SELECT Evaluated FROM inserted) END ELSE BEGIN SET @OldEvaluated = (SELECT Evaluated FROM deleted) SET @NewEvaluated = 1 END update dbo.ENTITY_ITEM_VIEW set TotalDimElements = TotalDimElements / @OldEvaluated * @NewEvaluated from dbo.ENTITY_ITEM_VIEW e JOIN inserted i on e.ENTITYITEMID = i.ENTITYITEMID AND e.MajorBuildNo = i.MajorBuildNo AND e.MinorBuildNo = i.MinorBuildNo JOIN deleted d on e.ENTITYITEMID = d.ENTITYITEMID AND e.MajorBuildNo = d.MajorBuildNo AND e.MinorBuildNo = d.MinorBuildNo where entityitemID= @entityID AND i.MajorBuildNo = 0 AND i.MinorBuildNo = 0 AND d.evaluated <> i.evaluated AND e.MajorBuildNo = 0 AND e.MinorBuildNo = 0/*last 2 would be redundant?*******************/ FETCH NEXT FROM entityCursor INTO @entityID END CLOSE entityCursor DEALLOCATE entityCursor |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-19 : 23:21:29
|
What's it trying to do?It only deals with single row updates - is that ok.The setting of @OldEvaluated and @NewEvaluated should be outside the cursor as it doesn't depend on the row.I'm also a bit surprised this works aswhere entityitemID= @entityIDdoesn't have a table qualifier forentityitemIDunless it's case sensitive.DECLARE @NewEvaluated int,@OldEvaluated intIF ((SELECT ValueIsInt FROM inserted) = -1)BEGINSET @OldEvaluated = (SELECT Evaluated FROM deleted)SET @NewEvaluated = (SELECT Evaluated FROM inserted)ENDELSEBEGINSET @OldEvaluated = (SELECT Evaluated FROM deleted)SET @NewEvaluated = 1ENDupdate dbo.ENTITY_ITEM_VIEWset TotalDimElements = TotalDimElements / @OldEvaluated * @NewEvaluatedfrom dbo.ENTITY_ITEM_VIEW e JOIN inserted i on e.ENTITYITEMID = i.ENTITYITEMID AND e.MajorBuildNo = i.MajorBuildNo AND e.MinorBuildNo = i.MinorBuildNoJOIN deleted d on e.ENTITYITEMID = d.ENTITYITEMID AND e.MajorBuildNo = d.MajorBuildNo AND e.MinorBuildNo = d.MinorBuildNowhere entityitemID in (SELECT EntityItemId FROM dbo.dimensionWHERE defineid = (SELECT defineid FROM inserted) AND MajorBuildNo = 0 AND MinorBuildNo = 0)AND i.MajorBuildNo = 0 AND i.MinorBuildNo = 0 AND d.evaluated <> i.evaluated AND e.MajorBuildNo = 0 AND e.MinorBuildNo = 0==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Ex
Posting Yak Master
166 Posts |
Posted - 2004-12-19 : 23:35:43
|
hey thank againbut still got a little problem the trigger is in an update trigger as such there could be multiple rowssoIF ((SELECT ValueIsInt FROM inserted) = -1)BEGINSET @OldEvaluated = (SELECT Evaluated FROM deleted)SET @NewEvaluated = (SELECT Evaluated FROM inserted)ENDELSEBEGINSET @OldEvaluated = (SELECT Evaluated FROM deleted)SET @NewEvaluated = 1ENDthat part is not going to work :(so where i am trying to get it so somewhere within the update statment but i cant really see a way to do this unless i use a cursor as it is kind of dependent on the deleted row |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-20 : 07:33:54
|
update dbo.ENTITY_ITEM_VIEWset TotalDimElements = TotalDimElements / d.Evaluated * case when i.ValueIsInt = -1 then i.Evaluated else 1 end==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|