0

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.

Improve this question
5
  • The answer to your last question is no, it won't work if insert is multiple lines with different product ids. The first two lines where you set @id can only capture a single product id out of all the productIds in inserted rows. So, everything after that is working with a subset of the truth. Commented Jul 19, 2017 at 22:23
  • No, this is very bad code. If multiple rows are inserted, it just picks one ID (the last one from the SELECT) for example. It ignores all the others. ALL triggers need to be written in set notation that ASSUMES there will be multiple rows in the Inserted (and/or deleted) collections. Any time you're declaring a scalar variable like here, it's a red flag. This will never work for what you are trying to do. Commented Jul 19, 2017 at 22:24
  • Any suggestions? Im doing an exercise for learning purposes its not an actual project. Furthermore, im fine if whole order gets rejected when one or more products lack quantity. Commented Jul 19, 2017 at 22:26
  • I suggest you do this in business logic further up the food chain, and leave triggers for simpler, more focused things like maintaining referential integrity (and as a last resort when there are no better options). In a sense, you're asking us to tell you how to drive a nail with a screwdriver. Commented Jul 19, 2017 at 22:28
  • I know that there are much better ways to solve this, but i need to use trigger. Silly assignment, i know. Commented Jul 19, 2017 at 22:31

2 Answers 2

Reset to default
1

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.

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

4 Comments

Thanks, but i get syntax errors where THEN and END are used.
I typed that in off the top of my head. I forgot the BEGIN. See edit.
Still cannot run it on SQL server, but thanks - now i know the concept of how this could be done.
Take out the "THEN". SQL Server just uses "BEGIN/END" blocks, and IF/ELSE, no "THEN".
0

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
Improve this answer

5 Comments

This is still wrong if there is more than one row in inserted.
Maybe we could run a loop here? Check each row (with re-declaring ID on each check) If any of the rows fail - rollback.
It's more complicated than that. You'd need to group the inserted rows by productId and rollback if the sum for any productId exceeded the available amount.
I think im ok here. Ill check how the rest of the code works. But i think it is like this: a) Order header is created in one table (but no products added here); b) Products are added in Order edit (added one line at a time). So i think that separate inserts are being performed here. If inserts are separate and only one row is inserted at a time - this trigger should be sufficient.
Never run loops in triggers if you can avoid it. Use 'set' operations to operate on the entire inserted/deleted collections at once.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.