触发器对事务的影响

[cqdj]

有一次我偶然发现触发器会对事务的操作有影响,不知道大家遇到过没有,比如说,用rollback trasaction 不能回滚插入操作,如果删除出发器,就可以正常回滚,有那位高手知道为什么请不惜赐教,并告知如何避免出现这种问题.谢谢!

[8992026]

贴出触发器代码和调用插入语句的代码，简单说明情况
触发器应该不影响事务的，除非触发器代码有问题。

[txlicenhe]

rollback 对insert,update,delete都有效，可能是哪一处没写对吧。

[txlicenhe]

rollback 对insert,update,delete都有效，可能是哪一处没写对吧。

[CrazyFor]

应该没有这样的问题的.你的触发器代码贴出来.

[cqdj]

表的代码:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ICCardOutOilBillInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ICCardOutOilBillInfo]
GO

CREATE TABLE [dbo].[ICCardOutOilBillInfo] (
[StageNo] [int] NULL ,
[OilTypeCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ClientID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[ICInnerID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[TruncCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[FarmCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[OutOilBillCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[SAPBillType] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[PlanOutOilNumberGJ] [decimal](20, 3) NULL ,
[PlanOutOilNumber] [decimal](20, 3) NULL ,
[FactOutOilNumber] [decimal](20, 3) NULL ,
[FactOutOilNumberGJ] [decimal](20, 3) NULL ,
[MeasureUnit] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[evidenceID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[OutPassword] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[PlaceNumber] [smallint] NULL ,
[TicketOperatorName] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[OperatorDateTime] [datetime] NULL ,
[LimitDateTime] [datetime] NULL ,
[PostAccountflag] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[BeginDatetime] [datetime] NULL ,
[EndDateTime] [datetime] NULL ,
[ICClassFlag] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[device_code] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[DriverName] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[FinishFlag] [int] NULL ,
[Density] [decimal](20, 5) NULL ,
[Meterrate] [decimal](20, 3) NULL ,
[Tempmode] [int] NULL ,
[Temper] [decimal](20, 3) NULL ,
[BucketNumber] [int] NULL ,
[SendMode] [int] NULL ,
[LUNo] [int] NULL
) ON [PRIMARY]
GO

[cqdj]

触发器的代码:

CREATE TRIGGER tI_ICCardOutOilBillInfo ON dbo.ICCardOutOilBillInfo
FOR INSERT
AS
begin
Declare @tab_name_ICCardOutOilBillLog nvarchar(200),
@cmd_inst_str nvarchar(2000),
@UnPostSapLogID nvarchar(20)


if @@trancount >0
save transaction trans_a
else
begin transaction trans_a

select @tab_name_ICCardOutOilBillLog ='ICCardOutOilBillLog_'+convert(nchar(4),datepart(year,getdate()))+'_History'

-- select * into table_temp from inserted
select * into #table_temp from inserted
--将新记录插入相应的历史表
if exists (select * from dbo.sysobjects where id = object_id( @tab_name_ICCardOutOilBillLog) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
select @cmd_inst_str = 'insert into '+@tab_name_ICCardOutOilBillLog +' select * from #table_temp'
execute (@cmd_inst_str)
end
else
begin
select @cmd_inst_str = 'select * into '+@tab_name_ICCardOutOilBillLog + ' from #table_temp'
execute (@cmd_inst_str)
end


/**********************************************************************************************************/
--插入未过帐历史表

exec SP_GetBillCode '15','0000','J06B',@UnPostSapLogID output

insert into ICCardUnPostSapLog (
UnPostSapLogID,
ICInnerID,
ClientID,
OilTypeCode,
FarmCode,
OutOilBillCode,
SAPBillCode,
SAPOilBillCode,
SAPBillType,
FactOutOilNumber,
MeasureUnit,
TradeType

)
select @UnPostSapLogID,
'0000000000',
ClientID,
OilTypeCode,
FarmCode,
OutOilBillCode,
'0000000000',
Icinnerid,
SAPBillType,
FactOutOilNumberGJ,
MeasureUnit,
0
from #table_temp

if @@error <> 0 goto error
-- commit transaction trans_a

return
error:
begin
rollback transaction trans_a
raiserror('A error accoured, this Inserting operator failed!',16,-1)
return
end
end

[dj0109]

好像你的触发器里面有对存储过程的调用,如果这个存储过程中使用了显示的commit tranasaction 语句,则可能造成整个事务不能回滚.你试试看.

[pengdali]

触发器对事务就是有影响的。你在触发器里rollback tran都会回滚掉操作的数据。

[cqdj]

谢谢dj0109，我查了一下，果然是我在触发器中使用了commit，删除后正常。

[cqdj]

谢谢所有回贴的朋友！

[nnwq]

关注.....