sqlserver里的after insert触发器，使用游标版

CREATE TRIGGER [dbo].[actStatisticsTrigger_actObj_insert]
ON [dbo].[gl_activity_customer]
AFTER INSERT
AS
  DECLARE @assignId NVARCHAR(32) --活动执行人
  DECLARE @actObjCount NUMERIC(5) --统计变量
  DECLARE @actDate NVARCHAR(8)  --活动当天日期
  DECLARE @dayInWeek NVARCHAR(1) --活动当天是周几
  DECLARE @myFlag NVARCHAR(1) --标志

  --找出同一天的同一个活动执行人的銷售活動/開拓準保戶/拜訪影響力中心(C of I)之活動的活动对象总人数
  DECLARE myCursor CURSOR FOR --定义游标
  SELECT t2.assign_id, COUNT(t1.tid),CONVERT(NVARCHAR(8), t2.active_date, 112),t2.day_in_week, 'a'
  FROM inserted t1 inner join gl_activity t2 ON t1.activity_id = t2.tid 
       AND t2.type_a = 'AGY_STD__10' AND t2.type_b = 'AGY_STD__1002' AND t2.type_c = 'AGY_STD__100201' 
       AND t2.active_state = 'done' AND t2.status = '1' AND t1.status = '1'
  GROUP BY t2.assign_id, CONVERT(NVARCHAR(8), t2.active_date, 112),t2.day_in_week
  UNION ALL
  --找出同一天的同一个活动执行人的AGY和STD通路下的所有活動的所有活动对象的推介人数的总人数
  SELECT t2.assign_id, COUNT(t1.recoperson_num),CONVERT(NVARCHAR(8), t2.active_date, 112),t2.day_in_week, 'b'
  FROM inserted t1 inner join gl_activity t2 ON t1.activity_id = t2.tid 
       AND (t2.type_a LIKE '%AGY%' OR t2.type_a LIKE '%STD%')
  GROUP BY t2.assign_id, CONVERT(NVARCHAR(8), t2.active_date, 112),t2.day_in_week
  UNION ALL
  --找出同一天的同一个活动执行人的管理及增員活動/增員篩選/轉介紹名單(Ref)之活動所有活动对象的推介人数的总人数
  SELECT t2.assign_id, COUNT(t1.recoperson_num),CONVERT(NVARCHAR(8), t2.active_date, 112),t2.day_in_week, 'c'
  FROM inserted t1 inner join gl_activity t2 ON t1.activity_id = t2.tid 
       AND t2.type_a = 'AGY_STD__11' AND t2.type_b = 'AGY_STD__1101' AND t2.type_c = 'AGY_STD__110110' 
       AND t2.active_state = 'done' AND t2.status = '1' AND t1.status = '1'
  GROUP BY t2.assign_id, CONVERT(NVARCHAR(8), t2.active_date, 112),t2.day_in_week

  OPEN myCursor
  
    FETCH NEXT FROM myCursor INTO @assignId, @actObjCount, @actDate, @dayInWeek, @myFlag

    WHILE(@@FETCH_STATUS = 0)
    BEGIN
     IF(@myFlag = 'a' OR @myFlag = 'b')
     BEGIN
       DECLARE @var_count INT
       SELECT @var_count = COUNT(*) FROM gl_activity_sale_statistics WITH(TABLOCKX) WHERE date_now = @actDate AND agent_number = @assignId

       IF(@var_count = 0)
       BEGIN
            DECLARE @yearIn NVARCHAR(4) --活动当天所属年份
            DECLARE @quarterIn NVARCHAR(1) --活动当天所属季度
            DECLARE @monthIn NVARCHAR(2) --活动当天所属月份
            DECLARE @weekIn NVARCHAR(20) --周的标识

            SET @yearIn = DATEPART(yyyy, @actDate)
            SET @quarterIn = DATEPART(q, @actDate)
            SET @monthIn = DATEPART(mm, @actDate)
            SET @weekIn = dbo.getWeekInStr(@actDate)

            IF(CAST(@monthIn AS INT) < 10)
            BEGIN
                 SET @monthIn = '0' + @monthIn
            END
            
            IF(@myFlag = 'a')
            BEGIN
              INSERT INTO gl_activity_sale_statistics(tid, agent_number, year_in, quarter_in, month_in, week_in, day_in, date_now, is_plan, is_edit_byhand, AGY_STD__100201)
              VALUES(replace(newid(),'-',''), @assignId, @yearIn, @quarterIn, @monthIn, @weekIn, @dayInWeek, @actDate, '0', '0', @actObjCount)
            END
            IF(@myFlag = 'b')
            BEGIN
                 INSERT INTO gl_activity_sale_statistics(tid, agent_number, year_in, quarter_in, month_in, week_in, day_in, date_now, is_plan, is_edit_byhand, AGY_STD__A0_rec_people_num)
                 VALUES(replace(newid(),'-',''), @assignId, @yearIn, @quarterIn, @monthIn, @weekIn, @dayInWeek, @actDate, '0', '0', @actObjCount)
            END
       END
       ELSE
       BEGIN
            IF(@myFlag = 'a')
            BEGIN
              UPDATE gl_activity_sale_statistics 
              SET AGY_STD__100201 = ISNULL(AGY_STD__100201, 0) + @actObjCount 
              WHERE agent_number = @assignId AND date_now = @actDate AND is_edit_byhand = '0'
            END
            IF(@myFlag = 'b')
            BEGIN
              UPDATE gl_activity_sale_statistics 
              SET AGY_STD__A0_rec_people_num = ISNULL(AGY_STD__A0_rec_people_num, 0) + @actObjCount 
              WHERE agent_number = @assignId AND date_now = @actDate AND is_edit_byhand = '0'
            END
       END
     END
     
     IF(@myFlag = 'c')
     BEGIN
       DECLARE @var_count2 INT
       SELECT @var_count2 = COUNT(*) FROM gl_activity_emp_statistics WITH(TABLOCKX) WHERE date_now = @actDate AND agent_number = @assignId
       IF(@var_count2 = 0)
       BEGIN
              INSERT INTO gl_activity_emp_statistics(tid, agent_number, year_in, quarter_in, month_in, week_in, day_in, date_now, is_plan, is_edit_byhand, AGY_STD__110110)
              VALUES(replace(newid(),'-',''), @assignId, @yearIn, @quarterIn, @monthIn, @weekIn, @dayInWeek, @actDate, '0', '0', @actObjCount)
       END
       ELSE
       BEGIN
              UPDATE gl_activity_emp_statistics 
              SET AGY_STD__110110 = ISNULL(AGY_STD__110110, 0) + @actObjCount 
              WHERE agent_number = @assignId AND date_now = @actDate AND is_edit_byhand = '0'
       END
     END

       FETCH NEXT FROM myCursor INTO @assignId, @actObjCount, @actDate, @dayInWeek, @myFlag
    END

  CLOSE myCursor



  
GO