A note for sql: todo foreach group string join without cursor v 0.5

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Irene
-- Create date: 2012/5/14
-- Description:    TaskResponse更新資料後 維護Event.FinishCount
-- =============================================
ALTER TRIGGER [dbo].[trTaskResponse] ON [dbo].[TaskResponse]
    AFTER INSERT,DELETE,UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    
    -- Insert statements for trigger here
    --控制範圍
    DECLARE @statusid int;
    DECLARE @eventid int;
    DECLARE @memberid int;
    SELECT @statusid=r.StateId, @memberid=r.MemberId, @eventid=t.EventId
        FROM DELETED r INNER JOIN Task t ON r.TaskId=t.TaskId;
    SELECT @statusid=r.StateId, @memberid=r.MemberId, @eventid=t.EventId
        FROM INSERTED r INNER JOIN Task t ON r.TaskId=t.TaskId;
    --INSERT INTO DBLog VALUES ('statusid='+cast(@statusid as varchar(5))+',memberid='+cast(@memberid as varchar(5))+',eventid='+cast(@eventid as varchar(5)));
    /*
    IF (@statusid<>1)
    BEGIN
        --INSERT INTO DBLog VALUES ('statusid='+cast(@statusid as varchar(5))+'memberid='+cast(@memberid as varchar(5))+',eventid='+cast(@eventid as varchar(5)))
        RETURN;
    END
    */
    IF (@eventid IS NULL AND @memberid IS NULL)
    BEGIN
        RETURN;
    END


    --Event下各MemberId回報完成的Task串
    SELECT MemberId
    , (SELECT CAST(TaskId AS VARCHAR(10))+','
        FROM [dbo].[TaskResponse]
        WHERE MemberId=r.MemberId AND StateId=1 AND TaskId IN (
            SELECT TaskId FROM [dbo].[Task] WHERE EventId=@eventid AND StateId=1)
        ORDER BY TaskId
        FOR XML PATH ('')
       ) AS MemberTasks
    INTO #tmp
    FROM [dbo].[TaskResponse] r
    WHERE r.StateId=1 AND r.TaskId IN (
        SELECT TaskId FROM [dbo].[Task] WHERE EventId=@eventid AND StateId=1)
    GROUP BY r.MemberId;
    --SELECT * FROM #tmp;

    --Event下所謂Finish的Task串
    DECLARE @eventtasks VARCHAR(1000);
    SELECT @eventtasks=COALESCE(@eventtasks,'')+ CAST(TaskId AS VARCHAR(10))+','
        FROM [dbo].[Task]
        WHERE TaskId IN (SELECT TaskId FROM [dbo].[Task] WHERE EventId=@eventid AND StateId=1)
        ORDER BY TaskId;
    --SELECT @eventtasks;

    --整理Event的FinishCount
    --SELECT COUNT(*) FROM #tmp WHERE MemberTasks=@eventtasks
    UPDATE [dbo].[Event] SET FinishCount=(SELECT COUNT(*) FROM #tmp WHERE MemberTasks=@eventtasks)
        FROM [dbo].[Event] e
        WHERE e.EventId=@eventid;

    DROP TABLE #tmp;

END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO