June 26, 2009 at 8:49 am
I have created a trigger below. The cursor is cuasing the update not to work at all. When I comment the cursor out the update works but the trigger clearly will not do what it is supposed to do. I tried using both index comparison with a fetch next and a fetch absolute. Either way the update dies.
Any help would be greatly appreciated.
Ken
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER TRIGGER [NoteTypeChange_u_trig]
ON [dbo].[CustomerFieldTrackExt]
for UPDATE
AS
If Update (Custom_1) OR Update (Custom_2)
BEGIN
Declare
@ProjectID int,
@IncidentID int,
@newnotetypeID int,
@newnotetype varchar(20),
@noteID int,
@index int
Select @ProjectID = (Select ProjectID from Inserted)
Select @IncidentID = (Select BugID from Inserted)
Select @newnotetypeID = (Select CustomerFieldTrackEXT.Custom_1 from CustomerFieldTrackEXT
where ProjectID = @ProjectID and BugID = @IncidentID)
Select @newnotetype = (Select CustomerFieldTrackEXT.Custom_2 from CustomerFieldTrackEXT
where ProjectID = @ProjectID and BugID = @IncidentID)
--DECLARE noteID_curs CUSROR FOR
--Select NoteID from BugNotes where BugID=@IncidentID and ProjectID=@ProjectID
--Open noteID_curs
--Fetch Absolute @newnotetypeID from noteID_curs Into @noteID
--Fetch ABSOLUTE 1 from noteID_curs Into @noteID
--FETCH NEXT FROM noteID_curs
--Into @noteID
--While @index <= @newnotetypeID
--Begin
--Set @index = @index+1
--End
--Close noteID_curs
--Deallocate noteID_curs
If @newnotetype = 'Visible to Licensee'
Begin
--SET NOCOUNT ON;
Update BugNotes
set NoteTypeID=2
where BugID=@IncidentID and ProjectID=@ProjectID and NoteID=@noteID
End
If @newnotetype = 'Invisible to Licensee'
Begin
--SET NOCOUNT ON;
Update BugNotes
set NoteTypeID=1
where BugID=@IncidentID and ProjectID=@ProjectID and NoteID=@noteID
End
END
June 26, 2009 at 9:01 am
Couple problems straight off.
That trigger will only work right if there's one row updated. If there's more than one row updated, only one of them is going to be 'processed'
You're never giving @noteID a value, hence it's null. That means that the updates are looking for rows where @noteID = null, which is never true.
The cursor looks completely unnecessary. I can't be sure without seeing data and getting an explaination, but I suspect the entire things can be rewritten fairly easily just like this
ALTER TRIGGER [NoteTypeChange_u_trig]
ON [dbo].[CustomerFieldTrackExt]
for UPDATE
AS
If Update (Custom_1) OR Update (Custom_2)
Update BugNotes
set NoteTypeID =
CASE inserted.Custom_2
WHEN 'Visible to Licensee' THEN 2
WHEN 'Invisible to Licensee' THEN 1
END
FROM BugNotes inner join inserted on BugNotes.BugID= inserted.BugNotes and BugNotes.ProjectID=inserted.ProjectID
Give it a try, as I can't test it I can't promise it'll work or do what you want.
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
June 26, 2009 at 9:15 am
Thanks Gail, but I must get a recordset and choose the the correct record to get the NoteID.
-I have a list of notes on and incident.
-Someone selects a note based on the index of the notes on that incident.
-In the DB however the notes have unique NoteIDs regardless of incidnet it is associated with.
-I need to build a recordset of all notes for a specific incidnet
-select the correct note for the incidnet (the index # 2 from the gui would relate to the second note in the recordset)
Then I need to find what the unique note id is, so that I can change the note type.
I hope that helps.
Ken
June 26, 2009 at 9:22 am
rkwitcher (6/26/2009)
Thanks Gail, but I must get a recordset and choose the the correct record to get the NoteID.-I have a list of notes on and incident.
-Someone selects a note based on the index of the notes on that incident.
-In the DB however the notes have unique NoteIDs regardless of incidnet it is associated with.
-I need to build a recordset of all notes for a specific incidnet
-select the correct note for the incidnet (the index # 2 from the gui would relate to the second note in the recordset)
Then I need to find what the unique note id is, so that I can change the note type.
So Custom_1 has the 'index' of the note? According to what other column (id note number 2 as ordered by what?)
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
June 26, 2009 at 9:49 am
You don't say what NoteID is the one you want to update. This query assumes it's the largest one, if you have other criteria maybe you can figure out what the correct subquery should be.
UPDATE b
SET NoteTypeID =
CASE c.Custom_2
WHEN 'Visible to Licensee' THEN 2
WHEN 'Invisible to Licensee' THEN 1
ELSE NoteTypeID
END
FROM INSERTED i
INNER JOIN CustomerFieldTrackEXT c ON c.ProjectID = i.ProjectID AND c.BugID = i.BugID
INNER JOIN (
SELECT ProjectID, BugID, MAX(NoteID) AS NoteID
FROM BugNotes
) n ON n.ProjectID = i.ProjectID AND n.BugID = i.BugID
INNER JOIN BugNotes b ON b.ProjectID = i.ProjectID AND b.BugID = i.BugID AND b.NoteID = n.NoteID
P.S. The phrase "I must have a recordset" is something you should leave out of future posts. And no reply should start "Thanks Gail, but..." 🙂
June 26, 2009 at 10:27 am
I realize that the entire cursor was commented out on my code so it was confusing. I have posted the code below with better comments to explain what I am doing.
Create TRIGGER [NoteTypeChange_u_trig]
ON [dbo].[CustomerFieldTrackExt]
for UPDATE
AS
If Update (Custom_1) OR Update (Custom_2)
BEGIN
Declare
@ProjectID int,
@IncidentID int,
@newnotetypeID int,
@newnotetype varchar(20),
@noteID int,
@index int
Set @index = 1
DECLARE noteID_curs CURSOR FOR
Select NoteID from BugNotes where BugID=@IncidentID and ProjectID=@ProjectID
----Get unique keys from inserted row
Select @ProjectID = (Select ProjectID from Inserted)
Select @IncidentID = (Select BugID from Inserted)
----Get index value and new notetype desired from gui input.
----User will see table of notes and id's (which is
----just an index of the notes for that particular incident)
----They will enter that id number in a field recorded to CustomerFieldTrackEXT.Custom_1
----and the will select the new desired type from a dropdown which will write to
----ustomerFieldTrackEXT.Custom_2. We will then pull thoes values in here:
Select @newnotetypeID = (Select CustomerFieldTrackEXT.Custom_1 from CustomerFieldTrackEXT
where ProjectID = @ProjectID and BugID = @IncidentID)
Select @newnotetype = (Select CustomerFieldTrackEXT.Custom_2 from CustomerFieldTrackEXT
where ProjectID = @ProjectID and BugID = @IncidentID)
----We will open a cursor to get a list of all NoteID's from all then notes for that
----particular incidnet. The NoteID field is unique for all notes.
----We will then Fetch the NoteID from the Cursor by choosing the row in the cursor based
----on the index value in CustomerFieldTrackEXT.Custom_1 and insert the value into @noteID
Open noteID_curs
Fetch Absolute @newnotetypeID from noteID_curs Into @noteID
----Alternatively we could use somehting like this
----FETCH NEXT FROM noteID_curs
----Into @noteID
----While @index <= @newnotetypeID
----Begin
----End
Close noteID_curs
Deallocate noteID_curs
----Now we can go to the Bugnotes Table and change the NoteTypeID field to the
----new desired note type based on the value in CustomerFieldTrackEXT.Custom_2
If @newnotetype = 'Visible to Licensee'
Begin
Update BugNotes
set NoteTypeID=2
where BugID=@IncidentID and ProjectID=@ProjectID and NoteID=@noteID
End
If @newnotetype = 'Invisible to Licensee'
Begin
Update BugNotes
set NoteTypeID=1
where BugID=@IncidentID and ProjectID=@ProjectID and NoteID=@noteID
End
END
June 26, 2009 at 10:55 am
So you're saying you have to count all the notes for an incident, and the sequence number of the one you're looking for is in the Custom_1 field? Sounds like a job for ROW_NUMBER()!
UPDATE b
SET NoteTypeID =
CASE c.Custom_2
WHEN 'Visible to Licensee' THEN 2
WHEN 'Invisible to Licensee' THEN 1
ELSE NoteTypeID
END
FROM (
SELECT i.ProjectID, i.BugID, b1.NoteID
ROW_NUMBER() OVER (PARTITION BY i.ProjectID, i.BugID ORDER BY b1.NoteID) AS NoteCounter
FROM INSERTED i
INNER JOIN BugNote b1 ON b1.ProjectID = i.ProjectID AND b1.BugID = i.BugID
) n
INNER JOIN CustomerFieldTrackEXT c ON c.ProjectID = n.ProjectID AND c.BugID = n.BugID AND n.NoteCounter = c.Custom_1
INNER JOIN BugNote b ON b.ProjectID = n.ProjectID AND b.BugID = n.BugID AND b.NoteID = n.NoteID
You still haven't specified the order you use while counting notes, here I assumed it was by NoteID.
June 26, 2009 at 11:55 am
That code looks great, but still getting
Msg 102, Level 15, State 1, Procedure NoteTypeChange_u_trig, Line 27
Incorrect syntax near '('.
Line is the blank line b/w the '(' and the 'Select'.
Any ideas?
June 26, 2009 at 12:00 pm
Scott Coleman (6/26/2009)
So you're saying you have to count all the notes for an incident, and the sequence number of the one you're looking for is in the Custom_1 field? Sounds like a job for ROW_NUMBER()!
Except that RowNumber is a SQL 2005 feature and this is the SQL 2000 forum. Identity and a temp table should work, painfully, but it should work
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
June 26, 2009 at 1:05 pm
Sorry, I got here through "Active Threads" and didn't notice that it was 2000.
You can use a self-join solution, although depending on your rowcounts and indexes this may be a performance issue. Note that it's still counting by NoteID, because the desired ordering still hasn't been specified if it is something different.
UPDATE b
SET NoteTypeID =
CASE c.Custom_2
WHEN 'Visible to Licensee' THEN 2
WHEN 'Invisible to Licensee' THEN 1
ELSE NoteTypeID
END
FROM (
SELECT i.ProjectID, i.BugID, b1.NoteID,
(SELECT COUNT(*) + 1 FROM BugNote WHERE ProjectID = i.ProjectID AND BugID = i.BugID AND NoteID < b1.NoteID) AS NoteCounter
FROM INSERTED i
INNER JOIN BugNote b1 ON b1.ProjectID = i.ProjectID AND b1.BugID = i.BugID
) n
INNER JOIN CustomerFieldTrackEXT c ON c.ProjectID = n.ProjectID AND c.BugID = n.BugID AND n.NoteCounter = c.Custom_1
INNER JOIN BugNote b ON b.ProjectID = n.ProjectID AND b.BugID = n.BugID AND b.NoteID = n.NoteID
June 26, 2009 at 3:29 pm
Scott Coleman (6/26/2009)
Sorry, I got here through "Active Threads" and didn't notice that it was 2000.You can use a self-join solution, although depending on your rowcounts and indexes this may be a performance issue. Note that it's still counting by NoteID, because the desired ordering still hasn't been specified if it is something different.
UPDATE bSET NoteTypeID =
CASE c.Custom_2
WHEN 'Visible to Licensee' THEN 2
WHEN 'Invisible to Licensee' THEN 1
ELSE NoteTypeID
END
FROM (
SELECT i.ProjectID, i.BugID, b1.NoteID,
(SELECT COUNT(*) + 1 FROM BugNote WHERE ProjectID = i.ProjectID AND BugID = i.BugID AND NoteID < b1.NoteID) AS NoteCounter
FROM INSERTED i
INNER JOIN BugNote b1 ON b1.ProjectID = i.ProjectID AND b1.BugID = i.BugID
) n
INNER JOIN CustomerFieldTrackEXT c ON c.ProjectID = n.ProjectID AND c.BugID = n.BugID AND n.NoteCounter = c.Custom_1
INNER JOIN BugNote b ON b.ProjectID = n.ProjectID AND b.BugID = n.BugID AND b.NoteID = n.NoteID
There's a wee bit of performance problem even in that fine bit of code. It has a triangular join in an aggregate correlated subquery.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2009 at 5:46 am
It is a triangular join, but without knowing more about the data I'm just guessing that there are a relatively small number of notes for each bug. Also, the original trigger was written to only work with one row at a time, so it's not a triangular join against the entire table but only against the notes for one bug. Assuming the query can use an index on ProjectID and BugID it shouldn't be too bad.
There are a number of assumptions there, but I think its an improvement over using a cursor.
June 29, 2009 at 6:33 am
Scott Coleman (6/29/2009)
It is a triangular join, but without knowing more about the data I'm just guessing that there are a relatively small number of notes for each bug. Also, the original trigger was written to only work with one row at a time, so it's not a triangular join against the entire table but only against the notes for one bug. Assuming the query can use an index on ProjectID and BugID it shouldn't be too bad.There are a number of assumptions there, but I think its an improvement over using a cursor.
I'd never assume such a thing as there being a relatively small number of notes for each bug but even if there are, performance will suffer relatively a lot because of the Triangular join in conjunction with the aggregate. Triangular joins used in conjunction with any form of aggregation usually aren't an improvement over using a well written cursor except for the size of code. Except for when using an UPDATE Pseudo-Cursor (ie. Quirky Update), the duration for "running aggregations" increases at a logarithmic rate as rows increase for both triangular joins and running aggregations in cursors.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2009 at 6:37 am
As a side bar, I'm still waiting for the OP to post code where the things he wants to use aren't actually commented out so I can be sure of what needs to be done. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2009 at 11:33 am
I actually reposted the code w/o the important stuff commented out on the first page of this thread, but here it is again, below.
Also, there will indeed be a small number of notes per incident. Usually less than 5 but occasionaly as many as 15-20.
And it is actually SQL2005. I did not notice I was in the wrong place either.
If a moderator can move the thread that would be great.
Create TRIGGER [NoteTypeChange_u_trig]
ON [dbo].[CustomerFieldTrackExt]
for UPDATE
AS
If Update (Custom_1) OR Update (Custom_2)
BEGIN
Declare
@ProjectID int,
@IncidentID int,
@newnotetypeID int,
@newnotetype varchar(20),
@noteID int,
@index int
Set @index = 1
DECLARE noteID_curs CURSOR FOR
Select NoteID from BugNotes where BugID=@IncidentID and ProjectID=@ProjectID
----Get unique keys from inserted row
Select @ProjectID = (Select ProjectID from Inserted)
Select @IncidentID = (Select BugID from Inserted)
----Get index value and new notetype desired from gui input.
----User will see table of notes and id's (which is
----just an index of the notes for that particular incident)
----They will enter that id number in a field recorded to CustomerFieldTrackEXT.Custom_1
----and the will select the new desired type from a dropdown which will write to
----ustomerFieldTrackEXT.Custom_2. We will then pull thoes values in here:
Select @newnotetypeID = (Select CustomerFieldTrackEXT.Custom_1 from CustomerFieldTrackEXT
where ProjectID = @ProjectID and BugID = @IncidentID)
Select @newnotetype = (Select CustomerFieldTrackEXT.Custom_2 from CustomerFieldTrackEXT
where ProjectID = @ProjectID and BugID = @IncidentID)
----We will open a cursor to get a list of all NoteID's from all then notes for that
----particular incidnet. The NoteID field is unique for all notes.
----We will then Fetch the NoteID from the Cursor by choosing the row in the cursor based
----on the index value in CustomerFieldTrackEXT.Custom_1 and insert the value into @noteID
Open noteID_curs
Fetch Absolute @newnotetypeID from noteID_curs Into @noteID
----Alternatively we could use somehting like this
----FETCH NEXT FROM noteID_curs
---- Into @noteID
---- While @index <= @newnotetypeID
---- Begin
---- End
Close noteID_curs
Deallocate noteID_curs
----Now we can go to the Bugnotes Table and change the NoteTypeID field to the
----new desired note type based on the value in CustomerFieldTrackEXT.Custom_2
If @newnotetype = 'Visible to Licensee'
Begin
Update BugNotes
set NoteTypeID=2
where BugID=@IncidentID and ProjectID=@ProjectID and NoteID=@noteID
End
If @newnotetype = 'Invisible to Licensee'
Begin
Update BugNotes
set NoteTypeID=1
where BugID=@IncidentID and ProjectID=@ProjectID and NoteID=@noteID
End
END
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply