复杂的存储过程，高手请进！(on line waiting...)

[good-code]

问题如下：
A表中有car_number,car_name,carTeam_number
B表中有carTeam_number,carTeam_name,carTeam_count
如何用存储过程 在删除A表中的car_number一条记录时,B表中的carTeam_count自动减少1

我的这个存储过程只要传入一个car_number参数就可以删除一条数据，但B表中的carTeam_count却没有减少 。
问题出在哪呢？谢谢各位赐教...
create PROCEDURE delcarinfo
@car_number nvarchar(50)
AS
delete from car_info where car_number=@car_number

declare @updateNumber nvarchar(32)
select @updateNumber = carTeam_number from A where car_number=@car_number
update B set carTeam_count=carTeam_count-1 where carTeam_number=@updateNumber

[good-code]

是不是没有更新到B表中的carTeam_count字段值呢？

[good-code]

怎么没人解答呢？
...

[jiating520]

等待高手..

[xuStanly]

表A和B以什么相关联？另外，最好给几行测试数据。

[xuStanly]

表A是不是就是car_info?

[aspnet19851111]

谁帮忙看下我的帖子，超简单的问题100分
http://topic.csdn.net/u/20090829/09/aa3f7946-7a23-4c61-99f1-c9a46a301bcf.html?seed=342764719&r=59421199#r_59421199

[good-code]

对啊

[xuStanly]

[Quote=引用 7 楼 wjmo520 的回复:]
对啊

[/Quote]

create PROCEDURE delcarinfo
@car_number nvarchar(50)
AS

declare @updateNumber nvarchar(32)
select @updateNumber = carTeam_number from A where car_number=@car_number
delete from car_info where car_number=@car_number
update B set carTeam_count=carTeam_count-1 where carTeam_number=@updateNumber

先select再delete。

[三楼の郎]

create PROCEDURE delcarinfo
@car_number nvarchar(50)
AS
declare @updateNumber nvarchar(32)
select @updateNumber = carTeam_number from A where car_number=@car_number
delete from car_info where car_number=@car_number
update B set carTeam_count=carTeam_count-1 where carTeam_number=@updateNumber

[good-code]

有先后顺序之分吗？

[三楼の郎]

[Quote=引用 10 楼 wjmo520 的回复:]
有先后顺序之分吗？

[/Quote]
当然，先Delete的话记录没有了，Select怎么还能查到东西！

[xuStanly]

另外，涉及到多表操作，最好用上事务

create PROCEDURE delcarinfo
@car_number nvarchar(50)
AS

declare @n int, @m int
declare @updateNumber nvarchar(32)

begin tran
select @updateNumber = carTeam_number from A where car_number=@car_number

delete from  car_info where car_number=@car_number
set @n = @@error

update B set carTeam_count=carTeam_count-1 where carTeam_number=@updateNumber
set @m = @@error

if @n = 0 and @m = 0 commit tran
else rollback tran

大概就是上面这个意思，我顺手写的。

[mbh0210]

你先更新后删除


delete from car_info where car_number=@car_number

declare @updateNumber nvarchar(32)
select @updateNumber = carTeam_number from A where car_number=@car_number
update B set carTeam_count=carTeam_count-1 where carTeam_number=@updateNumber

你已经删掉，在通过select @updateNumber = carTeam_number from A where car_number=@car_number 这条记录找数据，你觉得找的到吗？
应该是这样写
declare @updateNumber nvarchar(32)
select @updateNumber = carTeam_number from A where car_number=@car_number
update B set carTeam_count=carTeam_count-1 where carTeam_number=@updateNumber

delete from car_info where car_number=@car_number

[good-code]

呵呵 谢谢各位 问题经已解决啦 原来是顺序错了
现在可以啦 。

[good-code]

[Quote=引用 12 楼 xustanly 的回复:]
另外，涉及到多表操作，最好用上事务
SQL codecreatePROCEDURE delcarinfo@car_numbernvarchar(50)ASdeclare@nint,@mintdeclare@updateNumbernvarchar(32)begintranselect@updateNumber= carTeam_numberfrom Awhere car_number=@car_numberdeletefrom car_infowhere car_number=@car_numberset@n=@@errorupdate Bset carTeam_count=carTeam_count-1where carTeam_number=@updateNumberset@m=@@errorif@n=0and@m=0committranelserollbacktran

大概就是上面这个意思，我顺手写的。
[/Quote]
您说的用事务，那他的好处是什么？

[qinyeya]

where car_number=@car_number 好像是查不到數據的，因為前面你已經刪除了
最好是把兩個表的關系貼出來，帶點示例數據

[mbh0210]

用事务可以保证数据完整性，比如，更新完了，但是删除失败，那么事务会回滚的
，不会出现你已经减去1了，但是数据还没有删除的情况

[xuStanly]

[Quote=引用 17 楼 mbh0210 的回复:]
用事务可以保证数据完整性，比如，更新完了，但是删除失败，那么事务会回滚的
，不会出现你已经减去1了，但是数据还没有删除的情况
[/Quote]

顶

[netniule]

[Quote=引用 8 楼 xustanly 的回复:]
引用 7 楼 wjmo520 的回复:
对啊



create PROCEDURE delcarinfo
@car_number nvarchar(50)
AS

declare @updateNumber nvarchar(32)
select @updateNumber = carTeam_number from A where car_number=@car_number
delete from  car_info where car_number=@car_number
update B set carTeam_count=carTeam_count-1 where carTeam_number=@updateNumber

先select再delete。
[/Quote]
删除了就找不到

[good-code]

OK 明白！ 谢谢你们...