how to delete a record if i can delete other first?

Question

I have the following:

A file can contain another files (a video file contains audios an subtitles) and in general the content files are not saved alone, only appears as part of the main file. However in some specials cases I have some content files saved apart.

Also I have Stores, so one file are in a store if is main file, and if I have a secondary file saved apart of the main file too.

So I have this two tables:

• Files(IDfile,...);
• FilesContainsFiles(IDContainerFile, IDContentFile);
• Stores(IDStore, ...)
• StoresHaveFiles(IDStore, IDFile)

If I want to delete a main file, I want to delete only from an specific sotre, so if the file is in other stores, I don't want to delete the file. If the file is not in another stores, I would like to delete the file because it ahs not sense to have this information in the database.

Also, I want to delete all the content files, only if the content file is not stored in another store alone, outside the main file.

To ensure that I can't delete a file that is in anothers stores, I don't delete on cascade the relation on StoresHasFiles, so in this way I get an foreigner key error.

So I was thinking to follow this steps:

• Try to delete the main file. If I can do it, try to delete the content files.

I guess that I need a transaction to do that, but I don't know how to do that.

I don't know if I can declare a list with the files that I am trying to delete, how to iterate this list and how to try to delete the content files, and if some content file can't be deleted because is stored in another place, try to delete the next content file.

Perhaps are there another options to solve this case?

Thanks.

Answer 1

I would handle this with a series of checks.

If the file exists at the specified store.. else do nothing. If the file exists at some other store... else just delete from the one store.

Ex:

IF OBJECT_ID('TEMPDB..#Files') IS NOT NULL DROP TABLE #Files
IF OBJECT_ID('TEMPDB..#FilesContainsFiles') IS NOT NULL DROP TABLE #FilesContainsFiles
IF OBJECT_ID('TEMPDB..#Stores') IS NOT NULL DROP TABLE #Stores
IF OBJECT_ID('TEMPDB..#StoresHaveFiles') IS NOT NULL DROP TABLE #StoresHaveFiles

CREATE TABLE #Files ([IDFile] INT IDENTITY(1,1), [FileName] VARCHAR(100))
CREATE TABLE #FilesContainsFiles ([IDContainerFile] INT, [IDContentFile] INT)
CREATE TABLE #Stores ([IDStore] INT IDENTITY(1,1), [StoreName] VARCHAR(100))
CREATE TABLE #StoresHaveFiles ([IDStore] INT, [IDFile] INT)

INSERT INTO #Stores (StoreName) VALUES ('Target'),('Walmart')
INSERT INTO #Files ([FileName]) VALUES ('true.blood.mkv'), ('game.of.thrones.mkv'), ('game.of.thrones-swe.sub')
INSERT INTO #FilesContainsFiles (IDContainerFile, IDContentFile) VALUES (2,3)
INSERT INTO #StoresHaveFiles (IDStore, IDFile) VALUES (1,1),(1,2),(2,2)

DECLARE @StoreToDeleteFrom VARCHAR(100), @FileToDelete VARCHAR(100)
--SET @StoreToDeleteFrom='Target'
SET @StoreToDeleteFrom='Walmart'
--SET @FileToDelete='true.blood.mkv'
SET @FileToDelete='game.of.thrones.mkv'

SELECT *
FROM #Files F 
LEFT OUTER JOIN #FilesContainsFiles FParent ON F.IDFile=FParent.IDContainerFile
LEFT OUTER JOIN #StoresHaveFiles SHF ON F.IDFile=SHF.IDFile
LEFT OUTER JOIN #Stores S ON SHF.IDStore=S.IDStore

-- IF THE FILE IS AT OUR STORE...
IF EXISTS (SELECT *
            FROM #Files F 
            LEFT OUTER JOIN #StoresHaveFiles SHF ON F.IDFile=SHF.IDFile
            LEFT OUTER JOIN #Stores S ON SHF.IDStore=S.IDStore
            WHERE S.StoreName=@StoreToDeleteFrom
            AND F.[FileName]=@FileToDelete)
