USE <database>
GO

CREATE TRIGGER DBTrg_Ver_change ON DATABASE
    FOR DDL_TRIGGER_EVENTS, DDL_VIEW_EVENTS, DDL_TABLE_EVENTS,
        DDL_SCHEMA_EVENTS, DDL_FUNCTION_EVENTS, DDL_PROCEDURE_EVENTS,
        DDL_INDEX_EVENTS
AS
-- Here's the code to add the extended property rather than creating a new object in the database just to hold the value
-- For this trigger to work, you need to have the DBVersion extended property created and set to whatever value you want to start with.
-- EXEC sp_addextendedproperty 
-- @name = N'DBVersion', @value = '000.000.0001';
-- GO
    DECLARE @verno VARCHAR(25) ,
        @main INT ,
        @minor INT ,
        @rev INT
-- Read & extract the current version number parts from the database
    SET @verno = ( SELECT   CONVERT(VARCHAR(12), value)
                   FROM     sys.extended_properties
                   WHERE    class = 0
                            AND NAME = 'DBVersion'
                 )
    SET @main = SUBSTRING(@verno, 1, CHARINDEX('.', @verno, 0) - 1)
    SET @minor = SUBSTRING(@verno, CHARINDEX('.', @verno, 0) + 1,
                           CHARINDEX('.', @verno, CHARINDEX('.', @verno, 0))
                           - 1)
    SET @rev = SUBSTRING(@verno,
                         CHARINDEX('.', @verno, CHARINDEX('.', @verno, 0) + 1)
                         + 1, LEN(@verno))

--- this code tests the value of the individual variables to confirm they were extracted properly
--select convert(varchar(5),@main) + '.' + convert(varchar(5),@minor) + '.'+ convert(varchar(5),@rev) as previous
-- Now the fun starts. Incrementing the version number.
-- since I was using 3 character numbers checks had to be made to increment accordingly.
-- you can note the checks for a 999 value and the increment of the higher level number if the lower rolled over.
-- this way we could start at 000.000.000 and the system will continue to operate automatically up to version number 999.999.999
    BEGIN
        SET @rev = CASE WHEN @rev < 9999 THEN @rev + 1
                        ELSE 0
                   END
        SET @minor = CASE WHEN @rev = 0
                               AND @minor < 999 THEN @minor + 1
                          WHEN @rev = 0
                               AND @minor = 999 THEN 0
                          ELSE @minor
                     END
        SET @main = CASE WHEN @minor = 0
                              AND @rev = 0 THEN @main + 1
                         ELSE @main
                    END
    END
-- Now we combine the various parts together into a single variable to update the extended property
    SET @verno = RIGHT('000' + CONVERT(VARCHAR(5), @main), 3) + '.'
        + RIGHT('000' + CONVERT(VARCHAR(5), @minor), 3) + '.' + RIGHT('0000'
                                                              + CONVERT(VARCHAR(5), @rev),
                                                              4)
-- perform the actual update of the extended property
    EXEC sp_updateextendedproperty @name = N'DBVersion', @value = @verno;
GO

----- confirm new number version number set by querying the sys.extended_properties table
	--SELECT value
	--FROM sys.extended_properties
	--WHERE class = 0
	--	AND NAME = 'DBVersion'
        --GO