trigger.txt

利用触发器通过修改小时内的数据自动实现月报,年报的更新

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go













ALTER TRIGGER [month_change] ON [dbo].[ReportHis2008] 
after update 
AS 
BEGIN 
DECLARE @NewValue numeric(15,3) 
DECLARE @SID int
DECLARE @PID int 
DECLARE @pTime datetime
DECLARE @TeamName varchar(20) 

SELECT @NewValue=NewValue,@SID=SID,@PID=PID,@pTime=PTime,@TeamName=TeamName FROM INSERTED 

BEGIN

UPDATE reportmonth12008 SET NewValue=(select sum(newValue) from reporthis2008 where SID=@SID 
and PID=@PID and PTIME BETWEEN REPLACE(STR(YEAR(@pTime), 4, 0), ' ', 0) + '-' + REPLACE(STR(MONTH(@pTime), 2, 0), ' ', 0) 
                      + '-' + REPLACE(STR(DAY(@pTime), 2, 0), ' ', 0) + ' ' + '00' + ':' + '00'+':'+'00' AND REPLACE(STR(YEAR(@pTime), 4, 0), ' ', 0) 
                      + '-' + REPLACE(STR(MONTH(@pTime), 2, 0), ' ', 0) + '-' + REPLACE(STR(DAY(@pTime) + 1, 2, 0), ' ', 0) + ' ' + '00' + ':' + '00'+':'+'59'
)where SID=@SID and PID=@PID
and  Ddate=replace(str(year(@pTime),4,0),' ',0)+'-'+replace(str(month(@pTime),2,0),' ',0)+'-'+replace(str(day(@pTime),2,0),' ',0)

UPDATE reportyear SET NewValue=(select sum(newValue) from reportmonth12008 where SID=@SID 
and PID=@PID and mdate between replace(str(year(@pTime),4,0),' ',0)+'-'+replace(str(month(@pTime),2,0),' ',0)+'-'+'00'and
replace(str(year(@pTime),4,0),' ',0)+'-'+replace(str(month(@pTime),2,0),' ',0)+'-'+'32')  where SID=@SID and PID=@PID
and  Mdate=replace(str(year(@pTime),4,0),' ',0)+'-'+replace(str(month(@pTime),2,0),' ',0)
End

Else
BEGIN

UPDATE reportbasic2008 SET NewValue=(select sum(newValue) from reporthis2008 where SID=@SID 
and PID=@PID and PTIME BETWEEN REPLACE(STR(YEAR(@pTime), 4, 0), ' ', 0) + '-' + REPLACE(STR(MONTH(@pTime), 2, 0), ' ', 0) 
                      + '-' + REPLACE(STR(DAY(@pTime), 2, 0), ' ', 0) + ' ' + '00' + ':' + '00'+':'+'00' AND REPLACE(STR(YEAR(@pTime), 4, 0), ' ', 0) 
                      + '-' + REPLACE(STR(MONTH(@pTime), 2, 0), ' ', 0) + '-' + REPLACE(STR(DAY(@pTime) + 1, 2, 0), ' ', 0) + ' ' + '00' + ':' + '00'+':'+'59'
and TeamName=@TeamName)  where SID=@SID and PID=@PID
and  Ddate=replace(str(year(@pTime),4,0),' ',0)+'-'+replace(str(month(@pTime),2,0),' ',0)+'-'+replace(str(day(@pTime),2,0),' ',0) and TeamName=@TeamName

UPDATE reportmonth2 SET NewValue=(select sum(newValue) from reportbasic2008 where SID=@SID 
and PID=@PID and Ddate between replace(str(year(@pTime),4,0),' ',0)+'-'+replace(str(month(@pTime),2,0),' ',0)+'-'+'00'and
replace(str(year(@pTime),4,0),' ',0)+'-'+replace(str(month(@pTime),2,0),' ',0)+'-'+'32' and TeamName=@TeamName)  where SID=@SID and PID=@PID
and  Mdate=replace(str(year(@pTime),4,0),' ',0)+'-'+replace(str(month(@pTime),2,0),' ',0) and TeamName=@TeamName
END
END




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go














ALTER TRIGGER [month_change] ON [dbo].[ReportHis2008] 
after update 
AS 
BEGIN 
DECLARE @NewValue numeric(15,3) 
DECLARE @SID int
DECLARE @PID int 
DECLARE @pTime datetime
DECLARE @TeamName varchar(20) 

SELECT @NewValue=NewValue,@SID=SID,@PID=PID,@pTime=PTime,@TeamName=TeamName FROM INSERTED 


UPDATE reportmonth12008 SET NewValue=(select sum(newValue) from reporthis2008 where SID=@SID 
and PID=@PID and PTIME BETWEEN REPLACE(STR(YEAR(@pTime), 4, 0), ' ', 0) + '-' + REPLACE(STR(MONTH(@pTime), 2, 0), ' ', 0) 
                      + '-' + REPLACE(STR(DAY(@pTime), 2, 0), ' ', 0) + ' ' + '00' + ':' + '00'+':'+'00' AND REPLACE(STR(YEAR(@pTime), 4, 0), ' ', 0) 
                      + '-' + REPLACE(STR(MONTH(@pTime), 2, 0), ' ', 0) + '-' + REPLACE(STR(DAY(@pTime) + 1, 2, 0), ' ', 0) + ' ' + '00' + ':' + '00'+':'+'59'
)where SID=@SID and PID=@PID
and  Ddate=replace(str(year(@pTime),4,0),' ',0)+'-'+replace(str(month(@pTime),2,0),' ',0)+'-'+replace(str(day(@pTime),2,0),' ',0)

UPDATE reportyear SET NewValue=(select sum(newValue) from reportmonth12008 where SID=@SID 
and PID=@PID and Ddate between replace(str(year(@pTime),4,0),' ',0)+'-'+replace(str(month(@pTime),2,0),' ',0)+'-'+'01'and
replace(str(year(@pTime),4,0),' ',0)+'-'+replace(str(month(@pTime),2,0),' ',0)+'-'+'31')  where SID=@SID and PID=@PID
and  Mdate=replace(str(year(@pTime),4,0),' ',0)+'-'+replace(str(month(@pTime),2,0),' ',0)



END