declare @objectID int;
select @objectID = object_id from sys.triggers where parent_id = object_id(N'dbo.ConnectionHandbook') and name= N'tr_ConnectionHandbook';
if object_id(N'dbo.ConnectionHandbook') 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_ConnectionHandbook
on [dbo].[ConnectionHandbook]
for insert, update
as
begin
    set nocount on;

	insert into dbo.ConnectionHandbookHistory (HistoryType, ConnectionHandbookID, ConnectionID, Name, Data, GroupName, ModifiedDate, ModifiedUserName, ModifiedLoggedUserName, SortPriority, IsDefault, IsRevertable)
	select
		case
			when d.ID is null then ''I''
			else ''U''
		end
		,i.ID
		,i.ConnectionID
		,i.Name
		,i.Data
		,i.GroupName
		,case
			when d.ID is null then isnull(i.CreationDate, getutcdate())
			when i.ModifiedDate = d.ModifiedDate then getutcdate()
			else isnull(i.ModifiedDate, getutcdate())
		end
		,case
			when d.ID is null then i.CreationUsername
			else i.ModifiedUserName
		end
		,case
			when d.ID is null then i.CreationLoggedUserName
			else i.ModifiedLoggedUserName
		end
		,i.SortPriority
		,i.IsDefault
		,case /* IsRevertable true if new entry or if name or data has changed */
			when d.ID is null OR i.Name != d.Name OR i.Data != d.Data then 1
			else 0
		end
	from inserted as i left join deleted as d on d.ID = i.ID;
end
');
end;