This script can be used as a trigger to limit the number of versions for a particular document
2007-10-02 (first published: 2002-06-20)
15,459 reads
This script can be used as a trigger to limit the number of versions for a particular document
Declare @cmd VarChar(1000) ,
@LeafCount varchar(50),
@ID varchar (100) ,
@LeafName varchar (50),
@versionNum int
declare Timeval cursor for
SELECT DocVersions.Id AS ID, Docs.LeafName AS LeafName, COUNT(Docs.LeafName) AS LeafCount
FROM DocVersions INNER JOIN
Docs ON DocVersions.Id = Docs.Id
GROUP BY DocVersions.Id, Docs.LeafName
Open Timeval
FETCH NEXT from Timeval INTO @ID, @LeafName, @LeafCount
WHILE @@FETCH_STATUS = 0
Begin
--Print '{' + @ID + '}'
if @LeafCount > 4
SELECT @versionNum = MIN(version) FROM DocVersions WHERE Id = '{' + @ID + '}'
DELETE FROM DocVersions
WHERE version = @versionNum
AND Id = '{' + @ID + '}'
FETCH NEXT from Timeval INTO @ID, @LeafName, @LeafCount
end
close Timeval
deallocate Timeval