触发导致子查询返回超过1个值 [英] Trigger causes subquery returned more than 1 value

问题描述

我不使用触发器，但是在更新语句上执行触发器时，似乎出现了问题.例如，我有一个名为Project的表，其中允许软删除.我所说的软删除是在该表中有一个名为 Deleted 的位字段，如果为true，则表示已删除该记录；如果为false，则表示它是有效记录.

I don't use triggers much but I seem to be having an issue when a trigger is executed on an update statement. For instance I have a table called Project where we allow a soft delete. What I mean by soft delete is in this table we have a bit field named Deleted if it is true that means the record was deleted, if false then it means it is a valid record.

现在，此表具有许多关系(引用此项目的表).例如，一个项目可以有多个与之关联的程序.因此，我有一个 ProgramProject 表，其中一个程序可以具有多个项目(一对多关系，其中1是程序，许多是项目).因此，当我删除一个项目(从我上面提到的内容)时，触发器应删除与其关联的所有项目...

Now this table has many relationships (tables that reference this project). For instance, a project can have multiple programs associated with it. So I have a ProgramProject table where a program can have multiple projects (1 to many relationship where 1 is the program and many is the projects). So when I delete a project (From what I mentioned above) the trigger should delete all Projects associated with it...

但是，在某些情况下，执行触发器时，似乎会返回错误:

However, in some cases when a trigger executes it seems to return the error:

System.Data.SqlClient.SqlException被捕获HResult = -2146232060Message = Subquery返回了多个值.当子查询遵循=，！=，<，< =，>，> =或将子查询用作表达式时，不允许这样做.

我不正确地执行这些触发器吗?这是触发器的示例:

Am I doing these triggers incorrectly? Here is an example of the trigger:

ALTER TRIGGER [dbo].[trDeleteProject] ON [dbo].[Project]
FOR UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    --check if the Deleted field was updated
    IF(UPDATE(Deleted))
        BEGIN
            DECLARE @WasDeleted bit
            DECLARE @ProjectID int

            --get the project that was just deleted
            SET @ProjectID = (SELECT ProjectID FROM Inserted)
            --get whether they want the record deleted (1) or not (0)
            SET @WasDeleted = (SELECT Deleted FROM Inserted)

            --did they want to delete it? (1)
            if (@WasDeleted=1)
                BEGIN
                --yes they delete the record so we remove instances...

                    --1.)Remove programs when a project is deleted
                    UPDATE ProgramProject SET Deleted=1 WHERE ProjectID = @ProjectID

                    --2.)Remove project contact roles when you delete a project
                    UPDATE ProjectContactRole SET Deleted=1 WHERE ProjectID = @ProjectID

                    --3.)Remove sub projects when you delete a project
                    --UPDATE SubProject SET Deleted=1 WHERE ProjectID = @ProjectID

                    --4.)Remove any transport system when you delete a project
                    UPDATE ProjectTransportSystem SET Deleted=1 WHERE ProjectID = @ProjectID

                    --5.)Remove any project mechanical architecture when you delete a project
                    UPDATE ProjectMechanicalArchitecture SET Deleted=1 WHERE ProjectID = @ProjectID

                    --6.)Remove any install personnel when you delete a project
                    UPDATE ProjectInstallPersonnel SET Deleted=1 WHERE ProjectID = @ProjectID
                     END
        END
END

由于是触发器，我做错了吗?我应该加入某个临时表而不是一个更新语句吗?

Am I doing this incorrectly due to it being a trigger? Should I be joining to some temp table instead of a single update statement?

我尝试使用M Ali的示例，但是遇到了同样的问题，子查询返回了超过1行...

I tried to use M Ali's example but I was met with the same issue, subquery returned more than 1 row...

这是我使用的代码:

ALTER TRIGGER [dbo].[trDeleteProject] ON [dbo].[Project]
FOR UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT * INTO #temp 
    FROM Inserted i
    WHERE i.deleted = 1

        --1.)Remove programs when a project is deleted
        UPDATE P 
        SET p.Deleted = 1 
        FROM #temp t INNER JOIN ProgramProject p ON P.ProjectID = t.ProjectID

        --2.)Remove project contact roles when you delete a project
        UPDATE Pr 
        SET pr.Deleted = 1 
        FROM #temp t INNER JOIN ProjectContactRole pr ON Pr.ProjectID = t.ProjectID

        --3.)Remove sub projects when you delete a project
        UPDATE SP 
        SET SP.Deleted = 1 
        FROM #temp t INNER JOIN SubProject sp ON SP.ProjectID = t.ProjectID

        --4.)Remove any transport system when you delete a project
        UPDATE PTS 
        SET PTS.Deleted = 1 
        FROM #temp t INNER JOIN ProjectTransportSystem PTS ON PTS.ProjectID = t.ProjectID

        --5.)Remove any project mechanical architecture when you delete a project
        UPDATE PMA 
        SET PMA.Deleted = 1 
        FROM #temp t INNER JOIN ProjectMechanicalArchitecture PMA ON PMA.ProjectID = t.ProjectID

        --6.)Remove any install personnel when you delete a project
        UPDATE PIP 
        SET PIP.Deleted = 1 
        FROM #temp t INNER JOIN ProjectInstallPersonnel PIP ON PIP.ProjectID = t.ProjectID

        --7.)Remove any install shifts when you delete a project
        UPDATE PIS
        SET PIS.Deleted = 1
        FROM #temp t INNER JOIN ProjectInstallShift PIS ON PIS.ProjectID = t.ProjectID

        --8.)Remove any install windows when you delete a project
        UPDATE PIW
        SET PIW.Deleted = 1
        FROM #temp t INNER JOIN ProjectInstallWindow PIW ON PIW.ProjectID = t.ProjectID

        --9.)Remove any project pallet type when you delete a project
        UPDATE PPT
        SET PPT.Deleted = 1
        FROM #temp t INNER JOIN ProjectPalletType PPT ON PPT.ProjectID = t.ProjectID

        --10.)Remove issues related to a project
        UPDATE I
        SET I.Deleted = 1
        FROM #temp t INNER JOIN Issue I ON I.ProjectID = t.ProjectID

        --11.)Remove measurement architecture for a project
        UPDATE PMAS
        SET PMAS.Deleted = 1
        FROM #temp t INNER JOIN ProjectMeasurementArchitectureSystem PMAS ON PMAS.ProjectID = t.ProjectID

        --12.)Remove controls architecture for a project
        UPDATE PCAS
        SET PCAS.Deleted = 1
        FROM #temp t INNER JOIN ProjectControlsArchitectureSystem PCAS ON PCAS.ProjectID = t.ProjectID                  
