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.

CodePudding user response：

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?

CodePudding user response：

I'm unclear what you were thinking when you wrote this code, or what template you were basing off, but there are many syntax errors.

It seems you probably want something like this:

• The update() function only tells us if that column was present in the update statement, not if anything was actually changed.
• We need to check if we are being called recursively, in order to bail out.
• We also check if no rows have been changed at all, and bail out early
• Note how inserted and deleted are compared to see if any rows actually changed. This also deals correctly with multiple rows.
• We then need to rejoin Products in order to update it.

create or alter trigger CheckQuantity
on Products 
after update
as
    set nocount on;

    if not(update(Products_QOH))
       or trigger_nestlevel(object_id(N'dbo.CheckQuantity'),'AFTER','DML') > 1
       or not exists (select 1 from inserted)
         return;  -- early bail-out

    update p
    set Product_ReOrder = 1
    from Products p
    join (
        select i.YourPrimaryKey, i.Products_QOH
        from inserted i
        where i.Product_QOH < 5
        except
        select d.YourPrimaryKey, d.Products_QOH
        from deleted d
    ) i on i.YourPrimaryKey = p.YourPrimaryKey;

However, I don't understand why you are using a trigger at all.

I strongly suggest you use a computed column for this instead:

ALTER TABLE Products
    DROP COLUMN Product_ReOrder;

ALTER TABLE Products
    ADD Product_ReOrder AS (CASE WHEN Product_QOH < 5 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END);