• 全部
  • .NET Framework
  • ASP
  • Web Services
  • .NET互联网桌面应用
  • VB
  • 图表区
  • 分析与设计
  • 组件/控件开发
  • AppLauncher
  • 问答

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

good-code 2009-08-29 10:59:55
问题如下:
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
...全文
73 点赞 收藏 20
写回复
20 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
good-code 2009-08-29
OK 明白! 谢谢你们...
回复
netniule 2009-08-29
[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]
删除了就找不到
回复
xuStanly 2009-08-29
[Quote=引用 17 楼 mbh0210 的回复:]
用事务可以保证数据完整性,比如,更新完了,但是删除失败,那么事务会回滚的
,不会出现你已经减去1了,但是数据还没有删除的情况
[/Quote]



回复
mbh0210 2009-08-29
用事务可以保证数据完整性,比如,更新完了,但是删除失败,那么事务会回滚的
,不会出现你已经减去1了,但是数据还没有删除的情况
回复
qinyeya 2009-08-29
where car_number=@car_number 好像是查不到數據的,因為前面你已經刪除了
最好是把兩個表的關系貼出來,帶點示例數據
回复
good-code 2009-08-29
[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]
您说的用事务,那他的好处是什么?
回复
good-code 2009-08-29
呵呵 谢谢各位 问题经已解决啦 原来是顺序错了
现在可以啦 。
回复
mbh0210 2009-08-29
你先更新后删除


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

回复
xuStanly 2009-08-29
另外,涉及到多表操作,最好用上事务
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


大概就是上面这个意思,我顺手写的。
回复
三楼の郎 2009-08-29
[Quote=引用 10 楼 wjmo520 的回复:]
有先后顺序之分吗?

[/Quote]
当然,先Delete的话记录没有了,Select怎么还能查到东西!
回复
good-code 2009-08-29
有先后顺序之分吗?
回复
三楼の郎 2009-08-29
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
回复
xuStanly 2009-08-29
[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。
回复
good-code 2009-08-29
对啊
回复
xuStanly 2009-08-29
表A是不是就是car_info?
回复
xuStanly 2009-08-29
表A和B以什么相关联?另外,最好给几行测试数据。
回复
jiating520 2009-08-29
等待高手..
回复
good-code 2009-08-29
怎么没人解答呢?
...
回复
good-code 2009-08-29
是不是没有更新到B表中的carTeam_count字段值呢?
回复
相关推荐
发帖
.NET技术社区
创建于2007-09-28

5.8w+

社区成员

.NET技术交流专区
申请成为版主
帖子事件
创建了帖子
2009-08-29 10:59
社区公告
暂无公告