2

Please Note: I'm a Software Developer with limited knowledge of Database Programming/Administration.

I'm trying to build a structure where companies can have parent-child relationships with one another. The basic idea being that you might have different branches of a company operating in different parts of the world which share some data but not all data (Mailing Address, and Local Contacts for instance).

A striped down version of the table would look something like this:

CREATE TABLE COMPANY (
    COMPANY_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    PARENT_COMPANY INT FOREIGN KEY REFERENCES COMPANY(COMPANY_ID),
    NAME NVARCHAR(256)
);

The problem I'm having is that this structure allows cyclical relationships where a parent company could become the child of one of its descendants. The sql below would cause such a situation to occur.

INTO COMPANY
(COMPANY_ID, PARENT_COMPANY, NAME)
VALUES
(1, null, 'Company1'),
(2, 1, 'Company2'),
(3, 2, 'Company3');

UPDATE COMPANY
SET PARENT_COMPANY = 3
WHERE COMPANY_ID = 1;

Because this kind of relationship could cause infinite loops, I want to prevent the situation from ever occurring.

The best idea I could come up with was run a trigger on the COMPANY table, that would check to make sure any value updated in the PARENT_COMPANY column didn't cause a cyclical relationship. However Sql Server doesn't have a BEFORE UPDATE trigger; only AFTER and INSTEAD OF triggers, both of which run after the table has already been updated. This means the cyclical relationship would already be created before I got a chance to check for it. At this point I could potentially rebuild the "before" version of the table from the triggers DELETED temporary table, and the Company Table itself; then search that table for a cyclical relationship; but that seems very cumbersome and inefficient.

Is there any other way I could check for cyclical relationships in a self referencing Sql Server structure.

PS. I was planning on using something like this to search for a cyclical relationship in the trigger: 
DECLARE @CompanyId int = 1 -- ID of company thats been changed
;WITH cte AS 
 (
  SELECT a.COMPANY_ID, a.PARENT_COMPANY, a.NAME
  FROM COMPANY a
  WHERE COMPANY_ID = @CompanyId
  UNION ALL
  SELECT a.COMPANY_ID, a.PARENT_COMPANY, a.NAME
  FROM COMPANY a JOIN cte c ON a.PARENT_COMPANY = c.COMPANY_ID
  )
  SELECT COMPANY_ID, PARENT_COMPANY, NAME
  FROM cte
  ORDER BY NAME
Tom Aranda
  • 5,919
  • 11
  • 35
  • 51
azucarrogers
  • 125
  • 9
  • 1
    You could create an insert function that first checks for the cyclical relationship, all inserts would then have to be done through the function. You have an additional problem: what is to prevent an update statement from causing a cycle? Or a self referencing entry? – Brian Leach Dec 15 '17 at 00:50
  • 2
    I suppose one option would be a `check` constraint with a function that checks for a recursive loop. – ZLK Dec 15 '17 at 00:51
  • 2
    This is a problem with adjacency lists, there isn't a clean relational constraint to prevent this. Everything requires something programmatic to check for the circular reference(s). If you want to make it truly impossible, look at using "nested sets" instead of "adjacency lists". – MatBailie Dec 15 '17 at 01:12
  • Cool task. I built something like this but, rather than attempt as you are, I simply handled with a junction table; i resolved all sorts of many to many relationships and also updated the PK of the table, so the entire relationships could be changed daily or whenever. – Lee Dec 15 '17 at 01:27

1 Answers1

0

First of all I want to correct some of your statements:

  1. AFTER triggers: Trigger is part of the batch operation that caused the trigger to fire (e.g. UPDATE) and therefore the record does not become committed (and visible) unless trigger succeeds.

.... an after trigger fires before the an implicit transaction is committed. A rollback in a trigger will rollback the statement that fired the trigger and abort the entire batch as well

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/73862414-d770-46bb-97fb-249a3fb38680/does-a-insert-trigger-fire-when-the-insert-is-committed?forum=transactsql

The only way to bypass this is to run SELECT queries with explicit READ UNCOMMITTED hints, which is, generally never done, unless for very specific purposes.

  1. INSTEAD OF triggers: They run, as the name implies, instead of a regular insert/update operation. In other words if an INSTEAD OF trigger for an operation (e.g. update) is defined, it overrides update behaviour. See more: https://technet.microsoft.com/en-us/library/ms179288(v=sql.105).aspx

Summary:

In your case I would recommend creating a simple AFTER UPDATE trigger, where you will check your data and if problems are found throw a meaningful error and execute a ROLLBACK TRANSACTION to "cancel" this change. This way you achieve a similar behaviour to how constrains etc. work.

I suggest you also take a look at this comprehensive post about storing hierarchical relationships in a database: What are the options for storing hierarchical data in a relational database?

Alex
  • 4,885
  • 3
  • 19
  • 39