deleted all records strangely

we have two tables: TIMECARDBATCH and TIMECARD. TIMECARD has a foreign key
to TIMECARDBATCH.
User can only delete one row from TIMECARDBATCH at a time. We have a delete
trigger on TIMECARDBATCH which deletes corresponding records from TIMECARD.
In our production environment at one time all records in TIMECARD gets
deleted but there are still records in TIMECARD. Now we look into the code
and the programmer whoe code the delete trigger has this code:
CREATE TRIGGER td_timecardBatch ON [dbo].[TIMECARDBATCH]
FOR DELETE
AS
declare @.batchNumber int
declare @.id int
select @.batchNumber = batchNumber
from deleted
declare c_tc cursor local for
select id from timecard where batchNumber=@.batchNumber
open c_tc
fetch c_tc into @.id
while @.@.fetch_status=0
begin
delete from timecard where id=@.id
fetch c_tc into @.id
end
What I can see wrong here is that the cursor "c_tc" does not get closed and
deallocated. Can this cause the problem?
Thanksone more question i have is :
does SQL Server log SQL statements for a database? If yes then where can i
find them?
Thanks
"JY" <jy1970us@.yahoo.com> wrote in message
news:BVKef.2604$w84.470302@.news20.bellglobal.com...
> we have two tables: TIMECARDBATCH and TIMECARD. TIMECARD has a foreign key
> to TIMECARDBATCH.
> User can only delete one row from TIMECARDBATCH at a time. We have a
> delete trigger on TIMECARDBATCH which deletes corresponding records from
> TIMECARD.
> In our production environment at one time all records in TIMECARD gets
> deleted but there are still records in TIMECARD. Now we look into the code
> and the programmer whoe code the delete trigger has this code:
> CREATE TRIGGER td_timecardBatch ON [dbo].[TIMECARDBATCH]
> FOR DELETE
> AS
> declare @.batchNumber int
> declare @.id int
> select @.batchNumber = batchNumber
> from deleted
> declare c_tc cursor local for
> select id from timecard where batchNumber=@.batchNumber
> open c_tc
> fetch c_tc into @.id
> while @.@.fetch_status=0
> begin
> delete from timecard where id=@.id
> fetch c_tc into @.id
> end
>
> What I can see wrong here is that the cursor "c_tc" does not get closed
> and deallocated. Can this cause the problem?
> Thanks
>|||The trigger is poorly written. It does not take multi-row deletes into
consideration. It also uses a cursor which is not needed. ( Not deallocating
the cursor does not seem the issue here, but in general it can cause the
cursor reference to be retained in the memory and the data structures
comprising the cursor are not released. )
Just change the trigger as:
CREATE TRIGGER td_timecardBatch ON [dbo].[TIMECARDBATCH]
FOR DELETE
AS IF @.@.ROWCOUNT = 0 RETURN
DELETE FROM timecard
WHERE EXISTS (
SELECT *
FROM deleted d
WHERE d.batchNumber = timecard.batchNumber )
Anith|||>> does SQL Server log SQL statements for a database?
Not unless you explicitly track them, perhaps using profiler trace or some
proprietary methods.
Anith|||Hi Anith,
Thanks for your reply. Yes this trigger is written poorly and your solution
is excellent.
But our client wants to know why it happened with the current trigger we
have and the only thing i can think of is that the programmer forgets to
close the cursor. I just want to confirm if this can be the cause in any
case?
Thanks
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ehZJX%23t6FHA.808@.TK2MSFTNGP09.phx.gbl...
> The trigger is poorly written. It does not take multi-row deletes into
> consideration. It also uses a cursor which is not needed. ( Not
> deallocating the cursor does not seem the issue here, but in general it
> can cause the cursor reference to be retained in the memory and the data
> structures comprising the cursor are not released. )
> Just change the trigger as:
> CREATE TRIGGER td_timecardBatch ON [dbo].[TIMECARDBATCH]
> FOR DELETE
> AS IF @.@.ROWCOUNT = 0 RETURN
> DELETE FROM timecard
> WHERE EXISTS (
> SELECT *
> FROM deleted d
> WHERE d.batchNumber = timecard.batchNumber )
> --
> Anith
>|||>> But our client wants to know why it happened with the current trigger we
Based on the code, it looks like the following line of code might be the
culprit:
select @.batchNumber = batchNumber
from deleted
This will always return only one value and the code within the cursor will
always be executed only once.
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uVeC9au6FHA.4076@.tk2msftngp13.phx.gbl...
> Based on the code, it looks like the following line of code might be the
> culprit:
>
> select @.batchNumber = batchNumber
> from deleted
> This will always return only one value and the code within the cursor will
> always be executed only once.
> --
> Anith
>
Hi Anith,
The user from the browser can only delete one TIMECARDBATCH record at a
time. So "select @.batchNumber = batchNumber from deleted" is ok as there is
one row to get deleted.
What do you think about forgetting to close the cursor? Can it be a problem
when multiple users are deleting data?
Thanks|||I suspect there is actually an unhandled error due to the FK.
Try deleting a timecardbatch row directly in SQL Query Analyzer and see
if you get an error.
The most solid solution is to change the fk in TIMECARD to cascade
deletes. [and drop that horribly written trigger :) ]
JY wrote:
> we have two tables: TIMECARDBATCH and TIMECARD. TIMECARD has a foreign key
> to TIMECARDBATCH.
> User can only delete one row from TIMECARDBATCH at a time. We have a delet
e
> trigger on TIMECARDBATCH which deletes corresponding records from TIMECARD
.
> In our production environment at one time all records in TIMECARD gets
> deleted but there are still records in TIMECARD. Now we look into the code
> and the programmer whoe code the delete trigger has this code:
> CREATE TRIGGER td_timecardBatch ON [dbo].[TIMECARDBATCH]
> FOR DELETE
> AS
> declare @.batchNumber int
> declare @.id int
> select @.batchNumber = batchNumber
> from deleted
> declare c_tc cursor local for
> select id from timecard where batchNumber=@.batchNumber
> open c_tc
> fetch c_tc into @.id
> while @.@.fetch_status=0
> begin
> delete from timecard where id=@.id
> fetch c_tc into @.id
> end
>
> What I can see wrong here is that the cursor "c_tc" does not get closed an
d
> deallocated. Can this cause the problem?
> Thanks
>