PB前台插入数据的时候,有时候会出现:ROLLBACK TRANSACTION 请求没有对应的 BEGIN TRANSACTION。这个错误。。
是什么原因造成的?
代码如下。。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER TRIGGER [Tr_ctn_dynamic]
ON [dbo].[DT_Ctn_Dynamic]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
create table #ctn_dym(
id int identity(1,1),
Ctn_No varchar(20),
Main_Bl_No varchar(30),
Ie_Flag varchar(1),
Dynamic_Type varchar(40),
op_time varchar(20),
ef_flag varchar(1)
) --此处忽略部分变量声明---
declare @ctn_id int declare @rows int
set @ctn_id = 1
delete from #ctn_dym
insert into #ctn_dym(ctn_no,main_bl_no,ie_flag,dynamic_type,op_time,ef_flag)
select container_code,Main_Bl_No,ie_flag,dynamic_type,date_time,ef_flag from inserted
set @rows = @@rowcount
--开始循环
while @ctn_id <= @rows
begin
select @ctn_no = ctn_no,@op_time = op_time,@op_type = Dynamic_Type,@bl = main_bl_no,@ef_flag = ef_Flag
from #ctn_dym where id = @ctn_id
select @last_op_time = op_time from hdfc_container where Container_no = @ctn_no
if @@rowcount = 1
begin
if @last_op_time < @op_time
begin
if @op_type = 'EE'
begin
set @cy_flag = 'O'
set @country = 'CHN'
end
if @op_type = 'EI'
begin
set @cy_flag = 'I'
set @country = 'CHN'
end
update hdfc_container set op_type = @op_type,EF_FLAG = @EF_FLAG,country = @country , cy_flag = @cy_flag,op_time = @op_time ,
bl = @bl
where container_no = @ctn_no
end
set @CTN_ID = @ctn_id + 1
end
end
END
是什么原因造成的?
代码如下。。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER TRIGGER [Tr_ctn_dynamic]
ON [dbo].[DT_Ctn_Dynamic]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
create table #ctn_dym(
id int identity(1,1),
Ctn_No varchar(20),
Main_Bl_No varchar(30),
Ie_Flag varchar(1),
Dynamic_Type varchar(40),
op_time varchar(20),
ef_flag varchar(1)
) --此处忽略部分变量声明---
declare @ctn_id int declare @rows int
set @ctn_id = 1
delete from #ctn_dym
insert into #ctn_dym(ctn_no,main_bl_no,ie_flag,dynamic_type,op_time,ef_flag)
select container_code,Main_Bl_No,ie_flag,dynamic_type,date_time,ef_flag from inserted
set @rows = @@rowcount
--开始循环
while @ctn_id <= @rows
begin
select @ctn_no = ctn_no,@op_time = op_time,@op_type = Dynamic_Type,@bl = main_bl_no,@ef_flag = ef_Flag
from #ctn_dym where id = @ctn_id
select @last_op_time = op_time from hdfc_container where Container_no = @ctn_no
if @@rowcount = 1
begin
if @last_op_time < @op_time
begin
if @op_type = 'EE'
begin
set @cy_flag = 'O'
set @country = 'CHN'
end
if @op_type = 'EI'
begin
set @cy_flag = 'I'
set @country = 'CHN'
end
update hdfc_container set op_type = @op_type,EF_FLAG = @EF_FLAG,country = @country , cy_flag = @cy_flag,op_time = @op_time ,
bl = @bl
where container_no = @ctn_no
end
set @CTN_ID = @ctn_id + 1
end
end
END
解决方案 »
- 将主从数据行转成列(有难度)
- 请问如下问题的SQL如何实现?
- 如何能取到存储过程返回的某一项.
- 难题求解【标题要【长】【长】【长】【长】【长】【长】【长】【长】】
- 从外部导进的数据库用户名不同导致表格的名称全变了怎么办?
- 求一SQL
- null使用随机的历史数据替换问题请教
- 多个TADOQuery不能同时通过一个TADOConnection进行Query?
- 同一sql组下有两个实例,如何用作业操作另一个实例的表?
- 请告知SQlServer2000在winxp上安装失败的原因,以下是sqlstp.log中的错误内容
- 怎么导excel到表中的指定列?高手请教
- Windows切换用户后如何连接SQL Server?
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE a
SET a.op_type = b.op_type, a.EF_FLAG = b.EF_FLAG,
a.country = CASE b.Dynamic_Type WHEN 'EE' THEN 'CHN' WHEN 'EI' THEN 'CHN' END,
a.cy_flag = CASE b.Dynamic_Type WHEN 'EE' THEN 'O' WHEN 'EI' THEN 'I' END,
a.op_time = b.op_time, a.bl = b.bl
FROM hdfc_container a,INSERTED b
WHERE a.container_no = b.ctn_no
SET NOCOUNT OFF;
GO这样试试
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE a
SET a.op_type = b.Dynamic_Type, a.EF_FLAG = b.EF_FLAG,
a.country = CASE b.Dynamic_Type WHEN 'EE' THEN 'CHN' WHEN 'EI' THEN 'CHN' END,
a.cy_flag = CASE b.Dynamic_Type WHEN 'EE' THEN 'O' WHEN 'EI' THEN 'I' END,
a.op_time = b.date_time, a.bl = b.main_bl_no
FROM hdfc_container a,inserted b
WHERE a.container_no = b.ctn_no
SET NOCOUNT OFF;
GO改一下。
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE a
SET a.op_type = b.Dynamic_Type, a.EF_FLAG = b.EF_FLAG,
a.country = CASE b.Dynamic_Type WHEN 'EE' THEN 'CHN' WHEN 'EI' THEN 'CHN' END,
a.cy_flag = CASE b.Dynamic_Type WHEN 'EE' THEN 'O' WHEN 'EI' THEN 'I' END,
a.op_time = b.date_time, a.bl = b.main_bl_no
FROM hdfc_container a,inserted b
WHERE a.container_no = b.container_code
SET NOCOUNT OFF;
GO
begin
select @ctn_no = ctn_no,@op_time = op_time,@op_type = Dynamic_Type,@bl = main_bl_no,@ef_flag = ef_Flag
from #ctn_dym where id = @ctn_id
select @last_op_time = op_time from hdfc_container where Container_no = @ctn_no
if @@rowcount = 1
begin
if @last_op_time < @op_time
begin
if @op_type = 'EE'
begin
set @cy_flag = 'O'
set @country = 'CHN'
end
if @op_type = 'EI'
begin
set @cy_flag = 'I'
set @country = 'CHN'
end
update hdfc_container set op_type = @op_type,EF_FLAG = @EF_FLAG,country = @country , cy_flag = @cy_flag,op_time = @op_time ,
bl = @bl
where container_no = @ctn_no
end
--set @CTN_ID = @ctn_id + 1
end
set @CTN_ID = @ctn_id + 1--写到这里
end
begin
if @@rowcount = 1
begin
--set @CTN_ID = @ctn_id + 1 这个可能出现死循环
end
set @CTN_ID = @ctn_id + 1--写到这里
end