question about cascading deletes

[Dan]

Hi, I'm using SQL server 2000, and I have set up two tables, table A
and table B. Table A and B have a foreign key constraint such that if
an entry is deleted in table A, then all the entries in table B
associated with that entry are deleted as well. I imagine that I can
find out the number of records that are deleted in table A when I
execute the SQL, but is there an easy way of determining the number of
records that will be deleted in table B?

Thanks,
Dan

[Simon Hayes]

"Dan" <en********@sbcglobal.net> wrote in message
news:8c**************************@posting.google.c om...

Hi, I'm using SQL server 2000, and I have set up two tables, table A
and table B. Table A and B have a foreign key constraint such that if
an entry is deleted in table A, then all the entries in table B
associated with that entry are deleted as well. I imagine that I can
find out the number of records that are deleted in table A when I
execute the SQL, but is there an easy way of determining the number of
records that will be deleted in table B?

Thanks,
Dan

When you execute the delete statement, @@ROWCOUNT will have the number of
rows deleted from A, but there is no similar way to find out the rows
deleted from B. You could query how many rows will be affected by your
delete criteria before deleting, but that may not be reliable - if the
system is busy, the number of rows could change between your SELECT and your
DELETE queries.

One possible approach is a trigger on the child table (B) which stores
@@ROWCOUNT in a log table:

create trigger dbo.MyTrigger
on dbo.MyChildTable
after delete
as
declare @i int
set @i = @@ROWCOUNT
if @i > 0
insert into dbo.MyLogTable (TableName, RowsDeleted)
values ('MyChildTable', @i)

Simon