rows deletion affected by cursor

Hello,

I am using a cursor to navigate on data...of a table...
inside the while @.@.fetch_status = 0 command
I want to delete some rows from the table(temporary table)
in order to not be processed...
The problem is that I want this deletion to affect the rows the cursor has.

I declared a dynamic cursor but it does not work.

Does anyone know how I can do this??

Thanks :)Perhaps the fetch into @.var is already executed, changes to the resultset do not affect values in variables? If not, could you post your code?|||The deleted is executed before the fetch next statement.

My code is:

Set @.items = Cursor
For
Select T.patentrynr,t.patcode,t.patname,cast(t.groupid As Nvarchar(10)),g.groupname,
(select Sum(idaxia) From @.trans T1
Where T.groupid = T1.groupid Group By T1.groupid )as Idaxia,
(select Sum(tamaxia) From @.trans T1
Where T.groupid = T1.groupid Group By T1.groupid) As Tamaxia,
(select Sum(insuraxia) From @.trans T1
Where T.groupid = T1.groupid Group By T1.groupid) As Insuraxia,
Itemnum,indvrate,indvamount,tamrate,
Tamamount,insurrate,insuramount,maxqty,tamname,gro upnum,t.groupid
From @.trans T Inner Join @.rates R
On R.groupid = T.groupid And R.groupid Is Not Null
And T.itemid!=cast(t.groupid As Nvarchar(10))
Inner Join Groups G On G.groupid = T.groupid
Order By T.patentrynr,t.groupid

Open @.items

Fetch From @.items Into
@.patentrynr,@.patcode,@.patname,@.itemid,@.itemname,@.i daxia,@.tamaxia,@.insuraxia,@.itemnum,
@.indvrate,@.indvamount,@.tamrate,
@.tamamount,@.insurrate,@.insuramount,@.maxqty,@.tamnam e,@.groupnum,@.groupid

While @.@.fetch_status = 0
Begin
If @.indvamount Is Not Null And @.groupnum Is Not Null And
@.idaxia Is Not Null And @.idaxia!=0
Begin
If @.groupnum > @.maxqty And @.maxqty Is Not Null
Begin
Set @.indvposo = @.maxqty*@.indvamount
End
Else
Begin
Set @.indvposo = @.groupnum*@.indvamount
End
End
Else If @.indvrate Is Not Null And @.idaxia Is Not Null
Begin
Set @.indvposo = @.idaxia*(@.indvrate/100)
End


Insert Into @.result (patentrynr,patcode,patname,tamname,itemid,itemnam e,indvtziros,
Tamtziros,insurtziros,indvpososto,tampososto,insur pososto,parakrat)
Values(@.patentrynr,@.patcode,@.patname,@.tamname,@.ite mid,@.itemname,@.idaxia,@.tamaxia,@.insuraxia,
@.indvposo,@.tamposo,@.insurposo,@.parakrat)


Set @.idaxia=null
Set @.tamaxia =null
Set @.insuraxia=null
Set @.itemnum=null
Set @.indvrate=null
Set @.indvamount=null
Set @.tamrate=null
Set @.tamamount=null
Set @.insurrate=null
Set @.insuramount=null
Set @.maxqty=null
Set @.indvposo=null
Set @.tamposo=null
Set @.insurposo=null

Delete From @.trans Where Patentrynr = @.patentrynr
And Groupid = @.groupid


Fetch Next From @.items
Into @.patentrynr,@.patcode,@.patname,@.itemid,@.itemname,
@.idaxia,@.tamaxia,@.insuraxia,@.itemnum,
@.indvrate,@.indvamount,@.tamrate,@.tamamount,
@.insurrate,@.insuramount,@.maxqty,@.tamname,@.groupnum ,@.groupid
End --end While|||I don't see a reason why rows would not be deleted from the variable table. Are you saying no rows at all are deleted from the table? What are the values of @.groupid and @.patentrynr prior the delete (what rows match)?

I've setup an example that basically does the same, perhaps it gives you an idea.

use monkey
go

set nocount on

declare @.varTab table ( myInt integer, myValue varchar(3))

insert into @.varTab (myInt, myValue) values (1, 'aaa')
insert into @.varTab (myInt, myValue) values (2, 'aaa')
insert into @.varTab (myInt, myValue) values (3, 'aaa')
insert into @.varTab (myInt, myValue) values (4, 'aaa')

declare @.myint integer
declare @.myvalue varchar(3)
declare cur_tab1 cursor DYNAMIC
for select myInt, myvalue from @.varTab

declare @.mtef cursor
set @.mtef = cur_tab1 -- ?

open @.mtef
fetch next from @.mtef into @.myint, @.myvalue
while @.@.fetch_status = 0
begin
-- update tab1 set myValue = 'bbb' where myInt = @.myInt + 1
delete from @.varTab where myInt = @.myInt
select 'myInt: ', @.myint, @.myvalue

fetch next from @.mtef into @.myint, @.myvalue
end

select * from @.varTab

