CODEWITHSUNDEEP
sqlt-sqldatabase-trigger
Alphas Supremum
Alphas Supremum

Reputation: 505

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
as
begin 
  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;

end;
go

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?

Upvotes: 1

Views: 179

Answers (4)

Zohar Peled
Zohar Peled

Reputation: 82474

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)
GO

Upvotes: 3

Chanom First
Chanom First

Reputation: 1136

it's not do statement under if @@ROWCOUNT > 0 return ; because @@rowcount greater than 0 then it return

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28890

Remove RowCount >0,change it to 

if (@@rowcount=0) return;

Upvotes: 1

Milen
Milen

Reputation: 8867

Check this line if @@ROWCOUNT > 0 return ; This will return immediately if affected rows is greater than 0... so the additional logic will not be executed. Replace with if @@ROWCOUNT = 0

Upvotes: 1

Related Questions