dimanche 30 août 2020

Creating a trigger to update quantities in T-SQL

I am trying to create a trigger in T-SQL that will be called when an order is placed that will check the quantity in a the product table and see if the the order quantity requested is the product table quantity. If the order is greater than the quantity on hand then the change is not reflected and the the transaction is rolled back. However, I am getting stuck with the if statement and can use some advice on how to do this properly. Any advice is welcomed.

CREATE TRIGGER trg_checkQ
ON product
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;


IF I.quanitity > product.quanitity
FROM Inserted I
WHERE I.productid = product.productid 
RAISERROR('Transaction denied!!!',16,1);
ROLLBACK TRAN;
END;

UPDATE product SET quanitity = product.quanitity - I.quanitity
FROM Inserted I
WHERE I.productid = product.productid
END

Aucun commentaire:

Enregistrer un commentaire