END

推荐答案

只需将逻辑上删除的行放入临时表中，然后将该临时表与每个相关表连接起来，并更新其中的已删除字段.

Simply get the logically deleted rows into a temp table and than join that temp table with each related table and update the deleted fields in them tables.

您可以简单地使用已删除的表本身来与要更新的每个表联接，但是仅在删除的字段= 1限制临时表中的行数的情况下，将行插入到临时表中.并加入相对较小的表将为您带来一些性能提升.请看下面:

You can simply use the deleted table itself to join with each table you want to update but inserting rows into temp table only where field deleted = 1 limits the number or rows in your temp table. and joining a relatively smaller table will give you some performance boost. please see below:

ALTER TRIGGER [dbo].[trDeleteProject] ON [dbo].[Project]
FOR UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
-- Get logically deleted rows into a temp table 

 SELECT * INTO #temp 
 FROM Inserted i
 WHERE i.deleted = 1

        --1.)Remove programs when a project is deleted
        UPDATE P 
        SET p.Deleted = 1 
        FROM #temp t INNER JOIN ProgramProject p ON P.ProjectID = t.ProjectID

        --2.)Remove project contact roles when you delete a project
        UPDATE Pr 
        SET pr.Deleted = 1 
        FROM #temp t INNER JOIN ProjectContactRole pr ON Pr.ProjectID = t.ProjectID

        --3.)Remove sub projects when you delete a project
        UPDATE SP 
        SET SP.Deleted = 1 
        FROM #temp t INNER JOIN SubProject sp ON SP.ProjectID = t.ProjectID

        --4.)Remove any transport system when you delete a project
        UPDATE PTS 
        SET PTS.Deleted = 1 
        FROM #temp t INNER JOIN ProjectTransportSystem PTS ON PTS.ProjectID = t.ProjectID

        --5.)Remove any project mechanical architecture when you delete a project
        UPDATE PMA 
        SET PMA.Deleted = 1 
        FROM #temp t INNER JOIN ProjectMechanicalArchitecture PMA ON PMA.ProjectID = t.ProjectID

        --6.)Remove any install personnel when you delete a project
        UPDATE PIP 
        SET PIP.Deleted = 1 
        FROM #temp t INNER JOIN ProjectInstallPersonnel PIP ON PIP.ProjectID = t.ProjectID

END

更新

由于您已经提到过，您希望一次使用一个projectID来更新表.为了实现这一点，您将需要再次创建一个临时表以及一个临时表，并循环遍历临时表以通过projectid更新每个表projectid中的记录，我认为这对于一个相当简单的任务来说实在是太过分了.见下文:

Since you have mentioned you would like to update the table using a single projectID at a time. to achieve this you will need to create a while loop along with a temp table again and loop through your temp table to update records in each table projectid by projectid which I really think is an overkill for a fairly simply task. see below :

ALTER TRIGGER [dbo].[trDeleteProject] ON [dbo].[Project]
FOR UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

--check if the Deleted field was updated
    DECLARE @ProjectID int

  SELECT * INTO #Temp 
  FROM Inserted 
  WHERE deleted = 1

    --did they want to delete it? (1)
    WHILE EXISTS (SELECT * FROM #Temp)
        BEGIN
              SELECT TOP 1 @ProjectID = ProjectID 
              FROM #Temp

            --1.)Remove programs when a project is deleted
            UPDATE ProgramProject SET Deleted=1 WHERE ProjectID = @ProjectID

            --2.)Remove project contact roles when you delete a project
            UPDATE ProjectContactRole SET Deleted=1 WHERE ProjectID = @ProjectID

            --3.)Remove sub projects when you delete a project
            --UPDATE SubProject SET Deleted=1 WHERE ProjectID = @ProjectID

            --4.)Remove any transport system when you delete a project
            UPDATE ProjectTransportSystem SET Deleted=1 WHERE ProjectID = @ProjectID

            --5.)Remove any project mechanical architecture when you delete a project
            UPDATE ProjectMechanicalArchitecture SET Deleted=1 WHERE ProjectID = @ProjectID

            --6.)Remove any install personnel when you delete a project
            UPDATE ProjectInstallPersonnel SET Deleted=1 WHERE ProjectID = @ProjectID

         DELETE FROM #Temp WHERE ProjectID = @ProjectID  

       END 
END

这篇关于触发导致子查询返回超过1个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！