sqlserver中编撰的after insert触发器

CREATE TRIGGER [dbo].[actStatisticsTrigger_insert]ON [dbo].[gl_activity]AFTER INSERTASDECLARE @counntOfInsert INT--考虑到一条insert语句插入多条记录的情况SELECT @counntOfInsert = COUNT(*) FROM insertedWHILE(@counntOfInsert > 0)BEGINDECLARE @firstRankType NVARCHAR(40) --第一级的活动类型DECLARE @secondRankType NVARCHAR(40) --第二级的活动类型DECLARE @thirdRankType NVARCHAR(40) --第三极的活动类型DECLARE @activeDate NVARCHAR(8) --活动举行的日期 字符串，不带-的YYYYMMDD形式DECLARE @activeHours NUMERIC(4,1) --活动持续的小时数，精确到小数点后一位DECLARE @assignId NVARCHAR(32) --活动执行人DECLARE @dayInWweek NVARCHAR(1) --活动所在的日期是周几DECLARE @actState NVARCHAR(20)DECLARE @actStatus NVARCHAR(2)SELECT @actStatus = tt.status, @actState = tt.active_state, @firstRankType = tt.type_a, @secondRankType = tt.type_b, @thirdRankType = tt.type_c, @activeDate = CONVERT(NVARCHAR(8), tt.active_date, 112), @activeHours = tt.active_hours, @assignId = tt.assign_id, @dayInWweek = tt.day_in_week FROM inserted tt WHERE (SELECT COUNT(*) FROM inserted tmp WHERE tmp.tid <= tt.tid ) = @counntOfInsertDECLARE @yearIn NVARCHAR(4) --活动当天所属年份DECLARE @quarterIn NVARCHAR(1) --活动当天所属季度DECLARE @monthIn NVARCHAR(2) --活动当天所属月份DECLARE @weekIn NVARCHAR(20) --周的标识SET @yearIn = DATEPART(yyyy, @activeDate)SET @quarterIn = DATEPART(q, @activeDate)SET @monthIn = DATEPART(mm, @activeDate)SET @weekIn = dbo.getWeekInStr(@activeDate)IF(CAST(@monthIn AS INT) < 10)BEGIN     SET @monthIn = '0' + @monthInENDIF(@actStatus = '1' and @actState = 'done')BEGIN  --更新gl_activity_time_statistics  IF(@firstRankType = 'AGY_STD__10' OR @firstRankType = 'AGY_STD__11' OR ((@firstRankType not like '%AGY%') AND (@firstRankType not like '%STD%')))  BEGIN       DECLARE @var_count INT       --给时间统计表加锁，不允许别的事务读取和操作这张表，直至本事务结束       SELECT @var_count = COUNT(*) FROM gl_activity_time_statistics WITH(TABLOCKX) WHERE date_now = @activeDate AND agent_number = @assignId       IF(@var_count = 0)       BEGIN            DECLARE @insertSql NVARCHAR(500)            DECLARE @insertNewId NVARCHAR(32)            SET @insertNewId = replace(newid(),'-','')            --sql中用两个单引号来表示一个单引号            SET @insertSql = 'INSERT INTO gl_activity_time_statistics(tid, agent_number, year_in, quarter_in, month_in, week_in, day_in, date_now, is_plan, is_edit_byhand, ' + @secondRankType +' ) values(''' + @insertNewId + ''', ''' + @assignId + ''', ''' + @yearIn + ''', ''' + @quarterIn + ''', ''' + @monthIn + ''', ''' + @weekIn + ''', ''' + @dayInWweek + ''', ''' + @activeDate + ''', ''0'', ''0'', ' + CAST(@activeHours AS NVARCHAR(10)) + ')'             exec(@insertSql)       END       ELSE       BEGIN            DECLARE @updateSql NVARCHAR(500)            SET @updateSql = 'update gl_activity_time_statistics set ' + @secondRankType + ' = ISNULL(' + @secondRankType + ',0) + ' + CAST(@activeHours AS NVARCHAR(10)) + ' where agent_number = ''' + @assignId + ''' and date_now = ''' + @activeDate + ''' and is_edit_byhand = ''0'''            exec(@updateSql)       END  END  --更新gl_activity_sale_statistics  IF((@firstRankType = 'AGY_STD__10' AND (@secondRankType = 'AGY_STD__1001' OR @secondRankType = 'AGY_STD__1002') AND @thirdRankType <> 'AGY_STD__100201') OR (@firstRankType like '%BR%' OR @firstRankType like '%BD%'))  BEGIN       DECLARE @var_count2 INT       SELECT @var_count2 = COUNT(*) FROM gl_activity_sale_statistics WITH(TABLOCKX) WHERE date_now = @activeDate AND agent_number = @assignId              IF(@var_count2 = 0)       BEGIN            DECLARE @insertSql2 NVARCHAR(500)            DECLARE @insertNewId2 NVARCHAR(32)            SET @insertNewId2 = replace(newid(),'-','')            SET @insertSql2 = '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, ' + @thirdRankType +' ) values(''' + @insertNewId + ''', ''' + @assignId + ''', ''' + @yearIn + ''', ''' + @quarterIn + ''', ''' + @monthIn + ''', ''' + @weekIn + ''', ''' + @dayInWweek + ''', ''' + @activeDate + ''', ''0'', ''0'', ' + CAST(1 AS NVARCHAR(10)) + ')'             exec(@insertSql2)       END       ELSE       BEGIN            DECLARE @updateSql2 NVARCHAR(500)            SET @updateSql2 = 'update gl_activity_sale_statistics set ' + @thirdRankType + ' = ISNULL(' + @thirdRankType + ',0) + ' + CAST(1 AS NVARCHAR(10)) + ' where agent_number = ''' + @assignId + ''' and date_now = ''' + @activeDate + ''' and is_edit_byhand = ''0'''            exec(@updateSql2)       END  END  --更新更新gl_activity_emp_statistics  IF(@firstRankType = 'AGY_STD__11' AND @secondRankType = 'AGY_STD__1101' AND @thirdRankType <> 'AGY_STD__110110')  BEGIN       DECLARE @var_count3 INT       SELECT @var_count3 = COUNT(*) FROM gl_activity_emp_statistics WITH(TABLOCKX) WHERE date_now = @activeDate AND agent_number = @assignId       IF(@var_count3 = 0)       BEGIN            DECLARE @insertSql3 NVARCHAR(500)            DECLARE @insertNewId3 NVARCHAR(32)            SET @insertNewId3 = replace(newid(),'-','')            SET @insertSql3 = '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, ' + @thirdRankType +' ) values(''' + @insertNewId + ''', ''' + @assignId + ''', ''' + @yearIn + ''', ''' + @quarterIn + ''', ''' + @monthIn + ''', ''' + @weekIn + ''', ''' + @dayInWweek + ''', ''' + @activeDate + ''', ''0'', ''0'', ' + CAST(1 AS NVARCHAR(10)) + ')'             exec(@insertSql3)       END       ELSE       BEGIN            DECLARE @updateSql3 NVARCHAR(500)            SET @updateSql3 = 'update gl_activity_emp_statistics set ' + @thirdRankType + ' = ISNULL(' + @thirdRankType + ',0) + ' + CAST(1 AS NVARCHAR(10)) + ' where agent_number = ''' + @assignId + ''' and date_now = ''' + @activeDate + ''' and is_edit_byhand = ''0'''            exec(@updateSql3)       END  ENDENDSET @counntOfInsert = @counntOfInsert - 1ENDGO