I have created the following trigger so that if any item in the Sales.OrderDetails table has a unitprice less than 10, it cannot have a discount greater than .5:
create trigger Sales.tr_SalesOrderDetailsDML
After insert, update
if @@ROWCOUNT > 0 return ;
set nocount on;
if exists (select oi.orderid
from inserted as oi
where oi.unitprice < 10.0 and oi.discount > 0.50)
Throw 50002, 'Order can''t have discount more than 5 if unit price is less than 10', 10;
INSERT INTO Sales.OrderDetails (orderid,productid,unitprice,qty,discount)
VALUES (10249, 16, 9.00, 1, 0.90);
You have multiple mistakes here.
aftertrigger. meaning it will only be executed after the row was already inserted or updated.
if @@ROWCOUNT > 0 return;
Alter your table to add a check constraint that will prevent the value of discount to be bigger then 5 when the unit price is less then 10.
ALTER TABLE Sales.OrderDetails ADD CONSTRAINT CHK_UnitPriceAndDiscount CHECK (unitprice > 10 OR discount < 0.5) GO