AFTER INSERT trigger fails when more than one row to be updated

Question

Hiya,

Long time reader, first time caller, and all that...

Here's the business problem: a user makes one or more requests for documents. At some later time, a document is uploaded to the system. If that document matches one or more of the requests, the document request is fulfilled. So, there could be one or many requests for Document A, for instance. When Document A is uploaded, all of the requests for Document A are fulfilled.

And here's my technical problem: I've got an AFTER INSERT trigger on the table where the upload of the document is recorded. It checks the DocumentRequest table and updates each row that matches the uploaded document. If only one row matches (only one request for Document A), everything is hunky-dory. If, however, more than one matches, the UPDATE statement in the trigger fails -- I see this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Here are the CREATE statements for the relevant tables:

CREATE TABLE [dbo].[DocumentRequest](
    [DocumentRequestId] [int] IDENTITY(1,1) NOT NULL,
    [BatchID] [int] NULL,
    [CertificateNum] [varchar](20) NOT NULL,
    [RequestedTypCd] [varchar](5) NULL,
    [RequestedReason] [varchar](60) NULL,
    [DestinationTypCd] [varchar](5) NULL,
    [DocumentPackageTypCd] [varchar](5) NULL,
    [RequestedDtm] [datetime] NOT NULL,
    [RequestNotes] [varchar](1000) NULL,
    [RequestStatusTypCd] [varchar](5) NOT NULL,
    [InactiveFlag] [char](1) NULL,
    [CreationDtm] [datetime] NOT NULL,
    [CreationUserID] [varchar](10) NOT NULL,
    [CompletedDtm] [datetime] NULL,
    [CompletedUserID] [varchar](10) NULL,
    [ModifiedDtm] [datetime] NULL,
    [ModifiedUserID] [varchar](10) NULL,
 CONSTRAINT [XPKDocumentRequest] PRIMARY KEY NONCLUSTERED 
(
    [DocumentRequestId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

--------------
CREATE TABLE [dbo].[DocumentRequestContents](
    [DocumentTypCd] [varchar](5) NOT NULL,
    [CreationDtm] [datetime] NOT NULL,
    [CreationUserID] [varchar](10) NOT NULL,
    [DocumentRequestId] [int] NOT NULL,
    [DocumentReceivedDtm] [datetime] NULL,
    [DocumentReceivedFlag] [char](1) NULL,
    [DocumentIgnoreFlag] [char](1) NULL,
 CONSTRAINT [XPKDocumentRequestContents] PRIMARY KEY NONCLUSTERED 
(
    [DocumentRequestId] ASC,
    [DocumentTypCd] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

--------------
CREATE TABLE [dbo].[DocumentStorage](
    [DocumentStorageID] [int] IDENTITY(1,1) NOT NULL,
    [CertificateNum] [varchar](10) NULL,
    [DocumentHandleID] [int] NULL,
    [DocumentTypVal] [varchar](100) NULL,
    [CreationDtm] [datetime] NULL,
    [CreationUserID] [varchar](10) NULL,
    [lastupd_user] [varchar](8) NULL,
    [lastupd_stamp] [datetime] NULL,
    [DocumentNam] [varchar](100) NULL,
PRIMARY KEY NONCLUSTERED 
(
    [DocumentStorageID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


--------------
CREATE TABLE [dbo].[DocumentProfile](
    [DocumentProfileID] [int] IDENTITY(1,1) NOT NULL,
    [DocumentTypCd] [varchar](5) NULL,
    [DocumentVersionNum] [varchar](10) NULL,
    [DocumentApproveInd] [varchar](1) NULL,
    [CreationDtm] [datetime] NULL,
    [CreationUserID] [varchar](10) NULL,
    [lastupd_stamp] [datetime] NULL,
    [lastupd_user] [varchar](8) NULL,
    [DocumentStorageTypVal] [varchar](100) NULL,
    [MIMETypVal] [varchar](50) NULL,
    [DocumentSourceTypCd] [varchar](5) NULL,
    [DocumentFormatTypCd] [varchar](5) NULL,
    [DocumentReceiveLocationTypCd] [varchar](5) NULL,
    [DocumentIndexTypCd] [varchar](5) NULL,
    [DocumentNam] [varchar](100) NULL,
    [DocumentTrackedInd] [char](1) NULL CONSTRAINT [DF_DocumentProfile_DocumentTrackedInd]  DEFAULT ('N'),
PRIMARY KEY NONCLUSTERED 
(
    [DocumentProfileID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

And the trigger...

CREATE TRIGGER [trg_DMTDocumentReceived_DocumentStorage]
   ON  [dbo].[DocumentStorage]
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    UPDATE DocumentRequestContents
    SET DocumentRequestContents.DocumentReceivedFlag = 'Y',
        DocumentRequestContents.DocumentReceivedDtm = getdate()
    FROM
        DocumentRequestContents
        INNER JOIN DocumentRequest
            ON DocumentRequestContents.DocumentRequestId = DocumentRequest.DocumentRequestId
        INNER JOIN DocumentProfile
            ON DocumentRequestContents.DocumentTypCd = DocumentProfile.DocumentTypCd
        INNER JOIN Inserted
            ON DocumentProfile.DocumentStorageTypVal = Inserted.DocumentTypVal
    WHERE
        (DocumentRequestContents.DocumentReceivedFlag <> 'Y' OR
            DocumentRequestContents.DocumentReceivedFlag IS NULL)
        AND (DocumentRequest.InactiveFlag IS NULL)
        AND (DocumentRequest.CertificateNum = Inserted.CertificateNum)
        AND (DocumentProfile.DocumentStorageTypVal = Inserted.DocumentTypVal)
END

I'm at a bit of a loss as to where to go from here. Can you give a guy a hand?

EDIT: A request can have more than one document, so the table that is being updated in the trigger (DocumentRequestContents) also has a trigger on it, which determines if the entire request has been fulfilled. As far as I can tell, it does not have a subquery, either, but here it is:

CREATE TRIGGER [trg_DMTDocumentReceived_CompletenessCheck]
   ON  [dbo].[DocumentRequestContents] 
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @TotalDocs int, @ReceivedDocs int

    IF UPDATE(DocumentReceivedFlag)
    BEGIN
        IF (SELECT DocumentReceivedFlag FROM Inserted) = 'Y' AND (SELECT DocumentReceivedFlag FROM Deleted) = 'N'
        BEGIN
            SELECT @TotalDocs = count(*)
            FROM DocumentRequestContents
                INNER JOIN Inserted
                    ON DocumentRequestContents.DocumentRequestID = Inserted.DocumentRequestID
            WHERE (DocumentRequestContents.DocumentIgnoreFlag <> 'Y' OR DocumentRequestContents.DocumentIgnoreFlag IS NULL)

            SELECT @ReceivedDocs = count(*)
            FROM DocumentRequestContents
                INNER JOIN Inserted
                    ON DocumentRequestContents.DocumentRequestID = Inserted.DocumentRequestID
            WHERE DocumentRequestContents.DocumentReceivedFlag = 'Y'

            IF (@ReceivedDocs = @TotalDocs)
            BEGIN
                UPDATE DocumentRequest
                SET RequestStatusTypCd = 'CMPLT',
                    CompletedDtm = getdate(),
                    CompletedUserID = 'SYSTEM',
                    ModifiedDtm = getdate(),
                    ModifiedUserID = 'SYSTEM'
                FROM DocumentRequest
                    INNER JOIN Inserted 
                        ON DocumentRequest.DocumentRequestId = Inserted.DocumentRequestId
            END
        END
    END
END

Thanks, Jason

Answer 1

You may want to try aliasing the Inserted table in your join. While I haven't experienced this myself, it may be attempting to perform a subquery in your WHERE clause rather than using the joined table.

Answer 2

Your trigger doesn't appear to have a sub-query anywhere in it - this is the only trigger on your table? This error isn't caused by a join or a multi-row update, so I'm at a loss as to why your trigger would be generating this error.

Do you get the error when you're attempting the insert into the table itself? Perhaps it's actually the query you're using to insert that's failing - please post that insert statement as well.

Answer 3

Check if you don't have a trigger on DocumentRequestContents as well.

Answer 4

On re-reading, the obvious problem is this part of the second trigger:

IF (SELECT DocumentReceivedFlag FROM Inserted) = 'Y' 
    AND (SELECT DocumentReceivedFlag FROM Deleted) = 'N'

This would fail if multiple rows where inserted, because SQL Server expects both of these subqueries to return one or no rows.

By the way, I personally avoid triggers at any cost. They introduce too much complexity for me to handle.

Answer 5

The offending line appears to be here:

IF (SELECT DocumentReceivedFlag FROM Inserted) = 'Y' AND
   (SELECT DocumentReceivedFlag FROM Deleted) = 'N' ...

This is essentially a "subquery". The IF statement is written with the expectation that there will only be one row in both inserted and deleted. Without knowing the details of what the business logic is here, I can only speculate, but most likely you'll be OK if you just rewrite that as:

IF EXISTS(SELECT 1 FROM inserted WHERE DocumentReceivedFlag = 'Y') AND
   EXISTS(SELECT 1 FROM deleted WHERE DocumentReceivedFlag = 'N'))

But it might be more complicated than that... it could be that you actually need to join the inserted to deleted rows and check if the flag has actually changed from 'Y' to 'N'.