declare @objectID int;
select @objectID = object_id from sys.triggers where parent_id = object_id(N'dbo.SessionRecording') and name= N'tr_SessionRecording';
if object_id(N'dbo.SessionRecording') 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_SessionRecording
on dbo.SessionRecording after insert, delete
as 
begin
    with cteRows as
    (
        select ConnectionLogID from inserted i union select ConnectionLogID from deleted d
    )
    ,cteUpdate as 
    (
        select r.ConnectionLogID, cast(case when isnull(count(distinct t.ID), 0) > 0 then 1 else 0 end as bit) as HasRecording
        from cteRows r
                left outer join dbo.SessionRecording t on t.ConnectionLogID = r.ConnectionLogID
        group by r.ConnectionLogID
    )
    update c
    set HasRecording = cte.HasRecording
    from dbo.ConnectionLog c
        inner join cteUpdate cte on cte.ConnectionLogID = c.ID
    where isnull(c.HasRecording, 0) <> isnull(cte.HasRecording, 0);

    with cteRows as
    (
        select ConnectionID from inserted i union select ConnectionID from deleted d
    )
    ,cteUpdate as 
    (
        select r.ConnectionID, count(distinct t.ID) as RecordingCount
        from cteRows r
            left outer join dbo.SessionRecording t on t.ConnectionID = r.ConnectionID
        group by r.ConnectionID
    )
    update c
    set RecordingCount = cte.RecordingCount
    from dbo.Connections c
        inner join cteUpdate cte on cte.ConnectionID = c.ID
    where isnull(c.RecordingCount, 0) <> isnull(cte.RecordingCount, 0);end
');
end;