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

if object_id(N'dbo.Connections') 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_Connections_Update
on dbo.Connections
for update
as
begin
    set nocount on

    if (not exists(select * from inserted))  
        return;

    -- If the only changed column is one of the ''Count'' columns then kick out
    declare @count int;
    with cteColumns as (select COLUMN_NAME, columnproperty(object_id(TABLE_SCHEMA + ''.'' + TABLE_NAME), COLUMN_NAME, ''ColumnID'') as COLUMN_ID from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''Connections'')
        ,cteResult as (select COLUMN_NAME as [Column], COLUMN_ID, power(2, (case COLUMN_ID % 8 when 0 then 8 else COLUMN_ID % 8 end - 1)) as Value, (COLUMN_ID - 1) / 8 as Chunk from cteColumns)
        ,cteChanged as (select [Column], cast(case substring(columns_updated(), Chunk + 1, 1) & Value when Value then 1 else 0 end as bit) as Changed from cteResult)
    select @count = count(*) from cteChanged
    where [Column] not in (''Version'', ''AttachmentCount'', ''AttachmentPrivateCount'', ''TodoOpenCount'', ''HandbookCount'', ''InventoryReportCount'') and Changed = 1;
    if (@count = 0)
        return;

    -- history
    insert into dbo.ConnectionHistory(HistoryType, ConnectionID, DATA, SecurityGroup, ConnectionType, ConnectionSubType, GroupName, Name, MetaData, CreationDate, Description, ModifiedDate, ModifiedUsername, ModifiedLoggedUserName, Version, RepositoryID, IsSubConnection, IsTemplate, Groups, ConnectionMasterSubType, UrlLink, Keywords, SortPriority, Status, StatusMessage, Image, Expiration, PermissionCacheID)
    select N''U'', ID, DATA, SecurityGroup, ConnectionType, ConnectionSubType, GroupName, Name, MetaData, CreationDate, Description, isnull(ModifiedDate, getutcdate()), ModifiedUsername, ModifiedLoggedUserName, Version, RepositoryID, IsSubConnection, IsTemplate, Groups, ConnectionMasterSubType, UrlLink, Keywords, SortPriority, Status, StatusMessage, Image, Expiration, PermissionCacheID from inserted

    -- Did Root change Security/Permission? If so reset connection cache ID(s)
    if exists (select 1 from inserted i inner join deleted d on i.ID = d.ID where i.PermissionCacheID <> d.PermissionCacheID and i.ConnectionType = 92 /* Root */ )
    begin
        update dbo.DatabaseInfo set IntelligentCacheID = newid();
        return;
    end

    -- intelligent cache, when changing security of a folder, change all contained sessions
    update b
    set
        GroupName = b.GroupName -- set any field to itself
    from (select N''%;'' + i.GroupName + N''[\;]%'' as GroupName, i.ID, i.RepositoryID
          from inserted i
               inner
          join deleted d on i.ID = d.ID
          where (isnull(i.SecurityGroup, ''00000000-0000-0000-0000-000000000000'') <> isnull(d.SecurityGroup, ''00000000-0000-0000-0000-000000000000'') or
                 i.PermissionCacheID <> d.PermissionCacheID) and i.ConnectionType = 25 /* Group */) a
        inner join dbo.Connections b on N'';'' + b.GroupName + N'';'' like a.GroupName and b.ID <> a.ID and a.RepositoryID = b.RepositoryID
end
');
end;