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

if object_id(N'dbo.Attachment') 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_Attachment_Insert_Delete
on dbo.Attachment after insert, delete
as 
begin
    with cteConnections as
    (
        select i.ConnectionID from inserted i
        union
        select d.ConnectionID from deleted d
    )
    ,cteAttachmentCounts as
    (
        select
            b.ConnectionID
            ,sum(case a.IsPrivate when 0 then 1 else 0 end) as AttachmentCount
            ,sum(case a.IsPrivate when 1 then 1 else 0 end) as AttachmentPrivateCount
            ,sum(case when a.IsPrivate = 1 and PrivateSubType = ''Inventory'' then 1 else 0 end) as InventoryReportCount
        from cteConnections b
            left outer join dbo.Attachment a on a.ConnectionID = b.ConnectionID
        group by b.ConnectionID
    )
    update c
    set
       AttachmentCount = b.AttachmentCount
       ,AttachmentPrivateCount = b.AttachmentPrivateCount
       ,InventoryReportCount = b.InventoryReportCount
    from dbo.Connections c
        inner join cteAttachmentCounts b on c.ID = b.ConnectionID
end');
end;