Check Constraints in SQL
Management wants to implement checks to ensure that the price customers are charged falls within the 20 percent of the product’s cost.
Desired output : if NETAMT on SALES is less than 20% of the Product Value it rollback the transaction and throw an error or Print Msg : "Can not be sold at this price"
Desired output : if NETAMT on SALES is less than 20% of the Product Value it rollback the transaction and throw an error or Print Msg : "Can not be sold at this price"
Reference Information
INITIAL Source Data:
PRODUCTS
PRODID QUANTITY VALUE INSERT DATE DESCRIPTION UPDATE
1 4 $10 May 1, 2012 Toy Car May 23, 2012
3 7 $12 May 3, 2012 Toy Truck May 23, 2012
4 1 $11 May 2, 2012 Electric Motor May 23, 2012
5 5 $5 May 5, 2012 Remote Control May 23, 2012
6 13 $2 May 1, 2012 AA Battery May 23, 2012
CUSTOMER
CUSTID NAME ADDRESS REGION INSERT UPDATE
3453 Bob Nobody 123 There St. Western Canada Jan 2, 2012
1243 Joe Someone 567 Here St. Western Canada Jan 2, 2012
678 Pete Peter 986 Nice Ave. Eastern Canada Jan 3, 2012
876 P. Reaber 1 Royal St. Eastern Canada Jan 5, 2012
987 Frank Bills 12 York Ave. Eastern Canada Jan 2, 2012
SALESPERSONS
SALEID NAME MANAGERID REGION INSERT UPDATE
1 Joe Sales 1 Western Canada Jan 1, 2012
2 Barry Norton 1 Western Canada Jan 1, 2012
3 Mike Nash 1 Western Canada Jan 1, 2012
4 Jane Sales 4 Eastern Canada Jan 1, 2012
5 Nancy Wills 4 Eastern Canada Jan 1, 2012
6 Peter Parker 4 Eastern Canada Jan 1, 2012
7 Billy Boss 4 Eastern Canada Jan 1, 2012
SALES
PRODID QUANTITY NETAMT CUSTID SALEID INSERT
1 1 12 3453 2 May 24, 2012
3 1 13 876 6 May 21, 2012
5 2 12 987 7 May 13, 2012
5 3 16 1243 3 May 8, 2012
6 3 8 678 5 May 12, 2012
6 1 3 1243 3 May 10, 2012
6 1 3 987 6 May 11, 2012 
David Johnson, CD
don't you mean greater than 20% of the units cost or unitcost*1.2 is minimum
Hi David,
A constraint (trigger) to be installed to pop up a message when user enters price over 20% per email exchanges I had this morning with STARS1.
I suppose, STARS1 needs to supply his update routine to embed this functionality in his update routine.
Mike
A constraint (trigger) to be installed to pop up a message when user enters price over 20% per email exchanges I had this morning with STARS1.
I suppose, STARS1 needs to supply his update routine to embed this functionality in his update routine.
Mike
To get the message, you need to implement the check on the interface side which can be pushed to the validation if the item value is included in the interface tge says rep sees.
If you are defining a transaction, you do not need a constraint as the transaction will need to check the defined rules.
The flow of data entry presumably is such that the items are selected to build the items desired and only submitted as a whole. The purchase is all or nothing validation.
I.e. The sales rep transaction must be correct for all items, and quantities.
Let me see whether a constraint on a column referencing an external column in a separate table can be done.
If you are defining a transaction, you do not need a constraint as the transaction will need to check the defined rules.
The flow of data entry presumably is such that the items are selected to build the items desired and only submitted as a whole. The purchase is all or nothing validation.
I.e. The sales rep transaction must be correct for all items, and quantities.
Let me see whether a constraint on a column referencing an external column in a separate table can be done.
@STARS1,
You wrote: "... SALES is less than 20% of the Product Value it rollback the transaction...."
1. arnold is looking into producing a message like "Can not be sold at this price" which has nothing to do with rollback of a transaction coding.
2. If your front-end is using ADO.net + stored procedure for example to update the tables, the ADO.net portion could be controlling the transaction and the messaging has to be incorporated with it to roll back a transaction.
3. If your front end is using a stored procedure only to update the tables, then the entire rollback capability is built into that stored procedure.
For situations 2 or 3 above, you need to supply your related code for modification and add the applicable TAs like .net, aps.net, etc. to your question (possibly to a new question).
mike
You wrote: "... SALES is less than 20% of the Product Value it rollback the transaction...."
1. arnold is looking into producing a message like "Can not be sold at this price" which has nothing to do with rollback of a transaction coding.
2. If your front-end is using ADO.net + stored procedure for example to update the tables, the ADO.net portion could be controlling the transaction and the messaging has to be incorporated with it to roll back a transaction.
3. If your front end is using a stored procedure only to update the tables, then the entire rollback capability is built into that stored procedure.
For situations 2 or 3 above, you need to supply your related code for modification and add the applicable TAs like .net, aps.net, etc. to your question (possibly to a new question).
mike
The puzzling thing pushing the enforcement all the way to the SQL is the portion I am questioning.
The definition of a transaction is that it will not be committed if there is an error which an attempt to record an event that the constraint if setup will prevent.
This formulation suggests that one would need to include a constraint to make sure that more items than available are sold.......
Pushing the message back is a function of capturing responses.
On the front end there are two possible validation options, one using client side scripting as the optimal mechanism. The next deals with server side validation where client side validation is not available (client side scripts disabled).
The definition of a transaction is that it will not be committed if there is an error which an attempt to record an event that the constraint if setup will prevent.
This formulation suggests that one would need to include a constraint to make sure that more items than available are sold.......
Pushing the message back is a function of capturing responses.
On the front end there are two possible validation options, one using client side scripting as the optimal mechanism. The next deals with server side validation where client side validation is not available (client side scripts disabled).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@STARS1,
Suppose your boss says never mind about that 20% issue (for a minute). When user enters sales information on some front-end (asp.net etc.) how date gets updated in SQL Server?
Most likely, there is a stored procedure handles updating the tables and includes transaction rollback etc.
Please get hold of that stored procedure so we can revise to include messaging you have specified under condition you are asking for.
If ado.net is used (to control) transaction then we may need that piece of front-end code calling the updating stored procedure. But for now, let's focus on the updating stored procedure. Post its T-SQL here.
Mike
Suppose your boss says never mind about that 20% issue (for a minute). When user enters sales information on some front-end (asp.net etc.) how date gets updated in SQL Server?
Most likely, there is a stored procedure handles updating the tables and includes transaction rollback etc.
Please get hold of that stored procedure so we can revise to include messaging you have specified under condition you are asking for.
If ado.net is used (to control) transaction then we may need that piece of front-end code calling the updating stored procedure. But for now, let's focus on the updating stored procedure. Post its T-SQL here.
Mike
ASKER
This is just a practice type exam question, no front end involved.
"NETAMT" in "SALES" table should be within 20% of the "Value" each time a product is sold so no product will be sold less then 20% of the product cost
"Value" is given in Products table
So each time sale is being made and before it inserts the "NETAMT" in "SALES" table should check if the NETAMT is not less then 20% of the "Value" of that product.
May be trigger on Insert or CHECK Constraint can do that but I don't know how
"NETAMT" in "SALES" table should be within 20% of the "Value" each time a product is sold so no product will be sold less then 20% of the product cost
"Value" is given in Products table
So each time sale is being made and before it inserts the "NETAMT" in "SALES" table should check if the NETAMT is not less then 20% of the "Value" of that product.
May be trigger on Insert or CHECK Constraint can do that but I don't know how
a trigger or a check constraint function that will query that will compare the thresholds for price offered versus value*1.2
You would need to pass the itemID being sold and the price for which it is being sold to the check function.
You should make the comtext of your question clear.
You would need to pass the itemID being sold and the price for which it is being sold to the check function.
You should make the comtext of your question clear.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.