SQL Server : trigger handle multiple rows

Question

I have a trigger if I do multiple insert then I get an error on:

IF((SELECT FACTREGELPRIJS FROM inserted) > 0)

This is my trigger:

CREATE TRIGGER [dbo].[trg]
ON [dbo].[FACTUURREGEL]
AFTER INSERT
AS
BEGIN
    DECLARE @rowCount INT = @@ROWCOUNT 

    IF(@rowCount = 0)
        RETURN

    BEGIN TRY
        IF(EXISTS(SELECT 1 FROM inserted) 
           AND NOT EXISTS(SELECT 1 FROM deleted))
        BEGIN
            IF((SELECT FACTREGELPRIJS FROM inserted) > 0)
            BEGIN
                RAISERROR('MAG geen prijs toegevoegd worden',16,1)
            END
            ELSE
            BEGIN
                UPDATE f 
                SET FACTREGELPRIJS = (p.PRODUCTPRIJS * f.FACTREGELHOEVEELHEID) - ((p.PRODUCTPRIJS * f.FACTREGELHOEVEELHEID)*(f.FACTREGELKORTING/100.0))
                FROM FACTUURREGEL f
                INNER JOIN PRODUCT p on p.PRODUCTID = f.PRODUCTID
            END
        END
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;

        DECLARE
            @ErrorMessage   VARCHAR(400),
            @ErrorSeverity  INT,
            @ErrorState     INT

        SELECT
            @ErrorMessage   = ERROR_MESSAGE(),
            @ErrorSeverity  = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE()

        RAISERROR (@ErrorMessage , @ErrorSeverity, @ErrorState)
    END CATCH
END

How can I fix it? thank you

You need to use aggregate(max(...)) for if condition and you have to join to inserted pseudo-table to perform updates. Remember that triggers are fired for entire set of changes, not for every record. — Arvo
– Arvo, Commented Mar 1, 2016 at 11:36

marc_s · Accepted Answer · 2016-09-15 13:55:06Z

1

You cannot compare a column with a scalar value. Your query should return a scalar value not a column.

For example something like this:

IF((SELECT count(FACTREGELPRIJS) FROM inserted)> 0)

For checking price do this:

IF((SELECT SUM(FACTREGELPRIJS) FROM inserted)> 0)

edited Sep 15, 2016 at 13:55

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 1, 2016 at 11:36

Akshey Bhat

8,6872 gold badges23 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Akshey Bhat Over a year ago

What are you checking in if statement ?

Daniil Pimenau Over a year ago

I can't instert invoice price, if price > 0 error, The price is based on quantity, price and discount

Akshey Bhat Over a year ago

so you are checking if any row was inserted or not. Am i right?

Daniil Pimenau Over a year ago

Each row is a new invoice. should column "price" are checked. Which should not be greater than 0

Daniil Pimenau Over a year ago

i need a check every insert row, not all inserted rows together

|

Collectives™ on Stack Overflow

SQL Server : trigger handle multiple rows

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related