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
You can see a record in the id 1 is delete.
Table Structure
Table with primary key in the combination of three column
Table with one primary key column and reference of foreign key
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]
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)
)
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'
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.