CODEWITHSUNDEEP
sqlsql-server
Benua
Benua

Reputation: 269

SQL insert trigger condition statement and multiple rows

Could you please help me to finish my trigger. What i got so far:

CREATE TRIGGER [dbo].[atbl_Sales_OrdersLines_ITrigGG]
ON [dbo].[atbl_Sales_OrdersLines]
FOR INSERT
AS 
BEGIN

DECLARE @ID INT = (SELECT ProductID
FROM INSERTED)

DECLARE @OrderedQ INT = (SELECT SUM(Amount)
FROM atbl_Sales_OrdersLines
WHERE ProductID = @ID)

DECLARE @CurrentQ INT = (SELECT Quantity 
FROM atbl_Sales_Products
WHERE ProductID = @ID)

DECLARE @PossibleQ INT = (SELECT Amount
FROM INSERTED
WHERE ProductID = @ID)

IF (@CurrentQ - @OrderedQ >= @PossibleQ)

ELSE

END

I need to complete the code. Can not figure out how to do it. I need that if condition is met - trigger would allow the insert. If else, trigger would stop the insert/or rollback and prompt a message that quantity is not sufficient.

Also, will this code work if insert is multiple lines with different product ids?

Thanks.

Upvotes: 0

Views: 127

Answers (2)

hatchet - done with SOverflow
hatchet - done with SOverflow

Reputation: 16259

Something like this might work. This trigger checks the products that are in the insert, summing the total that have been ordered (now and in the past), and if any of them exceed the available quantity, the whole transaction is rolled back. Whenever writing triggers, you want to avoid any assumptions that there is a single row being inserted/updated/deleted, and avoid cursors. You want to just use basic set based operations.

    CREATE TRIGGER [dbo].[atbl_Sales_OrdersLines_ITrigGG]
    ON [dbo].[atbl_Sales_OrdersLines]
    FOR INSERT
    AS 
    BEGIN
        IF (exists (select 1 from (
            select x.ProductId, totalOrdersQty, ISNULL(asp.Quantity, 0) PossibleQty from (
                select i.ProductId, sum(aso.Amount) totalOrdersQty 
                from (select distinct ProductId from inserted) i
                join atbl_Sales_OrdersLines aso on aso.ProductId = i.ProductId
                group by productId) x
            left join atbl_Sales_Product asp on asp.ProductId = x.ProductId
            ) x
            where PossibleQty < totalOrdersQty))
        BEGIN    
            RAISERROR ('Quantity is not sufficient' ,10,1)
            ROLLBACK TRANSACTION
        END
    END

I still think this is a horrible idea.

Upvotes: 1

Jigar
Jigar

Reputation: 214

Try this, 

CREATE TRIGGER [dbo].[atbl_Sales_OrdersLines_ITrigGG]
ON [dbo].[atbl_Sales_OrdersLines]
INSTEAD OF INSERT --FOR INSERT
AS 
BEGIN

DECLARE @ID INT = (SELECT ProductID
FROM INSERTED)

DECLARE @OrderedQ INT = (SELECT SUM(Amount)
FROM atbl_Sales_OrdersLines
WHERE ProductID = @ID)

DECLARE @CurrentQ INT = (SELECT Quantity 
FROM atbl_Sales_Products
WHERE ProductID = @ID)

DECLARE @PossibleQ INT = (SELECT Amount
FROM INSERTED
WHERE ProductID = @ID)

IF (@CurrentQ - @OrderedQ >= @PossibleQ)
BEGIN
        INSERT INTO YOURTABLE (COLUMN1, COLUMN2, COLUMN3, ..)
         SELECT COLUMN1, COLUMN2, COLUMN3, ..  
         FROM inserted
END
ELSE
BEGIN
    RAISERROR ('Quantity is not sufficient' ,10,1)
      ROLLBACK TRANSACTION

END

Upvotes: 0

Related Questions