declare @objectID int;
select @objectID = object_id from sys.triggers where parent_id = object_id(N'dbo.Connections') and name= N'tr_Connections_GroupName';
declare @createOrAlter nvarchar(6);
set @createOrAlter = case when @objectID is null then N'create' else N'alter' end;
exec (@createOrAlter + N' trigger dbo.tr_Connections_GroupName
on dbo.Connections after insert, update
as 
begin
    -- Delete Groups
    with cteCleanUpGroupName as
    (
        select
            ID
            ,case
                when ConnectionType = 25 and GroupName = Name then '''' -- root level
                when ConnectionType = 25 and GroupName like ''%\'' + Name then substring(GroupName, 0, len(GroupName) - len(Name))
                else GroupName end as GroupName
        from inserted
    )
    ,cteXmlGroups as
    (
        select ID, cast(''<Group>'' + replace(dbo.fn_XMLEscape(replace(GroupName, '';'', ''||Group||Group|'')), ''||Group||Group|'', ''</Group><Group>'') + ''</Group>'' as xml) Xml
        from cteCleanUpGroupName
    )
    ,cteGroups as
    (
        select x.ID, ltrim(rtrim(c.value(''.'',''nvarchar(256)''))) as GroupName
        from cteXmlGroups x
            cross apply Xml.nodes(''/Group'') T(c)
    )
    ,cteGroupsDistinct as
    (
        select distinct * from cteGroups
    )
    delete g
    from dbo.ConnectionGroup g
        inner join inserted i on i.ID = g.ConnectionID
        left outer join cteGroupsDistinct x on x.ID = g.ConnectionID and x.GroupName = g.GroupName
    where x.ID is null;

    -- New Groups
    with cteCleanUpGroupName as
    (
        select
            ID
            ,case
                when ConnectionType = 25 and GroupName = Name then ''''
                when ConnectionType = 25 and GroupName like ''%\'' + Name then substring(GroupName, 0, len(GroupName) - len(Name))
                else GroupName end as GroupName
        from inserted
    )
    ,cteXmlGroups as
    (
        select ID, cast(''<Group>'' + replace(dbo.fn_XMLEscape(replace(GroupName, '';'', ''||Group||Group|'')), ''||Group||Group|'', ''</Group><Group>'') + ''</Group>'' as xml) Xml
        from cteCleanUpGroupName
    )
    ,cteGroups as
    (
        select x.ID, ltrim(rtrim(c.value(''.'',''nvarchar(256)''))) as GroupName
        from cteXmlGroups x
            cross apply Xml.nodes(''/Group'') T(c)
    )
    ,cteGroupsDistinct as
    (
        select distinct * from cteGroups
    )
    insert into dbo.ConnectionGroup (ConnectionID, GroupName )
    select x.ID, x.GroupName
    from cteGroupsDistinct x
        left outer join dbo.ConnectionGroup g on g.GroupName = x.GroupName and g.ConnectionID = x.ID
    where g.ID is null;
end;');