When a searched or positioned delete command is issued on the row at the cursor position of a cursor that includes a join, there can be one of two results:
Searched deletions close the cursor implicitly. The next fetch returns error 582:
Cursor ’cursor_name’ was closed implicitly because the current cursor position was deleted due to an update or a delete. The cursor scan position could not be recovered. This happens for cursors which reference more than one table.
Positioned deletions fail, with error 592:
The DELETE WHERE CURRENT OF to the cursor ’cursor_name’ failed because the cursor is on a join.
The cursor remains open, positioned at the same row.
When a searched or positioned update command is issued on the join columns of a cursor:
Searched updates to clustered index keys on allpages-locked tables succeed, but implicitly close the cursor, so the next fetch returns error 582. Searched updates to clustered index keys do not close cursors on data-only-locked tables.
Positioned updates for all locking schemes, and searched updates that do not change a clustered index key on an allpages-locked table succeed and do not close the cursor.
The cursor position depends on the type of table and whether the column has a clustered index. See “Cursor positioning after a delete or update command without joins” for more information.
Join column buffering may affect the result sets returned when join columns are updated. See “Join cursor processing and data modifications” for more information.
Table 19-5 shows how delete and update commands affect join cursors. “Left open” indicates that the cursor is not closed by the update. The cursor is still positioned on the row, so positioned updates can still be made, and the next fetch also succeeds.
Allpages-locked |
Data-only-locked |
||
|---|---|---|---|
delete commands |
|||
Positioned direct delete |
Error 592 |
Error 592 |
|
Searched direct delete |
Error 582 |
Error 582 |
|
Searched deferred delete |
Error 582 |
Error 582 |
|
Searched delete affecting clustered index (deferred) |
Error 582 |
Error 582 |
|
update commands |
|||
Positioned direct update |
Left open |
Left open |
|
Searched direct update |
Left open |
Left open |
|
Searched deferred update |
Left open |
Left open |
|
Searched update affecting clustered index (deferred) |
Aggregate 582 |
Left open |
|
