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

if object_id(N'dbo.Connections') is not null
begin
    declare @createOrAlter nvarchar(6);
    
    -- this is a hack, the open close square brakets where deleted once and broke this code, use char(91) & char(93) instead
    declare @braketStart nvarchar(1);
    declare @braketEnd nvarchar(1);
    set @braketStart = char(91);
    set @braketEnd = char(93);

    set @createOrAlter = case when @objectID IS NULL then N'create' else N'alter' end;
    exec (@createOrAlter + N' trigger dbo.tr_Connections_Insert
on dbo.Connections
for insert
as 
begin
	set nocount on

    -- if it''s a resurrect, flag the delete as ''R'' - Resurrect
    update c
    set HistoryType = N''R''
    from inserted i
        inner join dbo.ConnectionHistory c on c.ConnectionID = i.ID and c.HistoryType = N''D''

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

    -- 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''' + @braketStart + N'\;' + @braketEnd + N'%'' as GroupName, i.ID, i.RepositoryID
          from inserted i 
          where i.ConnectionType = 25) 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;