Version Controlling of Database Object

Introduction

I got a lot of publish related to source code version controlling. But not get any database object version controlling posts.

To understand what i mean to say please read this case study.

Case study

Take a scenario that we have 10 clients and we maintain the same database objects for all of our clients. We need some version controlling for our client database objects. Only the higher version script of DB objects can be compiled and create object in the database not the lower version.

What to do

Here we are using SQL server extended property to solve this problem. In our case we are taking Trigger as a database object.

Creating version control SP

-- [ Version Control Procedure ]

IF OBJECT_ID('UP_VERSIONCONTROL') IS NOT NULL

   BEGIN

     DROP PROCEDURE UP_VERSIONCONTROL;

   END

GO  

CREATE PROCEDURE UP_VERSIONCONTROL

  (

    @param_Type       VARCHAR(1)   = NULL, -- A-Addition D-Deletin U-Updation

    @param_TblObject  VARCHAR(MAX) = NULL,

    @param_TrgObject  VARCHAR(MAX) = NULL,

    @param_Version    VARCHAR(50)  = NULL

   )

AS

DECLARE @v_VersionDescr sql_variant;

BEGIN

   SET @v_VersionDescr = @param_Version;

  

   IF ISNULL(@param_Type,'')= 'A'

      BEGIN 

                 EXEC sys.sp_addextendedproperty

                           @name = N'MS_DescriptionExample',

                           @value = @v_VersionDescr,

                           @level0type = N'SCHEMA', @level0name = 'dbo',

                           @level1type = N'TABLE',  @level1name = @param_TblObject,

                           @level2type = N'TRIGGER',@level2name = @param_TrgObject;

         END               

   ELSE IF ISNULL(@param_Type,'')= 'D'  

           BEGIN

              EXEC sp_dropextendedproperty

                   @name = N'MS_DescriptionExample',

                   @level0type = N'SCHEMA', @level0name = 'dbo',

                   @level1type = N'TABLE',  @level1name = @param_TblObject,

                              @level2type = N'TRIGGER',@level2name = @param_TrgObject;

           END

        ELSE IF ISNULL(@param_Type,'')= 'U'

                BEGIN

                   EXEC sp_dropextendedproperty

                                         @name = N'MS_DescriptionExample',

                                         @level0type = N'SCHEMA', @level0name = 'dbo',

                                         @level1type = N'TABLE',  @level1name = @param_TblObject,

                                         @level2type = N'TRIGGER',@level2name = @param_TrgObject;

                                        

                              EXEC sys.sp_addextendedproperty

                                         @name = N'MS_DescriptionExample',

                                         @value = @v_VersionDescr,

                                         @level0type = N'SCHEMA', @level0name = 'dbo',

                                         @level1type = N'TABLE',  @level1name = @param_TblObject,

                                         @level2type = N'TRIGGER',@level2name = @param_TrgObject;            

                END 

             ELSE IF ISNULL(@param_Type,'')= 'R'

                     BEGIN

                         SELECT * FROM fn_listextendedproperty('MS_DescriptionExample',

                                                               'SCHEMA',

                                                               'dbo',

                                                               'TABLE',

                                                               @param_TblObject,

                                                               'TRIGGER',

                                                               @param_TrgObject);

                                                               

                     END

END  

Create the Base Table

IF OBJECT_ID('TBL_TESTING') IS NOT NULL

   BEGIN

     DROP TABLE TBL_TESTING;

   END

GO  

CREATE TABLE TBL_TESTING

       ( ID     INT           IDENTITY PRIMARY KEY NOT NULL,

         STNAME VARCHAR(50)   NOT NULL,

         MARKS  DECIMAL(20,0) NULL);

GO

INSERT INTO TBL_TESTING

            (STNAME)

VALUES ('JOYDEEP'),('RAJESH'),('RAHIM');           

GO

Creating Trigger Object

-- [ Creating Trigger Objects ]

DECLARE @VERSION_NO DECIMAL(20);

SET @VERSION_NO = 200;

IF OBJECT_ID('TRG_TESTING') IS NOT NULL

   BEGIN

     IF OBJECT_ID('TMP_VERSIONCTRL') IS NOT NULL

        BEGIN

           DROP TABLE TMP_VERSIONCTRL;

        END

     CREATE TABLE TMP_VERSIONCTRL

          (TYPEOFOBJECT    sql_variant,

           NAMEOFOBJECT    sql_variant,

           OBJECTNAME         sql_variant,

           VERSIONNO       sql_variant);

          

     INSERT INTO TMP_VERSIONCTRL

     EXEC UP_VERSIONCONTROL

                @param_Type       = 'R',

                @param_TblObject  = 'TBL_TESTING',

                @param_TrgObject  = 'TRG_TESTING'

               

    

     IF NOT EXISTS(SELECT * FROM TMP_VERSIONCTRL)

        BEGIN

           DROP TRIGGER TRG_TESTING;

        END

     ELSE

        BEGIN 

           IF (SELECT CONVERT(DECIMAL, VERSIONNO) FROM TMP_VERSIONCTRL) < @VERSION_NO

              BEGIN

                DROP TRIGGER TRG_TESTING

              END

           ELSE

              BEGIN

                PRINT 'Can NOT detete Trigger as Version control restricts for Lower version Install'

              END  

        END   

   END

GO 

CREATE TRIGGER TRG_TESTING

   ON  TBL_TESTING

   AFTER INSERT

AS

BEGIN

  IF EXISTS(SELECT * FROM inserted)

     BEGIN

                UPDATE TRG_TESTING SET MARKS = 50

                WHERE  ID=(SELECT ID FROM inserted);

     END

END

GO

-- Contain When the Trigger run first time

EXEC UP_VERSIONCONTROL

     @param_Type       = 'R',

     @param_TblObject  = 'TBL_TESTING',

     @param_TrgObject  = 'TRG_TESTING',

     @param_Version    = '100'

    

GO

To understand the above T-SQL please read it carefully. It's self explanatory.

Hope you like it.

Posted by: MR. JOYDEEP DAS