prevent soft delete if row is referenced in another table

Question

I have two tables, tableA and tableB. They are connected by a foreign key on their IDs (ATableID, BTableID). TableB has a stored procedure that allows rows to be archived, It has 2 columns in it named Available_Days and Available_Night. In TableA There is also columns Available_Days and Available_Night, which are joined to tableB by a left join. If there is data in those columns in TableA, you must not be able to archive the row in TableB.

This is the stored procedure currently, I need help implementing the criteria explained above.

@BTableID VARCHAR(500) = '',
@UserArchived INT = 0

AS

DECLARE @Local BTableID VARCHAR(500)
DECLARE @LocalUserArchived INT
DECLARE @LocalSql NVARCHAR(500)

set @Local BTableID = @BTableID;
set @LocalUserArchived = @UserArchived;


set @LocalSql = 'UPDATE tableB
SET DateArchived = GETDATE(),
UserArchived = '+CAST(@UserArchived as NVARCHAR)+'
WHERE BTableID  IN ('+@ BTableID +')'

I am also open for any suggestions if the current direction isn't wise. For full context, this stored procedure will be used in php Laravel framework where a user will be able to delete a row(TableB), unless that rows information is referenced elsewhere(TableA), in which case, they will be prompted to update the other table before attempting to delete again.

Once again, the desired result is to prevent archiving/soft deleting (preventing setting DateArchived = GETDATE()) a row if its information is referenced in another table.

UPDATE: I've made two possible adjustments (currently unable to test them as I'm not able to access the db at the moment.)

set @LocalSql = 'UPDATE BTable
SET DateArchived = GETDATE(),
UserArchived = '+CAST(@UserArchived as NVARCHAR)+'
WHERE BTableID IN ('+@BTableID+')
WHERE NOT EXISTS (
SELECT FROM ATable at
WHERE tp.ATable_FK = tp.BTableID)'

and

set @LocalSql = 'UPDATE tableB
SET DateArchived = GETDATE(),
UserArchived = '+CAST(@UserArchived as NVARCHAR)+'
WHERE tableBID IN ('+@tableBID+')
LEFT JOIN tableA cm ON cm.tableAID = tp.tableBID
WHERE cm.tableAID IS NULL’

I'm not able to test them at this time but will update this post to let you know if either did the job. If you have any suggestions on improvements please leave a comment or answer. :)

Answer 1

SOLUTION: I figured out a very simple solution to solve my issue. I forgot all about the archive/soft delete stored procedures and created two stored procedures for table A and table B which selects one record from the table. ie :

@TableAIB int
    
    AS 
    
    SELECT
    TableBID,
    TableBDescription,
    FROM TableB
    WHERE TableBID = @TableBID
    
    RETURN @@ERRO

and

GO


@TableAID int

AS 

SELECT
TableAID,
TabelADescritption
FROM TableA
WHERE TableAID = @TableAID

RETURN @@ERROR

GO

these two two tables are connected via a foreign key(on tableAID and tableBID).

in the controller I then did this:

public function archive(int $id)
{

    $tableA = tableA::find($id);
    $tableB = tableB::find($id);

    if ($tableA->TableAID == $tableB->TableBID){
        return redirect()->route(‘myPage.index')
            ->with('warning', ’This can not be archived, it is being used in another row. ');
    }
}

This is essentially saying that if the two stored procedure's ID's match, redirect to the index page with a warning.