Saturday, 9 August 2014

Cascading Delete of a Record in a table which have Primary key as combination of multiple column with references as Foreign key in various tables [Sql Server]

Cascade delete a record in Table means, when we delete a record in a table based on the Primary key , then the related foreign key tables should delete the related records from the table. why we need custom one there is a chance that we have to place our custom logic, Then the primary key column of a table not to be in single column, there is a chance that a Table can have more than one column as Primary key. now we see how to delete that kind of table in cascade. with specified values.

Below is the sample format we have to call a cascade delete 
First parameter refers to Table name, 
second parameter refers to the Key to delete from the table , here you can see it have multiple values with comma separated because the table have primary key in the combination of three columns, so we are passing the three values with comma separated.  

value 1 is refers to column RankId
value kumar is refers to column FirstName
value Ram is refers to column LastName


BEGIN TRAN

SELECT * FROM PERSONS
SELECT * FROM SCHOOL
SELECT * FROM CLASS

    EXEC CASCADEDELETE_SP 'Persons','1,''kumar'',''Ram'''


SELECT * FROM PERSONS
SELECT * FROM SCHOOL
SELECT * FROM CLASS



ROLLBACK

Before Execution



After Execution


You can see a record in the id 1 is delete.

Table Structure
Table with primary key in the combination of three column


CREATE TABLE PERSONS
(
    ID INT IDENTITY (1,1),
    Rankid INT ,
    LASTNAME VARCHAR(255) NOT NULL,
    FIRSTNAME VARCHAR(255) not null,
    ADDRESS VARCHAR(255),
    CITY VARCHAR(255),
    CONSTRAINT PK_PERSONID PRIMARY KEY (rankid,firstname,LASTNAME)
)


Table with one primary key column and reference of foreign key

CREATE TABLE SCHOOL
(
      ID INT IDENTITY(1,1)PRIMARY KEY,
      rankid INT ,
      PFIRSTNAME VARCHAR(255),
      PLASTNAME VARCHAR(255),
      NAME VARCHAR(1000),
      FOREIGN KEY (rankid,pfirstname,PLASTNAME) 
      REFERENCES PERSONS(Rankid,firstname,LASTNAME)
)

CREATE TABLE CLASS
(
      ID INT IDENTITY(1,1),
      SCHOOLID INT REFERENCES SCHOOL(ID),
      CLASSNAME VARCHAR(1000)
)



insert into PERSONS(Rankid,LASTNAME,FIRSTNAME,ADDRESS,CITY)
select 1,'Ram','kumar','CRM nagar','Chennai'
union
select 1,'Siva','kumar','SRM nagar','Chennai'
union
select 1,'Ram','Pandian','CRM nagar','Chennai'
union
select 2,'Ram','Jeeva','T nagar','Chennai'
union
select 2,'Samy','Joseph','R.E Nagar','Chennai'

insert into SCHOOL(rankid,PFIRSTNAME,PLASTNAME,NAME)
select 1,'kumar','Ram','st. jos'
union
select 1,'kumar','Ram','st. mar'
union
select 1,'Pandian','Ram','st. sdf'


insert into class(schoolid,classname)
select 1,'1st'
union
select 1, '2nd'
union
select 2,'1st'
union
select 3,'3rd'


From the above you can see that three tables are there, now we start to create a Three Functions and Two Sp's

Functions:
1. Get the full name of Database with schema [FrameQualName]
2. Get the Join condition based on Keys  [FrameJoinClause]
3. Get the Where Clause for multiple column Primary key  [FRAMEWHERECLAUSE]

Stored Procedures:
1. Get the Query based on the input and some debug information  [DeleteParentChildData]
2. Execute the Dynamic Query  [CASCADEDELETE_SP]

