CODEWITHSUNDEEP
sqlsql-server
Citizen SP
Citizen SP

Reputation: 1411

Subquery returned more than 1 value on trigger

I created a trigger to update rows in another table, triggered by a update statement in dbo.update. The reason why this trigger fails is probably because it tries to update more than 1 record. I try to change this, but without result. I'm wondering: what is the correct way to do this?

CREATE TRIGGER 
    [dbo].[Update]
ON 
    [dbo].[Records]
FOR UPDATE 
AS
  BEGIN
    SET NOCOUNT ON
      IF (SELECT State FROM INSERTED) = '1'
      Begin
       UPDATE dbo.Issue SET state = 1 WHERE EventID = (SELECT EventID FROM INSERTED)

       DECLARE @IssueID INT
       SET @issueID = (SELECT IssueID FROM Issue WHERE  EventID = (SELECT EventID FROM INSERTED))
       UPDATE dbo.Action SET state = 1 WHERE IssueID = @issueID

    end
      IF (SELECT State FROM INSERTED) = '0'
      Begin
       UPDATE dbo.Issue SET state = 0 WHERE EventID = (SELECT EventID FROM INSERTED)

       SET @issueID = (SELECT IssueID FROM Issue WHERE EventID = (SELECT EventID FROM INSERTED))
       UPDATE dbo.Action SET state = 0 WHERE IssueID = @issueID

    end
END

Upvotes: 0

Views: 153

Answers (2)

Amit Sukralia
Amit Sukralia

Reputation: 950

Your update will only work when there is only one record updated. If there are multiple records updated in a batch/transaction, then INSERTED will have multiple records in it and that's when your code will error out. You can try the below query:

UPDATE dbo.Issue 
SET state = INSERTED.State
FROM
INSERTED 
WHERE dbo.Issue.EventId = INSERTED.EventId
AND INSERTED.State in (0, 1)

UPDATE dbo.Action 
SET State = INSERTED.State 
FROM
INSERTED
JOIN Issue on INSERTED.EventID = Issue.EventID 
WHERE 
INSERTED.State in (0,1)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You are correct on the diagnosis. I think you want to replace the entire if with two updates using joins to inserted:

 UPDATE i
    SET state = ii.state
    FROM dbo.Issue i JOIN
         inserted ii
         ON i.EventID = ii.EventID
    WHERE ii.state IN (0, 1);

UPDATE a
    SET state = ii.state
    FROM dbo.Action a JOIN
         dbo.Issue i
         ON a.EventId = i.EventId JOIN
         inserted ii
         ON a.IssueId = ii.IssueId
    WHERE ii.state IN (0, 1);

Upvotes: 2

Related Questions