CREATE proc add_cgdd
(
@gys_name varchar(200),
@worker_name varchar(200),
@cangku_name varchar(200),
@write_date datetime,
@dh_date datetime,
@dj_num varchar(100),
@dj_bz varchar(200),
@dj_zy varchar(200),
@user_flag varchar(50)
)
as
declare
@sp_id varchar(100),
@sp_tm varchar(100),
@sp_name varchar(100),
@sp_dw varchar(100),
@sp_gg varchar(100),
@sp_lb varchar(100),
@cg_num dec(18,4),
@cg_price dec(18,4),
@total dec(18,4),
@tax dec(18,4),
@tax_num dec(18,4),
@tax_price dec(18,4),
@tax_total dec(18,4),
@made_date datetime,
@cbhs varchar(100)begin tranbegin
--写入cgdd表
declare C_cgdd cursor for select sp_id,sp_tm,sp_name,sp_dw,sp_gg,sp_lb,num,price,total,tax,tax_num,tax_price,tax_total,
made_date,cbhs from temp_spxx where dj_flag='1101' and user_flag=@user_flagopen C_cgdd
fetch next from C_cgdd into @sp_id ,@sp_tm,@sp_name,@sp_dw,@sp_gg,@sp_lb,@cg_num,@cg_price,@total,@tax,@tax_num,@tax_price,@tax_total,@made_date,@cbhswhile @@fetch_status=0
BEGIN
insert cgdd(sp_id,sp_tm,sp_name,sp_dw,sp_gg,sp_lb,cg_num,cg_price,total,tax,tax_num,tax_price,tax_total,made_date,cbhs,dj_num,flag1)
values(@sp_id,@sp_tm,@sp_name,@sp_dw,@sp_gg,@sp_lb,@cg_num,@cg_price,@total,@tax,@tax_num,@tax_price,@tax_total,@made_date,@cbhs,@dj_num,0)
fetch next from C_cgdd into @sp_id ,@sp_tm,@sp_name,@sp_dw,@sp_gg,@sp_lb,@cg_num,@cg_price,@total,@tax,@tax_num,@tax_price,@tax_total,@made_date,@cbhs
END
close C_cgdd
deallocate C_cgdd/*写入来往历史表*/
insert into dj_total(dj_num,gys_kh_name,worker_name,cangku_name,dj_zy,dj_bz,write_date,limit_date,flag1)
values(@dj_num,@gys_name,@worker_name,@cangku_name,@dj_zy,@dj_bz,@write_date,@dh_date,'1101')if @@error<>0
begin
rollback tran
return -1
end
commit tran
return 0
end
Go上面这个存储过程,当向cgdd中插入数据失败后,怎么还能写入到dj_total中的数据呢???
最后的
if @@error<>0
begin
rollback tran
return -1
end
commit tran
return 0
没有起作用吗???
(
@gys_name varchar(200),
@worker_name varchar(200),
@cangku_name varchar(200),
@write_date datetime,
@dh_date datetime,
@dj_num varchar(100),
@dj_bz varchar(200),
@dj_zy varchar(200),
@user_flag varchar(50)
)
as
declare
@sp_id varchar(100),
@sp_tm varchar(100),
@sp_name varchar(100),
@sp_dw varchar(100),
@sp_gg varchar(100),
@sp_lb varchar(100),
@cg_num dec(18,4),
@cg_price dec(18,4),
@total dec(18,4),
@tax dec(18,4),
@tax_num dec(18,4),
@tax_price dec(18,4),
@tax_total dec(18,4),
@made_date datetime,
@cbhs varchar(100)begin tranbegin
--写入cgdd表
declare C_cgdd cursor for select sp_id,sp_tm,sp_name,sp_dw,sp_gg,sp_lb,num,price,total,tax,tax_num,tax_price,tax_total,
made_date,cbhs from temp_spxx where dj_flag='1101' and user_flag=@user_flagopen C_cgdd
fetch next from C_cgdd into @sp_id ,@sp_tm,@sp_name,@sp_dw,@sp_gg,@sp_lb,@cg_num,@cg_price,@total,@tax,@tax_num,@tax_price,@tax_total,@made_date,@cbhswhile @@fetch_status=0
BEGIN
insert cgdd(sp_id,sp_tm,sp_name,sp_dw,sp_gg,sp_lb,cg_num,cg_price,total,tax,tax_num,tax_price,tax_total,made_date,cbhs,dj_num,flag1)
values(@sp_id,@sp_tm,@sp_name,@sp_dw,@sp_gg,@sp_lb,@cg_num,@cg_price,@total,@tax,@tax_num,@tax_price,@tax_total,@made_date,@cbhs,@dj_num,0)
fetch next from C_cgdd into @sp_id ,@sp_tm,@sp_name,@sp_dw,@sp_gg,@sp_lb,@cg_num,@cg_price,@total,@tax,@tax_num,@tax_price,@tax_total,@made_date,@cbhs
END
close C_cgdd
deallocate C_cgdd/*写入来往历史表*/
insert into dj_total(dj_num,gys_kh_name,worker_name,cangku_name,dj_zy,dj_bz,write_date,limit_date,flag1)
values(@dj_num,@gys_name,@worker_name,@cangku_name,@dj_zy,@dj_bz,@write_date,@dh_date,'1101')if @@error<>0
begin
rollback tran
return -1
end
commit tran
return 0
end
Go上面这个存储过程,当向cgdd中插入数据失败后,怎么还能写入到dj_total中的数据呢???
最后的
if @@error<>0
begin
rollback tran
return -1
end
commit tran
return 0
没有起作用吗???
begin
rollback tran
return -1
end
应该放到
insert cgdd
的后面
那岂不是在对每个表的操作后面都要放
if @@error<>0
begin
rollback tran
return -1
end
了,也太麻烦了吧?
@@ERROR
返回最后执行的 Transact-SQL 语句的错误代码。
我在处理几个表的过程中,如果其中的一个表出现问题,则其他表做过的修改都回滚,
在sql中如何设计呢?
谢谢
if ... rollback tran
...commit tran你可以同时用ADOConnection的事务和在存储过程中使用事务
一次处理几个表时,一旦一个表处理失败,则全部回滚,但并不是每个表执行完毕后都用
begin tran
....
if ... rollback tran
...
commit tran
,这样太麻烦。
能不能只最后执行一边事务回滚得方法,就可以。
用外部的事务控制最安全;
而且SQLSERVER对一些致命的错误是不能完全捕捉到的,这个以前有个贴子;同时,每句执行语句后面都要判断是否有错误产生,而不是最后才判断。