DELETE Procedure. How to do this?

Hello,

I created a delete procedure which is working but I still have a
problem.

When I delete a localized content from dbo.by27_ContentLocalized given
a ContentName and ContentCulture I want to check if this is the only
record in ContentLocalized for that ContentName.

If it is then I also want to delete the record in dbo.by27_Content
which has that ContentName.

How can I do this?

Thanks,
Miguel

Here is my DELETE procedure:

-- Define the procedure parameters
@.ContentCulture NVARCHAR(5),
@.ContentName NVARCHAR(100)

AS

-- Allows @.@.ROWCOUNT and the return of number of records when
ExecuteNonQuery is used
SET NOCOUNT OFF;

-- Declare and define ContentId
DECLARE @.ContentId UNIQUEIDENTIFIER;
SELECT @.ContentId = ContentId FROM dbo.by27_Content WHERE ContentName =
@.ContentName

-- Check if ContentId is Not Null
IF @.ContentId IS NOT NULL
BEGIN

-- Check if ContentId is Null
IF @.ContentCulture IS NULL
BEGIN

-- Delete all localized contents from dbo.by27_ContentLocalized
DELETE
FROM dbo.by27_ContentLocalized
WHERE ContentId = @.ContentId

-- Delete content from dbo.by27_Content
DELETE
FROM dbo.by27_Content
WHERE ContentName = @.ContentName;

END
ELSE

-- Delete localized content from dbo.by27_ContentLocalized
DELETE
FROM dbo.by27_ContentLocalized
WHERE (ContentID = @.ContentID AND ContentCulture = @.ContentCulture)

END

shapper:

When I delete a localized content from dbo.by27_ContentLocalized given
a ContentName and ContentCulture I want to check if this is the only
record in ContentLocalized for that ContentName.

Can you explain what you mean by "only record in ContentLocalized for that ContentName". Do you mean check if there is only 1 record and delete it? or something else?

|||

I mean that when a record in by27_ContentLocalized is deleted it checks if it is the only one which is related with its parent in by27_Content.

If it is then it deletes also its parent in by27_Content, see?

Thanks,

Miguel

|||

How about adding something like this to the end of the procedure...

IF NOT EXISTS(SELECT * FROM by27_ContentLocalized WHERE ContentId = @.ContentID)
BEGIN
DELETE FROM by27_Content WHERE ContentID = @.ContentID
END

I hope this helps,

Steve

|||You can create a DELETE trigger on dbo.by27_ContentLocalized table, which will check to see whether the deleted row is the only record in ContentLocalized for that ContentName. For example:

CREATE?TRIGGER trg_CheckDeleteCL ON dbo.by27_ContentLocalized AFTER DELETE
AS
SELECT 1 FROM dbo.by27_ContentLocalized b,deleted d
WHERE b.ContentName=d.ContentName
IF(@.@.ROWCOUNT=0)
BEGIN
DECLARE @.msg NVARCHAR(2000)
DELETE dbo.by27_Content FROM dbo.by27_Content c, deleted d
WHERE c.ContentName=d.ContentName
SELECT @.msg='The record with ContentName='''+ContentName+'''deleted from dbo.by27_Content'
FROM deleted
PRINT @.msg
END
go