/* To get the Full Qualified name for the Table*/
    IF EXISTS (SELECT '1' FROM SYS.OBJECTS where NAME = 'FrameQualName'
                  and TYPE = 'FN')
    BEGIN
            DROP FUNCTION FrameQualName
    END
    GO
     
      CREATE FUNCTION FrameQualName(@ObjectId INT)
      RETURNS VARCHAR (300)
      AS
      BEGIN
           
            DECLARE @schema_id AS BIGINT;
           
            SELECT      @schema_id = schema_id
            FROM  sys.tables
            WHERE object_id = @ObjectId;
           
    RETURN  '[' + SCHEMA_NAME(@schema_id) + '].[' + OBJECT_NAME(@ObjectId) + ']';
      END

      GO
   






    /* dbo.FrameJoinClause to frame the join condition */  
      IF EXISTS (SELECT '1' FROM SYS.OBJECTS where NAME = 'FrameJoinClause' and                    TYPE = 'FN')
    BEGIN
            DROP FUNCTION FrameJoinClause
    END
    GO
     
     
      CREATE FUNCTION dbo.FrameJoinClause(@fkNameId INT)
      RETURNS VARCHAR (max)
      AS
      BEGIN

            DECLARE @ONCLAUSE AS VARCHAR (1000);
            DECLARE @RESULTCLAUSE AS VARCHAR (1000);       
           
            SET @ONCLAUSE = '[<@parTab>].[<@parCol>] = [<@childTab>].                                           [<@childCol>] AND ';      
            SET @RESULTCLAUSE = '';
           
            SELECT @RESULTCLAUSE = @RESULTCLAUSE + REPLACE(REPLACE(REPLACE(
                   REPLACE(@ONCLAUSE, '<@parTab>', OBJECT_NAME(rkeyid)),                                    '<@parCol>', COL_NAME(rkeyid, rkey)), 
                          '<@childTab>', OBJECT_NAME(fkeyid)), 
                          '<@childCol>', COL_NAME(fkeyid, fkey))
            FROM   dbo.sysforeignkeys AS fk
            WHERE  fk.constid = @fkNameId;  
           
            RETURN LEFT(@RESULTCLAUSE, LEN(@RESULTCLAUSE) - LEN(' AND '));
                       
      END

      GO






/* dbo.FRAMEWHERECLAUSE to frame the where condition */    
   IF EXISTS (SELECT '1' FROM SYS.OBJECTS where NAME = 'FRAMEWHERECLAUSE' and                   TYPE = 'FN')
    BEGIN
            DROP FUNCTION FRAMEWHERECLAUSE
    END
    GO
     

CREATE FUNCTION DBO.FRAMEWHERECLAUSE(@TABLENAME VARCHAR(100),@INPUT VARCHAR(1000))
RETURNS VARCHAR(MAX)
AS
BEGIN


      DECLARE @CRLF CHAR(2)
      DECLARE @QUERY VARCHAR(MAX) = ''
      DECLARE @TEMPTABLE TABLE(ID INT IDENTITY(1,1),VALUE VARCHAR(100))
      DECLARE @KEYCOLTABLE TABLE(ID INT IDENTITY(1,1),VALUE VARCHAR(100))
      DECLARE @MIN INT = 1
      DECLARE @MAX INT = -1
      DECLARE @COLUMNNAME VARCHAR(200)
      DECLARE @VALUE VARCHAR(400)

      SET @CRLF = CHAR(13) + CHAR(10);

      INSERT INTO @TEMPTABLE
      SELECT TEXT FROM SPLIT(@INPUT,',')

      INSERT INTO @KEYCOLTABLE
      SELECT      COLUMN_NAME
      FROM  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
      WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
      AND         TABLE_NAME = @TABLENAME
      ORDER BY ORDINAL_POSITION

      SELECT @MAX = COUNT(*) FROM @KEYCOLTABLE

      WHILE(@MIN<=@MAX)
      BEGIN

            SELECT @COLUMNNAME = VALUE FROM @KEYCOLTABLE WHERE ID = @MIN
            SELECT @VALUE = VALUE FROM @TEMPTABLE WHERE ID = @MIN

            SELECT @QUERY = @QUERY + @TABLENAME +'.'+ @COLUMNNAME + '='+@VALUE +                              @CRLF + ' AND '
           
            SELECT @MIN = @MIN + 1

      END

      RETURN  LEFT(@QUERY, LEN(@QUERY) - LEN(' AND '));

