declare @objectID int;
select @objectID = object_id from sys.triggers where parent_id = object_id(N'dbo.Todo') and name= N'tr_TodoOpenCount';

if object_id(N'dbo.Todo') is not null
begin
    declare @createOrAlter nvarchar(6);
    set @createOrAlter = case when @objectID IS NULL then N'create' else N'alter' end;
    exec (@createOrAlter + N' trigger dbo.tr_TodoOpenCount
on dbo.Todo after insert, update, delete
as 
begin
    with cteRows as
    (
        select ConnectionID, Status from inserted i union select ConnectionID, Status from deleted d
    )
    ,cteUpdate as 
    (
		select r.ConnectionID, isnull(count(distinct t.ID), 0) as TodoOpenCount
		from cteRows r
			left outer join dbo.Todo t on t.ConnectionID = r.ConnectionID and isnull(t.Status, 0) in (0 /*Default*/, 1 /*Open*/, 2 /*Assigned*/, 3 /*InProgress*/)
		group by r.ConnectionID
    )
    update c
    set TodoOpenCount = cte.TodoOpenCount
    from dbo.Connections c
        inner join cteUpdate cte on cte.ConnectionID = c.ID
    where isnull(c.TodoOpenCount, 0) <> isnull(cte.TodoOpenCount, 0);
end');
end;