Question

I am creating a self-related table:

Table Item columns:
ItemId int - PK;
Amount money - not null; Price money - a computed column using a UDF that retrieves value according to the items ancestors Amount.
ParentItemId int - nullable, reference to another ItemId in this table.

I need to avoid a loop, meaning, a sibling cannot become an ancestor of his ancestors, meaning, if ItemId=2 ParentItemId = 1, then ItemId 1 ParentItemId = 2 shouldn t be allowed.

I don t know what should be the best practice in this situation. I think I should add a CK that gets a Scalar value from a UDF or whatever else.

EDIT: Another option is to create an INSTEAD OF trigger and put in 1 transaction the update of the ParentItemId field and selecting the Price field from the @@RowIdentity, if it fails cancel transaction, but I would prefer a UDF validating.

Any ideas are sincerely welcomed.

Answer 1

Tested and works just great:

CREATE TRIGGER Item_UPDATE
   ON Item
   FOR INSERT, UPDATE
AS 
BEGIN

BEGIN TRY
    SELECT Price FROM INSERTED
END TRY
BEGIN CATCH
    RAISERROR( This item cannot be specified with this parent. , 16, 1)
    ROLLBACK TRANSACTION;
END CATCH

END
GO

Answer 2

Does this definitely need to be enforced at the database level?

I m only asking as I have databases like this (where the table similar to this is like a folder) and I only make sure that the correct parent/child relationships are set up in the application.

Answer 3

Checks like this is not easy to implement, and possible solutions could cause a lot of bugs and problems may be harder then initial one. Usually it is enough to add control for user s input and prevent infinite loop on read data. If your application uses stored procedures, no ORM, than I would choose to implement this logic in SP. Otherwise - handle it in other layers, not in DB

Answer 4

How big of a problem is this, in real life? It can be expensive to detect these situations (using a trigger, perhaps). In fact, it s likely going to cost you a lot of effort, on each transaction, when only a tiny subset of all your transactions would ever cause this problem.

Think about it first.

Answer 5

A simple trick is to force the ParentItemId to be less than the ItemId. This prevents loop closure in this simple context.

However, there s a down side - if you need for some reason to delete/insert a parent, you may need to delete/insert all of its children in order as well.

Equally, hierarchies need to be inserted in order, and you may not be able to reassign a parent.

友情链接