触发器的代码: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
drop table [dbo].[ICCardOutOilBillInfo]
GOCREATE 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
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