?
Solved

Check child elements before deleting (recursive check?)

Posted on 2009-04-19
4
Medium Priority
?
281 Views
Last Modified: 2012-06-22
------------------
tblContent
------------------
Id
ParentId
TemplateId
Name
Text

When deleting a template (from tblTemplate table) I want to delete all content from tblContent based on this template, like this:

Delete tblContent where TemplateId = @TemplateId;
Delete tblTemplate where id = @TemplateId;

The big problem is that I dont want to delete anything IF a content element contains other child contents that is based on other Templates (different TemplateId's).


Example:

Contenttree
ID1
|
-Id2
   |
   Id4
     |
     Id8
   Id5
     |
     Id6
     Id7
-Id3
   |
   Id10
-Id9

If all content in my contenttree is based on template1 exept Id6 who is based on template 2 it should not be possible to delete template1. This is because content with tamplate 1 contains other content based on other tamplates (ant this content need to remaint their parent content).

How do I write the SQL to check if content based on one spesific template dont contain other content based on another template? The content tree has many levels.
0
Comment
Question by:webressurs
  • 2
  • 2
4 Comments
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 24178391
This code will check if any content with that TemplateId has children with a different TemplateID and not do the deletes if it finds any.

DECLARE @TemplateId INT, @ProblemContentExists BIT
SET @TemplateId = 1;
 
 
WITH tree AS
(
SELECT Id, TemplateId, [NAME]
FROM tblContent TC
WHERE TemplateId = @TemplateId
UNION ALL
SELECT TC2.Id, TC2.TemplateId, TC2.[NAME]
FROM tblContent TC2
INNER JOIN tree ON TC2.ParentId = tree.Id
)
SELECT @ProblemContentExists=CONVERT(BIT,COUNT(*)) FROM tree WHERE TemplateId <> @TemplateId
 
IF @ProblemContentExists = 0
BEGIN
	PRINT 'Deleting Records'
	Delete tblContent where TemplateId = @TemplateId;
	Delete tblTemplate where id = @TemplateId;
END
IF @ProblemContentExists = 1
BEGIN
	PRINT 'Can not delete because of Template Conflicts'
END

Open in new window

0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24178397
You can leave NAME out of the CTE query if you want, I had it in there while testing and forgot to remove it.
0
 
LVL 1

Author Closing Comment

by:webressurs
ID: 31571935
This was just what I was looking for. It worked perfect, thank you!!
0
 
LVL 1

Author Comment

by:webressurs
ID: 24180173
For those who may want the complete code, here is the procedure after some tiny adjustments:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Description:      Delete template, templatedefinitions and content based on template
-- =============================================

ALTER PROCEDURE [dbo].[spDeleteTemplateWithDefinitionsAndContent]
(
      @TemplateId smallint,
      @Success bit OUTPUT
)
AS
BEGIN
      SET NOCOUNT ON;      
      DECLARE @ProblemContentExists BIT;

      WITH tree AS
      (
            SELECT Id, TemplateId
            FROM tblContent TC
            WHERE TemplateId = @TemplateId
            UNION ALL
            SELECT TC2.Id, TC2.TemplateId
            FROM tblContent TC2
            INNER JOIN tree ON TC2.ParentId = tree.Id
      )

      SELECT @ProblemContentExists=CONVERT(BIT,COUNT(*)) FROM tree WHERE TemplateId <> @TemplateId
      
      IF @ProblemContentExists = 0
      BEGIN
            --'Deleting Records'
            DELETE tblContentAttribute WHERE templateDefinitionId in (SELECT Id FROM tblTemplateDefinition WHERE TemplateID = @TemplateId);
            DELETE tblContent WHERE templateId = @TemplateId;      
            DELETE tblTemplateDefinition WHERE TemplateID = @TemplateId;
            DELETE tblTemplate WHERE Id = @TemplateId;            
            (select @Success = '1')
      END

      IF @ProblemContentExists = 1
      BEGIN
            --'Can not delete because of Template Conflicts'
            (select @Success = '0')
      END

END
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question