CREATE PROCEDURE [Test]
@fid int
AS
begin tran
delete from a where fid =@fid
delete from b where tid in (select tid from c where fid =@fid)
delete from c where fid =@fid
if @@error>0
rollback tran
else
commit tran
@fid int
AS
begin tran
delete from a where fid =@fid
delete from b where tid in (select tid from c where fid =@fid)
delete from c where fid =@fid
if @@error>0
rollback tran
else
commit tran
表a
fid fenleiname num
1 分类1 2
2 分类2 1
3 分类3 1表b
tid fenleiname num
1 分类1 2
2 分类2 3表c
id fid tid name
1 1 1 xxxx
2 1 2 bbbb
3 2 2 yyyy
4 3 2 cccc如果删除表a中fid为1的记录,那么表C中fid为1的两条记录就被删除,那么b中相对应的tid为1和2的num都应该减1
如何处理
表a
fid fenleiname num
1 分类1 2
2 分类2 1
3 分类3 1表b
tid fenleiname num
1 分类1 1
2 分类2 3表c
id fid tid name
1 1 1 xxxx
2 1 2 bbbb
3 2 2 yyyy
4 3 2 cccc如果删除表a中fid为1的记录,那么表C中fid为1的两条记录就被删除,那么b中相对应的tid为1和2的num都应该减1
如何处理
内容是delete from 表a where fid in (select fid from deleted )
update 表b set num=num-1 from deleted where 表b.tid=deleted.tid
@fid int
AS
begin tran
delete from a where fid =@fid
delete from c where fid =@fid
update b set num = c.num from (select tid,count(tid) as num from c group by tid) c,b where c.tid = b.tid
if @@error>0
rollback tran
else
commit tran
@fid int
AS
begin tran
delete from a where fid =@fid
delete from c where fid =@fid
update b set num = c.num from (select tid,count(tid) as num from c group by tid) c,b where c.tid = b.tid
delete from b where tid not in (select tid from c)
if @@error>0
rollback tran
else
commit tran
楼上,当....就....
明显用TRIGGER嘛,不信等红星老大来
on 表c
FOR DELETE
AS
delete from 表a where fid in (select fid from deleted )
update 表b set num=num-1 from deleted where 表b.tid=deleted.tid
go
on 表c
FOR DELETE
AS
begin tran
delete from 表a where fid in (select fid from deleted )
update 表b set num=num-1 from deleted where 表b.tid=deleted.tid
commit tran
go