BEGIN
    -- IF THE FILE IS NOT AT ANY OTHER STORE
    IF NOT EXISTS (SELECT *
                    FROM #Files F 
                    LEFT OUTER JOIN #StoresHaveFiles SHF ON F.IDFile=SHF.IDFile
                    LEFT OUTER JOIN #Stores S ON SHF.IDStore=S.IDStore
                    WHERE S.StoreName<>@StoreToDeleteFrom
                    AND F.[FileName]=@FileToDelete)
    BEGIN
        -- GO AHEAD AND DELETE FILE EVERYWHERE...
        PRINT 'DELETE FILE'

        DELETE SHF
        FROM #Stores S 
        JOIN #StoresHaveFiles SHF
           ON S.IDStore=SHF.IDStore
           JOIN #Files F 
              ON SHF.IDFile=F.IDFile
              AND F.[FileName]=@FileToDelete
        WHERE S.StoreName=@StoreToDeleteFrom

        DELETE FCF
        FROM #Files F 
        JOIN #FilesContainsFiles FCF
           ON F.IDFile=FCF.IDContentFile
        WHERE F.[FileName]=@FileToDelete

        DELETE FCF
        FROM #Files F 
        JOIN #FilesContainsFiles FCF
           ON F.IDFile=FCF.IDContainerFile
        WHERE F.[FileName]=@FileToDelete

        DELETE F 
        FROM #Files F
        WHERE [FileName]=@FileToDelete
    END
    ELSE
    BEGIN
        -- ONLY DELETE FROM STORE
        PRINT 'DELETE FILE FROM STORE ONLY'

        DELETE SHF
        FROM #Stores S 
        JOIN #StoresHaveFiles SHF
           ON S.IDStore=SHF.IDStore
           JOIN #Files F 
              ON SHF.IDFile=F.IDFile
              AND F.[FileName]=@FileToDelete
        WHERE S.StoreName=@StoreToDeleteFrom
    END
END
ELSE
BEGIN
    -- ITS NOT AT THAT STORE...
    PRINT 'NOTHING TO DELETE'
END


SELECT *
FROM #Files F 
LEFT OUTER JOIN #FilesContainsFiles FParent ON F.IDFile=FParent.IDContainerFile
LEFT OUTER JOIN #StoresHaveFiles SHF ON F.IDFile=SHF.IDFile
LEFT OUTER JOIN #Stores S ON SHF.IDStore=S.IDStore

Answer 2

There are a few different ways you can do this. Regarding transactions, easiest way to learn about syntax and usage is to read on MSDN about Begin Transaction.

For the delete, the way I would do it is:

    -- Assumed inputs
    DECLARE @StoreId int;
    DECLARE @FileIdToDelete int;

    -- Query
    SET XACT_ABORT ON;  -- Forces whole transaction to roll back if 
                        --  there was an error at any point

    BEGIN TRAN;

    DECLARE @NumberOfStoresContainingFile int;

    -- Find out how many stores have this file
    SELECT  @NumberOfStoresContainingFile = COUNT(*)
    FROM    StoresHaveFiles shf
    WHERE   IDFile = @FileIdToDelete

    -- If it is more than one we know we dont want to delete so signal caller
    IF (@NumberOfStoresContainingFile > 1)
        RAISERROR(<addYourDetailsHere>);

    -- Remove any contained files of the main file we want to remove
    DELETE  f
    FROM    Files f
    WHERE   EXISTS (SELECT * 
                    FROM FilesContainsFiles fcf 
                    WHERE fcf.IDContainerFile = @FileIdToDelete AND fcf.IDContentFile = f.IDFile);

    -- Remove the main file entry itself
    DELETE
    FROM    Files
    WHERE   IDFile = @FileIdToDelete;

    -- Remove the JOIN table record between store and file
    DELETE  StoresHaveFiles
    WHERE   IDStore = @StoreId
        AND IDFile = @FileIdToDelete;

    -- This is an optimistic approach (it assumes the file belongs to the store you asked about
    --  if the file is only in one store).  This check is to ensure that the JOIN record was
    --  deleted.  If it is not, this means the file did not belong to the store and an error
    --  should be thrown.  To do this pessimistically, simply do this check before the deletes
    --  and after the count check.
    IF (@@rowcount = 0)
        RAISERROR(<addYourDetailsHere>);

    COMMIT;