END







/* Cascade Delete  */
-- exec DeleteParentChildData @ParentTableId='Template',@WhereKeyValues='1',@Debug='N'

IF EXISTS (SELECT '1' FROM SYS.OBJECTS where NAME = 'DeleteParentChildData' and TYPE = 'P')
BEGIN
DROP PROCEDURE DeleteParentChildData
END
GO

CREATE PROCEDURE dbo.DeleteParentChildData
(
@ParentTableId       VARCHAR (300),
@WhereKeyValues      VARCHAR (3000),
@ExecuteDelete       CHAR   (1)           =      'N',
@FromClause          VARCHAR (8000)       =      '',
@Debug               CHAR   (1)           =      'Y',
@Generation                INT                        =      1
)
AS
BEGIN

SET NOCOUNT ON;

/* SET UP DEBUG */
DECLARE @DebugMsg          AS VARCHAR (4000);
DECLARE @DebugIndent AS VARCHAR (50);
DECLARE @CRLF              AS CHAR (2);
DECLARE @strSQL                   AS VARCHAR (4000);

SET           @DebugIndent  =      REPLICATE('***', @@NESTLEVEL)+'    ';

IF ISNUMERIC(@ParentTableId) = 0

BEGIN
      
       IF CHARINDEX('.', @ParentTableId) = 0
              SET @ParentTableId = OBJECT_ID('[dbo].[' + @ParentTableId + ']');
       ELSE
              SET @ParentTableId = OBJECT_ID(@ParentTableId);
END

IF @Generation = 1
BEGIN
       IF @Debug = 'Y'
       BEGIN               
                                                                          
         PRINT +'/*****************************************************/';                                                          
       END
END


SET @CRLF = CHAR(13) + CHAR(10);

IF @Generation = 1
SET @strSQL = 'SET NOCOUNT ON' + @CRLF;
ELSE
SET @strSQL = '';

