Alphas Supremum Alphas Supremum - 1 year ago 68
SQL Question

SQL Trigger is Not working

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
on Sales.OrderDetails
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 > 0.50)
Throw 50002, 'Order can''t have discount more than 5 if unit price is less than 10', 10;


When I insert the following row:

INSERT INTO Sales.OrderDetails (orderid,productid,unitprice,qty,discount)
VALUES (10249, 16, 9.00, 1, 0.90);

it accepts it, the trigger does not Throw the error despite the fact that it's unitprice is less than 10 and decount is bigger than 5.

What am I missing here?

Answer Source

You have multiple mistakes here.

  1. Your trigger is an after trigger. meaning it will only be executed after the row was already inserted or updated.
  2. You are stopping the trigger to be executed when rows are updated, with that if @@ROWCOUNT > 0 return;
  3. This is the kind of thing you don't want triggers for, but a check constraint.

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download