时间戳

首页 > 解决方案 > 如何在 .cfc 文件的一个 DELETE 语句中从多个表中删除？

sql-server - 如何在 .cfc 文件的一个 DELETE 语句中从多个表中删除？

问题描述

我有两张桌子。一个设备表和一个位置表。设备表中只能有一个设备，但位置表中可以有多个具有多个位置的设备。

如果用户删除设备，我还需要从位置表中删除具有相同 ID 的位置。

有没有办法从 cfc 页面中的 Delete 语句（使用 sql server）中从多个表中删除？

<cfif>当我进行初始删除时，我尝试编写一个，以检查是否有记录被删除，如果是，则运行下一个从表中删除设备的删除语句。

我也尝试过使用这种格式。

delete T1, T2
from T1 
inner join T2 on T1.device_id = T2.device_id
where T2.device_id = '111';

T1 = 设备表，T2 = 位置表

<cfif ARGUMENTS.submitButton eq 'btn_Delete'>
   <cfquery name="DeleteDevice" datasource="#session.dsn#" maxRows=1 >
        DELETE from #session.tq#device
        WHERE device_id = <cfqueryparam value="#formStruct.deviceId#" cfsqltype="CF_SQL_VARCHAR">
    </cfquery>
 </cfif>

<cfif DeleteDevice.RecordCount eq 1>
<cfquery name="DeleteLocation" datasource="#session.dsn#" maxRows=1 >
        DELETE from #session.tq#device_location_xref
        WHERE device_location_xref_recno=<cfqueryparam value="#formStruct.deviceId#" cfsqltype="CF_SQL_VARCHAR">
</cfquery>

标签： sql-servertsqlcoldfusion

解决方案

（我认为在社区 wiki 答案下整合所有有用的评论会很有帮助）

您可以在单个语句中从多个表中删除吗？

不使用 SQL Server，不。一些数据库，如 MySQL，支持多表删除语句。SQL Server 没有。您将需要多个删除语句。但是，如果目标只是删除记录，那差别不大。

当前代码有什么问题？

  • 当前代码不起作用有几个原因。首先，初始delete查询仅在表单提交时执行。第二个代码块引用了相同的查询，但不首先验证它是否确实存在。如果表单尚未提交，这将导致未定义的变量错误。

  • 其次，更重要DELETE的是，用 CF 的说法，语句从不返回“查询”对象。因此，即使第一个查询确实执行了，查询变量DeleteDevice 也永远不会存在。同样，当代码尝试在此处使用它时会导致未定义的变量错误：<cfif DeleteDevice.RecordCount eq 1>.

    虽然除非有更多的东西，......这对cfifIMO没什么用。如果 deviceID 存在记录，它们将被删除。如果没有，什么都不会发生。跳过第二次删除所节省的任何可忽略不计的节省都被增加的代码复杂性所抵消。

  • 最后，虽然它不会导致硬错误，但并没有按照maxRows=1你的想法做。它对DELETE语句没有任何影响，也不会阻止 DELETE 语句删除多条记录。该maxRows属性仅适用于返回“查询”对象的语句，并用于限制该查询中包含的记录数。

那么如何处理多个删除语句呢？

由于 SQL Server 不支持多表DELETE语句，因此无论如何您都需要两条语句。有几种方法可以构建 SQL，每种方法都有自己的优缺点。但是，在所有情况下，您都应该使用事务来对相关语句进行分组，并确保所有内容作为一个单元完成或失败

  • 多个cfquery

    只需在事务中一个接一个地运行您的两个查询。正如肖恩建议的那样，尽管交换语句顺序。由于 FK 约束的可能性，最好先从子/FK 表中删除，最后从主/PK 表中删除。

    <cftransaction> <cfquery datasource="#session.dsn#"> DELETE FROM device_location_xref WHERE ... </cfquery> <cfquery datasource="#session.dsn#"> DELETE FROM device WHERE ..... </cfquery> </cftransaction>

  • 单个 cfquery 中的多个语句

    正如JamesAMohler 的回答所示，将两个 SQL 语句放在一个 cfquery 标记中是另一种选择。但是，请记住Alex 提到的限制。出于安全原因，可以在 db/driver 级别禁用多个语句。所以这种方法可能不适用于所有环境。

    <cftransaction> <cfquery datasource="#session.dsn#"> DELETE FROM device_location_xref WHERE ... DELETE FROM device WHERE ..... </cfquery> </cftransaction>

  • SQL 存储过程

    另一个选择是肖恩建议的：将 sql 逻辑放入存储过程中。一般来说，存储过程更适合复杂的 sql 操作，与 不同cfquery的是，它们可以返回多个结果集。 

    CREATE PROCEDURE DeleteDevice
  @deviceID VARCHAR(50)
AS
BEGIN
    -- minimal error handling for demo only
    BEGIN TRAN
        DELETE FROM device_location_xref
        WHERE device_location_xref_recno = @deviceID

        DELETE from device
        WHERE device_id = @deviceID

    END TRAN

END

    然后用cfstoredproc而不是调用它cfquery。

    <cfstoredproc procedure="DeleteDevice" datasource="#ds#"> 
   <cfprocparam type="in" value="#formStruct.deviceId#" 
          cfsqltype="cf_sql_varchar"> 
</cfstoredproc>

软删除与硬删除

最后，Shawn 提到的另一种选择是“软删除”。您可以向表中添加一个 BIT 列，而不是物理删除记录。然后UPDATE，每当您需要将记录标记为已删除时，该列就会标记。有关软删除的优缺点的讨论，请参阅此线程。

      UPDATE TableName
      SET    IsDeleted   = 1
      WHERE  TheIDColumn = 12345

推荐阅读