IF @Debug = 'Y'
SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + dbo.FrameQualName(@ParentTableId) + '                              Level=' +cast(@Generation as varchar(10))+'''' 

IF @ExecuteDelete = 'Y'
EXECUTE (@strSQL);
ELSE
IF @strSQL <> ''
SELECT @strSQL;

DECLARE cursor_child CURSOR LOCAL FORWARD_ONLY
FOR           SELECT  DISTINCT
                     constid AS fkNameId,
                     fkeyid AS cTableId
       FROM   dbo.sysforeignkeys AS fk
       WHERE  fk.rkeyid <> fk.fkeyid                  
       AND       fk.rkeyid = @ParentTableId;
                
OPEN cursor_child;

DECLARE @fkNameId    AS INT,
       @cTableId     AS INT,
       @cColId              AS INT,
       @pTableId     AS INT,
       @pColId              AS INT;
      
FETCH NEXT
FROM cursor_child
INTO @fkNameId, @cTableId;

DECLARE @strFromClause AS VARCHAR (1000);
DECLARE @nLevel AS INT;

       IF @Generation = 1
       BEGIN
              SET @FromClause = 'FROM ' + dbo.FrameQualName(@ParentTableId)+' (NOLOCK)';                     
              SELECT @WhereKeyValues =  dbo.FRAMEWHERECLAUSE(object_name(@ParentTableId),@WhereKeyValues)                          
       END
      
       WHILE @@FETCH_STATUS = 0
       BEGIN
      
              SELECT @strFromClause = @FromClause + @CRLF +'      INNER JOIN ' +                                                    dbo.FrameQualName(@cTableId)+' (NOLOCK)' + @CRLF +
                                      '      ON ' + dbo.FrameJoinClause(@fkNameId);
             
              SET @nLevel = @Generation + 1;
             
              EXECUTE dbo.DeleteParentChildData @ParentTableId = @cTableId, 
                      @WhereKeyValues = @WhereKeyValues, @ExecuteDelete = @ExecuteDelete, 
                      @FromClause = @strFromClause,@Debug=@Debug, @Generation = @nLevel;
             
              SET @strSQL = @CRLF+@CRLF+'DELETE FROM ' + dbo.FrameQualName(@cTableId) + 
                            @CRLF+ @strFromClause + @CRLF + 'WHERE   ' 
                            + @WhereKeyValues + @CRLF;
             
              IF @Debug = 'Y'
              SET @strSQL = @strSQL + 'PRINT ''***' + @DebugIndent + 'DELETE FROM ' +                                    dbo.FrameQualName(@cTableId) + '     Rows Deleted: '' +                                        CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF + @CRLF;
             
              IF @ExecuteDelete = 'Y'
                     EXECUTE (@strSQL);
              ELSE
               IF @strSQL <> ''
                     SELECT @strSQL;

              FETCH NEXT FROM cursor_child INTO @fkNameId, @cTableId;             

END

       IF @Generation = 1
       BEGIN
             
              IF @Debug = 'Y'
                     SET @strSQL = @CRLF + 'PRINT ''' + @DebugIndent +                                                           dbo.FrameQualName(@ParentTableId) + ' Level=' + 
                                   CAST (@Generation AS VARCHAR) + 
                                   ' TOP LEVEL PARENT TABLE''' + @CRLF;
              ELSE
                     SET @strSQL = @CRLF
             
              SET @strSQL = @strSQL +@CRLF + 'DELETE FROM ' +                                                            dbo.FrameQualName(@ParentTableId) + ' WHERE ' + 
                            @WhereKeyValues + @CRLF;
             
              IF @Debug = 'Y'
              SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + 'DELETE FROM ' +                                        dbo.FrameQualName(@ParentTableId) + 
                            ' Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF;
             
              IF @ExecuteDelete = 'Y'
                     EXECUTE (@strSQL+@CRLF+ 'SET NOCOUNT OFF' + @CRLF);
              ELSE
                     SELECT @strSQL+@CRLF+ 'SET NOCOUNT OFF' + @CRLF;
       END

CLOSE cursor_child;
DEALLOCATE cursor_child;

END




/* Delete the Record in Cascade */
IF EXISTS (SELECT '1' FROM SYS.OBJECTS where NAME = 'CASCADEDELETE_SP' and TYPE = 'P')
BEGIN
       DROP PROCEDURE CASCADEDELETE_SP
END
GO
   
   
CREATE PROCEDURE CASCADEDELETE_SP(@tablename varchar(100),@keycolumnvalues varchar(1000))
AS
BEGIN

DECLARE @CASCADTABLE TABLE (ID INT IDENTITY(1,1),QUERY VARCHAR(MAX))
DECLARE @MIN INT = 1
DECLARE @MAX INT = -1
DECLARE @RESULT VARCHAR(MAX)

       INSERT INTO @CASCADTABLE
       EXEC DeleteParentChildData @ParentTableId=@tablename,
                                  @WhereKeyValues=@keycolumnvalues,@DEBUG='Y'

       SELECT @MAX = COUNT('1') FROM @CASCADTABLE

       WHILE(@MIN <= @MAX)
       BEGIN
             
             
              SELECT @RESULT = QUERY FROM @CASCADTABLE WHERE ID = @MIN            
              EXEC(@RESULT)
             
              SELECT @MIN = @MIN + 1
       END

END




From this you can find what is cascade delete , and how to create a custom cascade delete for a table which have a multiple column as primary key.