Post reply

911 - Cursor killing my trigger

  • June 26, 2009 at 8:49 am

    #73928

    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

    #1016089

    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

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

  • June 26, 2009 at 9:15 am

    #1016108

    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

    #1016115

    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

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

  • June 26, 2009 at 9:49 am

    #1016140

    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

    #1016179

    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

    ----Set @index = @index+1

    ----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

    #1016189

    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

    #1016219

    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

    #1016221

    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

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

  • June 26, 2009 at 1:05 pm

    #1016250

    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

    #1016299

    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 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

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 29, 2009 at 5:46 am

    #1016741

    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

    #1016779

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 29, 2009 at 6:37 am

    #1016782

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 29, 2009 at 11:33 am

    #1017015

    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

    ---- Set @index = @index+1

    ---- 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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy