参数的问题:-h "hint [,...n]"指定在大容量复制数据到表或视图时所使用的提示。在大容量复制数据到 SQL Server 6.x 或更早版本时,不能使用此选项。FIRE_TRIGGERS 与 in 参数一起指定,在目的表上定义的任何插入触发器将在大容量复制操作期间执行。如果没有指定 FIRE_TRIGGERS,则不执行插入触发器。对于 out、queryout 和 format 参数,将忽略 FIRE_TRIGGERS。
加了FIRE_TRIGGERS 参数后,好象只在最后一行插入触发器才发生作用,是不是游标位于最后一行,其他行不发生作用?如果用语句: insert into table1 select * from table2 成批插入数据时触发器又会怎样?我是不是要重写触发器? 附源码: CREATE TRIGGER [TRIG_OutList] ON [dbo].[OutList] FOR INSERT AS declare @aTime DateTime, @aDate varchar(19), @aParam smallint, --@eDate varchar(19),@sDate varchar(10), @AreaNO smallint,@RoadNO smallint,@StationNO smallint, @InAreaNO smallint,@InRoadNO smallint,@InStationNO smallint,@SquadDate datetime,@SquadNO Tinyint, @VehType Tinyint,@VehClass Tinyint, @VehCount smallint,@VehCount0 smallint, @CashMoney int,@CashMoney0 int,@FreeMoney int,@FreeMoney0 int,@OfficeMoney int,@OfficeMoney0 int, @UnpayMoney int,@UnpayMoney0 int,@ETCMoney int,@ETCMoney0 int,@TicketType Tinyint select @aTime=OpTime,@AreaNO=AreaNO,@RoadNO=RoadNO,@StationNO=StationNO, @InAreaNO=InAreaNO,@InRoadNO=InRoadNO,@InStationNO=InStationNO,@SquadDate=SquadDate,@SquadNO=SquadNO, @VehType=VehType,@VehClass=VehClass,@VehCount=VehCount,@CashMoney=CashMoney,@FreeMoney=FreeMoney, @OfficeMoney=OfficeMoney,@UnpayMoney=UnpayMoney,@ETCMoney=ETCMoney,@TicketType=TicketType from inserted set @aParam =DATEPART(year, @aTime) set @aDate=cast(@aParam as varchar(4))+'-' set @aParam =DATEPART(Month, @aTime) set @aDate=@aDate+cast(@aParam as varchar(4))+'-' set @aParam =DATEPART(dd, @aTime) --set @sDate=@aDate+cast(@aParam as varchar(4)) ----工班日期 set @aDate=@aDate+cast(@aParam as varchar(4))+' ' set @aParam =DATEPART(hh, @aTime) --set @eDate=@aDate+cast(@aParam as varchar(2))+':59:59' --无边界时间问题 set @aDate=@aDate+cast(@aParam as varchar(2))+':00:00'set @VehCount0=0 set @CashMoney0=0 set @FreeMoney0=0 set @OfficeMoney0=0 set @UnpayMoney0=0 set @ETCMoney0=0--出口车型车种时段车流量统计中间表(OutVehHourSum_C) select @VehCount0=VehCount from OutVehHourSum_C where AreaNO=@AreaNO and RoadNO=@RoadNO and StationNO=@StationNO and OutTime=cast(@aDate as datetime) and VehClass=@VehClass and VehType=@VehType delete from OutVehHourSum_C where AreaNO=@AreaNO and RoadNO=@RoadNO and StationNO=@StationNO and OutTime=cast(@aDate as datetime) and VehClass=@VehClass and VehType=@VehType insert into OutVehHourSum_C(AreaNO,RoadNO,StationNO,OutTime,VehClass,VehType,VehCount) values(@AreaNO,@RoadNO,@StationNO,@aDate,@VehClass,@VehType,@VehCount0+@VehCount)--出口车型车种车流量收费统计中间表(OutVehSum_C) select @VehCount0=VehCount,@CashMoney0=CashMoney,@FreeMoney0=FreeMoney, @OfficeMoney0=OfficeMoney,@UnpayMoney0=UnpayMoney,@ETCMoney0=ETCMoney from OutVehSum_C where AreaNO=@AreaNO and RoadNO=@RoadNO and StationNO=@StationNO and SquadDate=@SquadDate and SquadNO=@SquadNO and VehClass=@VehClass and VehType=@VehType delete from OutVehSum_C where AreaNO=@AreaNO and RoadNO=@RoadNO and StationNO=@StationNO and SquadDate=@SquadDate and SquadNO=@SquadNO and VehClass=@VehClass and VehType=@VehType insert into OutVehSum_C(AreaNO,RoadNO,StationNO,SquadDate,SquadNO,VehClass,VehType,VehCount, CashMoney,FreeMoney,OfficeMoney,UnpayMoney,ETCMoney) values(@AreaNO,@RoadNO,@StationNO,@SquadDate,@SquadNO,@VehClass,@VehType,@VehCount0+@VehCount, @CashMoney0+@CashMoney,@FreeMoney0+@FreeMoney,@OfficeMoney0+@OfficeMoney, @UnpayMoney0+@UnpayMoney,@ETCMoney0+@ETCMoney)--出口定向车种车流量收费统计中间表(OutDirVehSum_C) select @VehCount0=VehCount,@CashMoney0=CashMoney,@FreeMoney0=FreeMoney, @OfficeMoney0=OfficeMoney,@UnpayMoney0=UnpayMoney,@ETCMoney0=ETCMoney from OutDirVehSum_C where AreaNO=@AreaNO and RoadNO=@RoadNO and StationNO=@StationNO and InAreaNO=@InAreaNO and InRoadNO=@InRoadNO and InStationNO=@InStationNO and SquadDate=@SquadDate and SquadNO=@SquadNO and VehClass=@VehClass and VehType=@VehType delete from OutDirVehSum_C where AreaNO=@AreaNO and RoadNO=@RoadNO and StationNO=@StationNO and InAreaNO=@InAreaNO and InRoadNO=@InRoadNO and InStationNO=@InStationNO and SquadDate=@SquadDate and SquadNO=@SquadNO and VehClass=@VehClass and VehType=@VehType insert into OutDirVehSum_C(AreaNO,RoadNO,StationNO,InAreaNO,InRoadNO,InStationNO, SquadDate,SquadNO,VehClass,VehType,VehCount, CashMoney,FreeMoney,OfficeMoney,UnpayMoney,ETCMoney) values(@AreaNO,@RoadNO,@StationNO,@InAreaNO,@InRoadNO,@InStationNO, @SquadDate,@SquadNO,@VehClass,@VehType,@VehCount0+@VehCount, @CashMoney0+@CashMoney,@FreeMoney0+@FreeMoney,@OfficeMoney0+@OfficeMoney, @UnpayMoney0+@UnpayMoney,@ETCMoney0+@ETCMoney) --出口IC卡回收表(OutICCardGet) select @VehCount0=TicketCount from OutICCardGet where AreaNO=@AreaNO and RoadNO=@RoadNO and StationNO=@StationNO and InAreaNO=@InAreaNO and InRoadNO=@InRoadNO and InStationNO=@InStationNO and SquadDate=@SquadDate and SquadNO=@SquadNO and TicketType=@TicketTypedelete from OutICCardGet where AreaNO=@AreaNO and RoadNO=@RoadNO and StationNO=@StationNO and InAreaNO=@InAreaNO and InRoadNO=@InRoadNO and InStationNO=@InStationNO and SquadDate=@SquadDate and SquadNO=@SquadNO and TicketType=@TicketTypeinsert into OutICCardGet(AreaNO,RoadNO,StationNO,InAreaNO,InRoadNO,InStationNO, SquadDate,SquadNO,TicketType,TicketCount) values(@AreaNO,@RoadNO,@StationNO,@InAreaNO,@InRoadNO,@InStationNO, @SquadDate,@SquadNO,@TicketType,@VehCount0+@VehCount)
insert into table1 select * from table2
成批插入数据时触发器又会怎样?我是不是要重写触发器?
附源码:
CREATE TRIGGER [TRIG_OutList] ON [dbo].[OutList]
FOR INSERT
AS
declare @aTime DateTime, @aDate varchar(19), @aParam smallint, --@eDate varchar(19),@sDate varchar(10),
@AreaNO smallint,@RoadNO smallint,@StationNO smallint,
@InAreaNO smallint,@InRoadNO smallint,@InStationNO smallint,@SquadDate datetime,@SquadNO Tinyint,
@VehType Tinyint,@VehClass Tinyint,
@VehCount smallint,@VehCount0 smallint,
@CashMoney int,@CashMoney0 int,@FreeMoney int,@FreeMoney0 int,@OfficeMoney int,@OfficeMoney0 int,
@UnpayMoney int,@UnpayMoney0 int,@ETCMoney int,@ETCMoney0 int,@TicketType Tinyint select @aTime=OpTime,@AreaNO=AreaNO,@RoadNO=RoadNO,@StationNO=StationNO,
@InAreaNO=InAreaNO,@InRoadNO=InRoadNO,@InStationNO=InStationNO,@SquadDate=SquadDate,@SquadNO=SquadNO,
@VehType=VehType,@VehClass=VehClass,@VehCount=VehCount,@CashMoney=CashMoney,@FreeMoney=FreeMoney,
@OfficeMoney=OfficeMoney,@UnpayMoney=UnpayMoney,@ETCMoney=ETCMoney,@TicketType=TicketType
from inserted set @aParam =DATEPART(year, @aTime)
set @aDate=cast(@aParam as varchar(4))+'-'
set @aParam =DATEPART(Month, @aTime)
set @aDate=@aDate+cast(@aParam as varchar(4))+'-'
set @aParam =DATEPART(dd, @aTime)
--set @sDate=@aDate+cast(@aParam as varchar(4)) ----工班日期
set @aDate=@aDate+cast(@aParam as varchar(4))+' '
set @aParam =DATEPART(hh, @aTime)
--set @eDate=@aDate+cast(@aParam as varchar(2))+':59:59' --无边界时间问题
set @aDate=@aDate+cast(@aParam as varchar(2))+':00:00'set @VehCount0=0
set @CashMoney0=0
set @FreeMoney0=0
set @OfficeMoney0=0
set @UnpayMoney0=0
set @ETCMoney0=0--出口车型车种时段车流量统计中间表(OutVehHourSum_C)
select @VehCount0=VehCount from OutVehHourSum_C
where AreaNO=@AreaNO and RoadNO=@RoadNO and StationNO=@StationNO and OutTime=cast(@aDate as datetime)
and VehClass=@VehClass and VehType=@VehType delete from OutVehHourSum_C
where AreaNO=@AreaNO and RoadNO=@RoadNO and StationNO=@StationNO and OutTime=cast(@aDate as datetime)
and VehClass=@VehClass and VehType=@VehType insert into OutVehHourSum_C(AreaNO,RoadNO,StationNO,OutTime,VehClass,VehType,VehCount)
values(@AreaNO,@RoadNO,@StationNO,@aDate,@VehClass,@VehType,@VehCount0+@VehCount)--出口车型车种车流量收费统计中间表(OutVehSum_C)
select @VehCount0=VehCount,@CashMoney0=CashMoney,@FreeMoney0=FreeMoney,
@OfficeMoney0=OfficeMoney,@UnpayMoney0=UnpayMoney,@ETCMoney0=ETCMoney from OutVehSum_C
where AreaNO=@AreaNO and RoadNO=@RoadNO and StationNO=@StationNO and SquadDate=@SquadDate
and SquadNO=@SquadNO and VehClass=@VehClass and VehType=@VehType delete from OutVehSum_C
where AreaNO=@AreaNO and RoadNO=@RoadNO and StationNO=@StationNO and SquadDate=@SquadDate
and SquadNO=@SquadNO and VehClass=@VehClass and VehType=@VehType insert into OutVehSum_C(AreaNO,RoadNO,StationNO,SquadDate,SquadNO,VehClass,VehType,VehCount,
CashMoney,FreeMoney,OfficeMoney,UnpayMoney,ETCMoney)
values(@AreaNO,@RoadNO,@StationNO,@SquadDate,@SquadNO,@VehClass,@VehType,@VehCount0+@VehCount,
@CashMoney0+@CashMoney,@FreeMoney0+@FreeMoney,@OfficeMoney0+@OfficeMoney,
@UnpayMoney0+@UnpayMoney,@ETCMoney0+@ETCMoney)--出口定向车种车流量收费统计中间表(OutDirVehSum_C)
select @VehCount0=VehCount,@CashMoney0=CashMoney,@FreeMoney0=FreeMoney,
@OfficeMoney0=OfficeMoney,@UnpayMoney0=UnpayMoney,@ETCMoney0=ETCMoney from OutDirVehSum_C
where AreaNO=@AreaNO and RoadNO=@RoadNO and StationNO=@StationNO
and InAreaNO=@InAreaNO and InRoadNO=@InRoadNO and InStationNO=@InStationNO
and SquadDate=@SquadDate and SquadNO=@SquadNO and VehClass=@VehClass and VehType=@VehType delete from OutDirVehSum_C
where AreaNO=@AreaNO and RoadNO=@RoadNO and StationNO=@StationNO
and InAreaNO=@InAreaNO and InRoadNO=@InRoadNO and InStationNO=@InStationNO
and SquadDate=@SquadDate and SquadNO=@SquadNO and VehClass=@VehClass and VehType=@VehType insert into OutDirVehSum_C(AreaNO,RoadNO,StationNO,InAreaNO,InRoadNO,InStationNO,
SquadDate,SquadNO,VehClass,VehType,VehCount,
CashMoney,FreeMoney,OfficeMoney,UnpayMoney,ETCMoney)
values(@AreaNO,@RoadNO,@StationNO,@InAreaNO,@InRoadNO,@InStationNO,
@SquadDate,@SquadNO,@VehClass,@VehType,@VehCount0+@VehCount,
@CashMoney0+@CashMoney,@FreeMoney0+@FreeMoney,@OfficeMoney0+@OfficeMoney,
@UnpayMoney0+@UnpayMoney,@ETCMoney0+@ETCMoney)
--出口IC卡回收表(OutICCardGet)
select @VehCount0=TicketCount from OutICCardGet
where AreaNO=@AreaNO and RoadNO=@RoadNO and StationNO=@StationNO
and InAreaNO=@InAreaNO and InRoadNO=@InRoadNO and InStationNO=@InStationNO
and SquadDate=@SquadDate and SquadNO=@SquadNO and TicketType=@TicketTypedelete from OutICCardGet
where AreaNO=@AreaNO and RoadNO=@RoadNO and StationNO=@StationNO
and InAreaNO=@InAreaNO and InRoadNO=@InRoadNO and InStationNO=@InStationNO
and SquadDate=@SquadDate and SquadNO=@SquadNO and TicketType=@TicketTypeinsert into OutICCardGet(AreaNO,RoadNO,StationNO,InAreaNO,InRoadNO,InStationNO,
SquadDate,SquadNO,TicketType,TicketCount)
values(@AreaNO,@RoadNO,@StationNO,@InAreaNO,@InRoadNO,@InStationNO,
@SquadDate,@SquadNO,@TicketType,@VehCount0+@VehCount)