deallocate @.mtef
go|||I have executed your example an it works...fine
but when i added a select statement in my code
before the fetch next statement to find what the table hoes
I found that the values are deleted...
I have also added a select statement after the fetch next...
to find out the values that will be next processed
and they are the next values found in the table before the deletion...

Do I have to change anything in the cursor declaration?|||I'm not sure what you're saying. So the rows are deleted from @.trans? What do you mean with "Do I have to change anything in the cursor declaration?" (assuming the delete works)?|||The delete works but...
I want the fetch next to fetch the next row in the table after the deletion.
This does not work.
It cursor fetches the next row in the table as it was before the deletion.
It seems that the data in the cursor is static...and it is not affected by the deletion.|||so it's like...

...
for select myInt, myvalue
from @.varTab
order by myInt -- order by clause
...
delete from @.varTab where myInt = @.myInt + 1 -- First run: delete myInt = 2
...
deallocate @.mtef
deallocate cur_tab1

The output includes all four rows...

Does your cursor declaration include an 'ORDER BY'-clause?
If it does, your cursor is converted into a KEYSET-cursor (see BOL on this).
What I know from keyset cursors is from BOL, so I'm a bit guessing here but I think the deletes are not visible because it's not the cursor doing the deletes.

Row deletion

Hi!
I have a table with the history of blocks for few months. It is
automaticaly filled as new blocks appear. Recently I found that
someone is deleting data from two columns. /* he updates two columns
to NULL value */
It is done by sime kind of sheduled job or trigger, because the data
is deleted right away. The guy who did this is most probably from
development and has sa rights on many servers.
Can I get some step by step hints how to track this?
My best wish is to figure out where this came from...
Perhaps he added a spare step for a job, or created a separate job on
another server, etc.
Addin an 'instead of update trigger' is not an option, cause I, myself
do some updates on this table via job. Another reason is that it is
easy to disable all triggers before the update.
Thanks.Start a SQL Profiler trace and leave it running overnight. In the morning
you should have captured the offending statement and see where it came from.
--
HTH
Ryan Waight, MCDBA, MCSE
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0309290649.1a94a0cc@.posting.google.com...
> Hi!
> I have a table with the history of blocks for few months. It is
> automaticaly filled as new blocks appear. Recently I found that
> someone is deleting data from two columns. /* he updates two columns
> to NULL value */
> It is done by sime kind of sheduled job or trigger, because the data
> is deleted right away. The guy who did this is most probably from
> development and has sa rights on many servers.
> Can I get some step by step hints how to track this?
> My best wish is to figure out where this came from...
> Perhaps he added a spare step for a job, or created a separate job on
> another server, etc.
> Addin an 'instead of update trigger' is not an option, cause I, myself
> do some updates on this table via job. Another reason is that it is
> easy to disable all triggers before the update.
> Thanks.|||In this case SQL Server profiler may help you to some good
level. Through which you can start a new trace.
Add an event for "stmtcompleted" under TSQL event class.
This will capture SQL statements that are getting
executed.
It will give you details about
the "applicationname"/"ntusername"/"loginname" that is
executing the statement.
- Vishal
>--Original Message--
>Hi!
>I have a table with the history of blocks for few months.
It is
>automaticaly filled as new blocks appear. Recently I
found that
>someone is deleting data from two columns. /* he updates
two columns
>to NULL value */
>It is done by sime kind of sheduled job or trigger,
because the data
>is deleted right away. The guy who did this is most
probably from
>development and has sa rights on many servers.
>Can I get some step by step hints how to track this?
>My best wish is to figure out where this came from...
>Perhaps he added a spare step for a job, or created a
separate job on
>another server, etc.
>Addin an 'instead of update trigger' is not an option,
cause I, myself
>do some updates on this table via job. Another reason is
that it is
>easy to disable all triggers before the update.
>Thanks.
>.
>|||If it were me, I would search every one of my stored procs and triggers for
an update statement that updates the table in question. For each stored
proc found, I would determine what other objects (or jobs) call this stored
proc. This should make it somewhat easy to determine how this is happening.
BTW, Largo SQL Tools makes this quite easy to do.
J.R.
Largo SQL Tools
The Finest Collection of SQL Tools Available
http://www.largosqltools.com
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0309290649.1a94a0cc@.posting.google.com...
> Hi!
> I have a table with the history of blocks for few months. It is
> automaticaly filled as new blocks appear. Recently I found that
> someone is deleting data from two columns. /* he updates two columns
> to NULL value */
> It is done by sime kind of sheduled job or trigger, because the data
> is deleted right away. The guy who did this is most probably from
> development and has sa rights on many servers.
> Can I get some step by step hints how to track this?
> My best wish is to figure out where this came from...
> Perhaps he added a spare step for a job, or created a separate job on
> another server, etc.
> Addin an 'instead of update trigger' is not an option, cause I, myself
> do some updates on this table via job. Another reason is that it is
> easy to disable all triggers before the update.
> Thanks.