mssql2000触发器,演绎日周月年数据累加统计

触发器和游标的使用一般会影响数据库的效率或性能,所以最好用于不频繁触发的情况;在对某个表执行日统计一般是每日只有一次,所以用触发器来实现把日统计数据累加到周,月,年统计表应该是良好的选择!
以下触发器适用于对批量数据同时插入而触发,如insert  into table1(……) select …… from table2
对单条数据插入当然就不用说了！

////////////////////////////////////////////////
--日统计触发器  把日统计数据累加到周统计表 月统计表 年统计表
--clearden 2006年5月 
CREATE TRIGGER  MargeStatData  ON EventDayStatData
AFTER INSERT
AS
BEGIN

--声明游标

DECLARE evenstat_cursor CURSOR FOR
SELECT STATIPAddress,STATSeverity,STATEventTime,STATEventACount  FROM  Inserted

--声明局部变量
 DECLARE @IPAddress varchar(50)
 DECLARE @Severity int
 DECLARE @sTime datetime
 DECLARE @Count bigint

 DECLARE @YearStartTime datetime
 DECLARE @MonthStartTime  datetime
 DECLARE @DayStartTime  datetime
 DECLARE @WeekStartTime datetime

--打开游标
OPEN evenstat_cursor

 FETCH NEXT FROM evenstat_cursor
 INTO @IPAddress, @Severity, @sTime,@Count

if  @@FETCH_STATUS <> 0
BEGIN
CLOSE evenstat_cursor
DEALLOCATE evenstat_cursor
return
END

--一行一行的取数据直到取完
 WHILE @@FETCH_STATUS = 0
   BEGIN             
 --SET @IPAddress = ( SELECT STATIPAddress FROM Inserted )
 --SET @Severity = ( SELECT STATSeverity FROM Inserted )
 --SET @sTime = ( SELECT STATEventTime FROM Inserted )
 --SET @Count = ( SELECT STATEventACount FROM Inserted )

 SET @YearStartTime =DateAdd( Year, Year( @sTime )-Year(0), 0 )
 SET @MonthStartTime = DateAdd( Month, Month( @sTime ) - 1, @YearStartTime )

 SET @DayStartTime =  DateAdd( Day,Day(@sTime) - 1, @MonthStartTime )

 SET @WeekStartTime = DateAdd( Day,-1*DateDiff( Day,0, @DayStartTime  ) % 7,   @DayStartTime )
 
 --周统计
  --如果本周数据尚且不存在，则创建
  if not exists( SELECT * FROM EventWeekStatData Where  STATIPAddress=@IPAddress AND STATSeverity=@Severity AND STATEventTime = @WeekStartTime   )
   INSERT INTO EventWeekStatData(STATIPAddress,STATSeverity,  STATEventTime,STATEventACount) VALUES( @IPAddress,@Severity,@WeekStartTime,0)
  --累加周数据
  UPDATE  EventWeekStatData SET STATEventACount = STATEventACount + @Count Where STATIPAddress=@IPAddress AND STATSeverity=@Severity AND STATEventTime = @WeekStartTime

 --月统计
  --如果本月数据不存在，则创建
  if not exists( SELECT * FROM EventMonthStatData Where  STATIPAddress=@IPAddress AND STATSeverity=@Severity AND STATEventTime  = @MonthStartTime  )
   INSERT INTO EventMonthStatData(STATIPAddress,STATSeverity,  STATEventTime,STATEventACount) VALUES( @IPAddress,@Severity,@MonthStartTime,0)
  --累加月数据
  UPDATE  EventMonthStatData SET STATEventACount = STATEventACount + @Count Where STATIPAddress=@IPAddress AND STATSeverity=@Severity AND STATEventTime  = @MonthStartTime
 
 --年统计
  --如果本年数据不存在，则创建
  if not exists( SELECT * FROM EventYearStatData Where  STATIPAddress=@IPAddress AND STATSeverity=@Severity  AND STATEventTime  = @YearStartTime  )
   INSERT INTO EventYearStatData(STATIPAddress,STATSeverity,  STATEventTime,STATEventACount) VALUES( @IPAddress,@Severity,@YearStartTime,0)
  --累加年数据
  UPDATE  EventYearStatData SET STATEventACount = STATEventACount + @Count Where STATIPAddress=@IPAddress AND STATSeverity=@Severity AND STATEventTime  = @YearStartTime

 FETCH NEXT FROM evenstat_cursor INTO @IPAddress, @Severity, @sTime,@Count

    END 
CLOSE evenstat_cursor
DEALLOCATE evenstat_cursor
END