CODEWITHSUNDEEP
.netsqlsql-serverlinq-to-sql
user2528012
user2528012

Reputation: 89

Constraint with Sql statement

I have 2 tables

1.Sales_Items

2.Stock_Items

I want to make sure that when any item is inserted in sale_items table then it's qty must be less than (Stock_Items.qty-Sales_Items.Qty),if this condition is not true then insertion should fail and throw an exception.

How should i do this?Do I need to use sql constraint with expression or Sql triggers?

Upvotes: 1

Views: 73

Answers (2)

bjnr
bjnr

Reputation: 3437

Even it is possible to enforce business rules with triggers, you can use CHECK CONSTRAINT with user defined function:

ALTER TABLE sales_items
ADD CONSTRAINT CHECK (qty < dbo.udfCheckAvailableStock(YourProductIdColumn, qty))

I would use a trigger in case you need to update Stock_Items when a new row is added in Sales_Items.

Triggers can enforce restrictions that are more complex than those defined with CHECK constraints. Unlike CHECK constraints, triggers can reference columns in other tables. For example, a trigger can use a SELECT from another table to compare to the inserted or updated data and to perform additional actions, such as modify the data or display a user-defined error message.

See source

Upvotes: 0

Pantelis Natsiavas
Pantelis Natsiavas

Reputation: 5369

It could be done using both triggers and constraint. I would choose a constraint for the specific scenario.

This is because the triggers are actually used when you want to do something actively on the moment of the insert/update/delete, like adding or updating another row. In your scenario, you don't actually need a trigger. Your scenario is the definition of the constraint need. The only reason to use a trigger is if you want to throw a custom error message.

See this as a reference for constraints.

Hope I helped!

Upvotes: 1

Related Questions