target table `RECEIPT` of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause

Question

I have a table called INVOICE which stores bill information about an order/orders, one of the columns in this table is a column named paid which is a type of bit. As its name indicates, this column indicates whether the specific order/orders bill is paid or not.

I have another table named RECEIPT, this table stores information about any payment processes for a specific invoice.

So every time user pay an amount for the specified invoice, a new receipt record is created.

Now What I'm trying to do is to create a trigger that updates the paid column in the INVOICE table and set it to 1. This update process should be triggered in case of that the sum of receipts that belong to the invoice is equal to the amount_due in the INVOICE table.

In other words, if invoice due amount= 100$ and the user paid 50$ then, late he paid the other 50$ The paid column in the INVOICE table should be set to 1 as the total payments are equal to the invoice due amount

This is the trigger I've created to achieve the above

CREATE TRIGGER tg_invoice_payment ON RECEIPT
AFTER INSERT
AS
BEGIN
UPDATE INVOICE
SET paid = 1 
WHERE INVOICE.invoice_id = (SELECT inserted.invoice_id FROM inserted)
AND (SELECT SUM(RECEIPT.amount_paid) 
FROM RECEIPT 
JOIN inserted ON RECEIPT.receipt_id = inserted.receipt_id 
WHERE RECEIPT.invoice_id = inserted.invoice_id) = (SELECT INVOICE.amount_due 
FROM INVOICE 
JOIN inserted ON INVOICE.invoice_id = inserted.invoice_id 
WHERE INVOICE.invoice_id = inserted.invoice_id)
END;

it compiled successfully but at run time I've get the below error:

The target table 'RECEIPT' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause

Answer 1

I think personally that you should update the paid status outside the scope of triggers. If you perform an INSERT into RECEIPT, you can execute the UPDATE INVOICE ... statement right after that (inside a TRANSACTION of course). A lot cleaner and predictable that way.

As to the error you are getting it's hard to say what is causing that based on the information you gave us. Perhaps the TRIGGER is triggering other TRIGGERs that produce the error you are getting? The statement you provided simply doesn't have an OUTPUT statement.

In any case, the statement you provided is not written correctly (as Damien pointed out) because the inserted table can have multipe rows. This is a rewrite to correct at least that part:

CREATE TRIGGER tg_invoice_payment ON RECEIPT
AFTER INSERT
AS
BEGIN
    UPDATE
        INVOICE
    SET 
        paid = 1
    FROM
        inserted AS ins
        INNER JOIN INVOICE AS inv ON
            inv.invoice_id=ins.invoice_id
    WHERE
        inv.amount_due=(
            SELECT 
                SUM(r.amount_paid) 
            FROM 
                RECEIPT AS r
            WHERE
                r.receipt_id=ins.invoice_id
        );
END;

But as I mentioned earlier you probably not be doing this from a TRIGGER. Execute this statement from your program right after any INSERT/UPDATE. Alternatively, write a Stored Procedure for inserting into RECEIPT and execute the UPDATE statement right after the INSERT.