Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MarkHC
Starting Member
5 Posts |
 Posted - 2006-05-22 : 18:00:02
|
| Hi,I'm having trouble with creating a constraint on a column.Quick rundown;I have an Active Orders table (lets say ActiveOrders), each seperate buy or sell order has a unique OrderId. When a cancel or an ammend is placed, it creates a new record in the table (referenced by a GUID), with the SAME OrderId as the parent buy or sell, and the OrderType for that record will be 'Ammend' or 'Cancel'. I need to create a constraint on the table to ensure all buy and sell OrderId's are unique, however, since ammends and cancel records will have the same OrderId as the parent record (the buy/sell), it cannot be a UNIQUE constraint.Does anyone have any ideas as to how i can implement a check constraint (on inserts and updates) to enforce this?Thanks for your time |
|
|
quarter
Starting Member
8 Posts |
Posted - 2006-05-22 : 19:21:53
|
| Perhaps you need a 'before' trigger. I'm not too sure on the syntax offhand.Below is an example of an 'after' trigger. Do not use this unless you are sure you need an after trigger.CREATE TRIGGER meTrigger ON dbo.activeOrderFOR INSERT, UPDATEASIF @@ROWCOUNT = 0 RETURNIF EXISTS(SELECT 1 FROM dbo.activeOrder WHERE orderId = (SELECT TOP 1 orderId FROM inserted) AND type = 'buy' OR type = 'sell' ) RETURNThis one, I believe, would do abolutely nothing if it were buy or sell, meaning the record would still be updated. Thats why you might want to look into a 'before' trigger.Maybe someone else can respond more authoritatively. |
 |
|
|
MarkHC
Starting Member
5 Posts |
Posted - 2006-05-22 : 19:38:16
|
| Thanks for the response.I think a trigger will have to be a last resort, as our business rules state we have to use table constraints. If i figure it out, I will post the result if anyone is interested.Otherwise, if anyone else has any ideas, i would love to hear them. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-05-23 : 19:46:36
|
| Would a composite key (OrderID, OrderType) provide uniqueness for your needs?DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
|
|
|
samuelclay
Yak Posting Veteran
71 Posts |
Posted - 2006-05-24 : 15:29:53
|
| If an order was changed more than once, there would be duplicate (OrderID, OrderType) pairs as well...Seems to be a business logic problem.. They want a field to be unique, but also have multiple records have the same "unique" value?Why are you trying to put a uniqueness constraint on a table where your logic mandates duplicates? What are you trying to enforce with the constraint? Sounds like some structure/logic redesign is needed. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-05-24 : 18:25:20
|
| Sounds like you need a "ParentOrderID" column that is nullable.I know you have GUID's but lets say you had INT PK's.So, Row 1 is PK: 1 ParentOrderID: NullFor the Ammend, PK: 2ParentOrderID: NULLAlways get the "top 1" for the parent and any children it have ordered by creation date descending, and that will give you the last "state" of a given OrerId.Does that work, or do you have to work within the bounds of your existing system?Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
|
|
|
|
|
Complex Constraints