Trigger in SQL causing error “Product_Reorder is not a recognized SET option”

CREATE OR ALTER TRIGGER CheckQuantity
ON dbo.Products 
AFTER UPDATE
AS
BEGIN
    UPDATE dbo.Products
    SET Product_ReOrder = 1 
    FROM Inserted i
    WHERE i.Product_ID = dbo.Products.Product_ID
      AND i.Product_QOH < 5;

I am not getting a syntax error

syntax error near ;

This is referring to the ; at the end of the code.

Answer

Not 100% sure what you’re trying to do – you’re not giving us much to go on, either!

I’m assuming you mean you want to set a column called Product_ReOrder in your table to 1 if another column Product_QOH is less than 5 – correct?

In that case – use a trigger something like this:

CREATE OR ALTER TRIGGER CheckQuantity
ON dbo.Products 
AFTER UPDATE
AS
BEGIN
    UPDATE dbo.Products
    SET Product_ReOrder = 1 
    FROM Inserted i
    WHERE i.PrimaryKeyColumn = dbo.Products.PrimaryKeyColumn
      AND i.Product_QOH < 5;
END

The trigger will fire after an UPDATE, and Inserted will contain all rows (can and will be multiple rows!) that have been updated – so I’m assuming you want to check the quantity on those rows.

I’m joining the base table (dbo.Products) to the Inserted pseudo table on the primary key column of your table (which we don’t know what it is – so you need to adapt this as needed), and I’m setting the Product_ReOrder column to 1, if the Products_QOH value is less than 5.

Your line of code

Select @QOH = (select Product_QOH from inserted)

has a fatal flaw of assuming that only one row was updated – this might be the case sometimes – but you cannot rely on that! Your trigger must be capable of handling multiple rows being updated – because the trigger is called only once, even if 10 rows are updated with a command – and then Inserted will contain all those 10 updated rows. Doing such a select is dangerous – you’ll get one arbitrary row, and you’ll ignore all the rest of them ….

Is that what you’re looking for?