IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_UpdateFileSize') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateFileSize
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateFileSize
 @PortalId INT,
 @EntryId INT,
 @FileSize BIGINT
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Entries
SET [FileSize]=@FileSize
WHERE
 [EntryId]=@EntryId
 AND [PortalId]=@PortalId

GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_EntryTriggerInsert') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerInsert
GO

CREATE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerInsert
ON {databaseOwner}{objectQualifier}DMX_Entries
AFTER INSERT
AS

IF TRIGGER_NESTLEVEL() < 2
BEGIN

 -- select self (in root)
 UPDATE e
 SET e.[Path] = '0;' + CAST(i.EntryId AS NVARCHAR(20)) + ';',
  e.LastModified = ISNULL(e.LastModified, GETDATE()),
  e.LastVersionId = ISNULL(e.LastVersionId, e.EntryId)
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN INSERTED i ON i.EntryId=e.EntryId
  WHERE i.CollectionId=0;

 -- select self (not in root)
 UPDATE e
 SET e.[Path] = e1.[Path] + CAST(i.EntryId AS NVARCHAR(20)) + ';',
  e.LastModified = ISNULL(e.LastModified, GETDATE()),
  e.LastVersionId = ISNULL(e.LastVersionId, e.EntryId)
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.EntryId LIKE e.CollectionId
  INNER JOIN INSERTED i ON i.EntryId=e.EntryId;
 
 -- select parents where new entry
 UPDATE e
 SET e.[FileSize] = e.[FileSize] + i.FileSize,
  e.LastModified = GETDATE()
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN INSERTED i ON i.CollectionId=e1.EntryId
  WHERE e.EntryType LIKE 'Collection%' AND (i.LastVersionId IS NULL OR i.LastVersionId=i.EntryId);

END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_EntryTriggerUpdate') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerUpdate
GO

CREATE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerUpdate
ON {databaseOwner}{objectQualifier}DMX_Entries
AFTER UPDATE
AS

IF TRIGGER_NESTLEVEL() < 2
 AND (UPDATE(Author) OR UPDATE(CollectionId) OR UPDATE([Entry]) OR UPDATE(EntryType) 
  OR UPDATE(IsApproved) OR UPDATE(Keywords) OR UPDATE(MD5Hash) OR UPDATE(OriginalFilename)
  OR UPDATE([State]) OR UPDATE(PermissionsOnlyByAdmin) OR UPDATE(InheritAttributes))
BEGIN

 DECLARE @PortalId INT
 SELECT @PortalId = (SELECT TOP 1 PortalId FROM INSERTED);
 DECLARE @Now DATETIME
 SET @Now = GETDATE()

-- move: select old parents
 UPDATE e
 SET e.[FileSize] = e.[FileSize] - i.FileSize
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN DELETED d ON d.CollectionId=e1.EntryId
  INNER JOIN INSERTED i ON i.EntryId=d.EntryId AND i.CollectionId<>d.CollectionId
 WHERE e.EntryType LIKE 'Collection%';

-- move: select new parents
 UPDATE e
 SET e.[FileSize] = e.[FileSize] + i.FileSize,
  e.LastModified = @Now
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN INSERTED i ON i.CollectionId=e1.EntryId
  INNER JOIN DELETED d ON i.EntryId=d.EntryId AND i.CollectionId<>d.CollectionId
 WHERE e.EntryType LIKE 'Collection%';

-- move: select self and children
 UPDATE e 
 SET [Path]=NULL
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN DELETED d ON e.[Path] LIKE d.[Path]+'%'
  INNER JOIN INSERTED i ON i.EntryId=d.EntryId AND i.CollectionId<>d.CollectionId;
 EXEC {databaseOwner}{objectQualifier}DMX_UpdatePaths @PortalId, -1;

-- nomove/nonewversion: select parents
 UPDATE e
 SET e.LastModified = @Now
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN INSERTED i ON i.CollectionId=e1.EntryId
  INNER JOIN DELETED d ON i.EntryId=d.EntryId AND i.CollectionId=d.CollectionId AND i.FileSize=d.FileSize;

-- nomove/newversion: select parents
 UPDATE e
 SET e.LastModified = @Now,
  e.FileSize = e.FileSize - d.FileSize + i.FileSize
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN INSERTED i ON i.CollectionId=e1.EntryId
  INNER JOIN DELETED d ON i.EntryId=d.EntryId AND i.CollectionId=d.CollectionId AND i.FileSize<>d.FileSize;

-- move: select self
 UPDATE e
 SET e.LastModified = @Now
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN INSERTED i ON i.EntryId=e.EntryId
  INNER JOIN DELETED d ON i.EntryId=d.EntryId AND d.LastModified=i.LastModified;
 
END
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_EntryTriggerDelete') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerDelete
GO

CREATE TRIGGER {databaseOwner}{objectQualifier}DMX_EntryTriggerDelete
ON {databaseOwner}{objectQualifier}DMX_Entries
AFTER DELETE
AS

IF TRIGGER_NESTLEVEL() < 2
BEGIN

 DECLARE @PortalId INT
 SELECT @PortalId = (SELECT TOP 1 PortalId FROM INSERTED);

-- select parents
 UPDATE e
 SET e.[FileSize] = e.[FileSize] - d.FileSize
 FROM {databaseOwner}{objectQualifier}DMX_Entries e
  INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries e1 ON e1.Path LIKE e.Path+'%'
  INNER JOIN DELETED d ON d.CollectionId=e1.EntryId
  WHERE d.EntryId=d.LastVersionId AND e.EntryType LIKE 'Collection%';
 
END
GO

