程序代碼

Create TRIGGER [Web_UpdateIPAndPVTG]
   ON  [dbo].[Web_AccessLogTB]
   AFTER Insert
AS
BEGIN
    IF @@rowcount>0
    BEGIN
        DECLARE @clientip nvarchar(50), @recdatetime datetime
        DECLARE @ipcount int
        Select @clientip=ClientIP,@recdatetime=RecDateTime FROM inserted
        SET @recdatetime=DATEADD(day,DATEDIFF(day,0,@recdatetime),0)
        
        IF NOT EXISTS(Select TOP 1 * FROM Web_AccessLog2TB Where RecdateTime=@recdatetime)
        BEGIN--新增
            Insert INTO Web_AccessLog2TB(RecDateTime,IPCount,PVCount) VALUES(@recdatetime,1,1)
            TRUNCATE TABLE dbo.Web_IPLogTB --每天清空
            Insert INTO dbo.Web_IPLogTB(RecDateTime,ClientIP) VALUES(@recdatetime,@clientip)
        END
        ELSE
        BEGIN
            Select @ipcount=count(1) FROM Web_IPLogTB Where RecdateTime>=@recdatetime AND ClientIP=@clientip
            IF @ipcount=0
            BEGIN--更新IP和PV
                Update Web_AccessLog2TB SET IPCount=IPCount+1,PVCount=PVCount+1 Where RecdateTime=@recdatetime
                Insert INTO Web_IPLogTB(RecDateTime,ClientIP) VALUES(@recdatetime,@clientip)                
            END
            ELSE--只更新PV
                Update Web_AccessLog2TB SET PVCount=PVCount+1 Where RecdateTime=@recdatetime
        END            
    END
END