The code posted here is 'example' code, it's not production code. I've done this to make the problem I'm explaining readable / concise.
Using code similar to that below, we're coming across a strange bug. After every INSERT the WHILE loop is stopped.
table containst 100 rows, when the insert is done after 50 rows then the cursor stops, having only touched the first 50 rows. When the insert is done after 55 it stops after 55, and so on.
-- This code is an hypothetical example written to express
-- an problem seen in production
DECLARE @v1 int
DECLARE @v2 int
DECLARE MyCursor CURSOR FAST_FORWARD FOR
SELECT Col1, Col2
FROM table
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @v1, @v2
WHILE(@@FETCH_STATUS=0)
BEGIN
IF(@v1>10)
BEGIN
INSERT INTO table2(col1) VALUES (@v2)
END
FETCH NEXT FROM MyCursor INTO @v1, @v2
END
CLOSE MyCursor
DEALLOCATE MyCursor
There is an AFTER INSERT trigger on table2 which is used to log mutaties on table2 into an third table, aptly named mutations. This contains an cursor which inserts to handle the insert (mutations are logged per-column in an very specific manner, which requires the cursor).
A bit of background: this exists on an set of small support tables. It is an requirement for the project that every change made to the source data is logged, for auditing purposes. The tables with the logging contain things such as bank account numbers, into which vast sums of money will be deposited. There are maximum a few thousand records, and they should only be modified very rarely. The auditing functionality is there to discourage fraud: as we log 'what changed' with 'who did it'.
The obvious, fast and logical way to implement this would be to store the entire row each time an update is made. Then we wouldn't need the cursor, and it would perform an factor better. However the politics of the situation means my hands are tied.
Phew. Now back to the question.
Simplified version of the trigger (real version does an insert per column, and it also inserts the old value):
--This cursor is an hypothetical cursor written to express
--an problem seen in production.
--On UPDATE a new record must be added to table Mutaties for
--every row in every column in the database. This is required
--for auditing purposes.
--An set-based approach which stores the previous state of the row
--is expressly forbidden by the customer
DECLARE @col1 int
DECLARE @col2 int
DECLARE @col1_old int
DECLARE @col2_old int
--Loop through old values next to new values
DECLARE MyTriggerCursor CURSOR FAST_FORWARD FOR
SELECT i.col1, i.col2, d.col1 as col1_old, d.col2 as col2_old
FROM Inserted i
INNER JOIN Deleted d ON i.id=d.id
OPEN MyTriggerCursor
FETCH NEXT FROM MyTriggerCursor INTO @col1, @col2, @col1_old, @col2_old
--Loop through all rows which were updated
WHILE(@@FETCH_STATUS=0)
BEGIN
--In production code a few more details are logged, such as userid, times etc etc
--First column
INSERT Mutaties (tablename, columnname, newvalue, oldvalue)
VALUES ('table2', 'col1', @col1, @col1_old)
--Second column
INSERT Mutaties (tablename, columnname, newvalue, oldvalue)
VALUES ('table2', 'col2', @col2, @col1_old)
FETCH NEXT FROM MyTriggerCursor INTO @col1, @col2, @col1_old, @col2_old
END
CLOSE MyTriggerCursor
DEALLOCATE MyTriggerCursor
Why is the code exiting in the middle of the loop?