从多个表中删除记录

本文关键字：删除 记录 | 更新日期: 2023-09-27 18:05:40

在我的Web Api应用程序中，我有一些像这样的表，当创建process并上传文件时，向tableLogs添加了一些信息:

tableProcess
-------------------
ProcessId    Name
1            proc1
2            proc2
tableFile
------------------------------
FileId    ProcessId    Name
1             1        file1
2             1        file2
3             1        file3
4             2        file4
5             2        file5
tableLogs
------------------
LogId    FileId    Description
1          1          log1
2          1          log2
3          2          log3
4          2          log4
5          3          log5
6          4          log6
7          5          log7

我需要在删除process时从所有这些表中删除记录，例如，如果我想删除proc1，文件file1, file2， &应该从tableFile中删除file3，并且应该从tableLogs中删除与这三个文件匹配的前五个日志。我没能删除与tableFile相关的日志。有什么建议吗?

我创建了一个存储过程，正如@websch01ar在他的回答中建议的那样，现在我的方法看起来像这样:

public IHttpActionResult DeleteLeadProcess(int id)
        {
            var procId = db.tableProcess.SingleOrDefault(p => p.ProcessId == id);
            string errMsg = null;
            using (var transaction = db.Database.BeginTransaction())
            {
                try
                {
                    db.deleteProcess(id);
                    transaction.Commit();  <-- error here
                }
                catch (Exception e)
                {
                    transaction.Rollback();
                    errMsg = e.Message;
                    return BadRequest(errMsg);
                }
            }
            return Ok("Process " + procId.ProcessName + " has been deleted successfully!");
}

如果我在SQL Server中运行它，则该过程工作正常，但在控制器中，我有一个错误"A System.Data.SqlClient.SqlException被抛出:"事务操作无法执行，因为有待处理的请求在此事务上工作。"

从多个表中删除记录

我的建议是，当您需要处理级联删除时，切换到执行存储过程。下面是一个您可以使用的人造存储过程。请注意，我正在使用我自己的sproc模板，您可能不需要所有这些

USE [Database]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[csp_Application_DeleteProcessRecords]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[csp_Application_DeleteProcessRecords]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[csp_Application_DeleteProcessRecords]
    @ProcessID int
AS
BEGIN
    /******************************************************************************
    **  File: csp_Application_DeleteProcessRecords.sql
    **  Name: csp_Application_DeleteProcessRecords
    **  Desc: 
    *******************************************************************************
    **  Change History
    *******************************************************************************
    **  Date:       Author:             Description:
    **  --------    --------            -------------------------------------------
    **  07-31-00    Cool Developer      Creation.
    **
    *******************************************************************************/
    BEGIN TRY
        /*
            Delete from tableLogs
        */
            Delete TL
            from [Database].dbo.tableLogs TL
            JOIN [Database].dbo.tableFile TF on TL.FileID = TF.FileID
            Where TF.ProcessID = @ProcessID;
        /*
            Delete from tableFile
        */
            Delete TF
            from [Database].dbo.tableFile TF
            Where TF.ProcessID = @ProcessID;
        /*
            Delete from Process
        */
            Delete TP
            from [Database].dbo.tableProcess TP 
            where ProcessID = @ProcessID;
            Select 1; -- true as an output?
    END TRY
    BEGIN CATCH
        BEGIN
            Rollback;
            DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
            SELECT 
                @ErrorMessage = ERROR_MESSAGE(),
                @ErrorSeverity = ERROR_SEVERITY(),
                @ErrorState = ERROR_STATE();
            RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
        END
    END CATCH;
END
Go
/*
**  Test Scripts
*/
/*
Declare @ProcessID int = (Select 'A')
exec [Database].dbo.csp_Application_DeleteProcessRecords @ProcessID 1
*/
相关文章：