Re: Hierarchy as 'UP' constraint

x wrote: 
Foreign key on pnr + instead of trigger in SQL Server


CREATE TRIGGER treenodes_delete
ON treenodes
instead of delete
AS
DECLARE @RowCount INT
repet:
    update treenodes
    set pnr=(select pnr from deleted where deleted.nr=treenodes.pnr)
    where exists (select * from deleted where deleted.nr=treenodes.pnr);

    SELECT @RowCount = @@ROWCOUNT ;

if @RowCount>0
    goto repet ;

delete treenodes
from treenodes t, deleted d
where t.nr=d.nr;

This needs some changes to cope with various root encodings. 

The generated set of 'under water' triggers
needed would be different with the choice of
implementation strategy for the 'up' constraint.

Eg 'UP NESTED_INTERVALS'.

I don't know if it is doable for the other encodings,
but to me it certainly seems doable for the NR=PNR root
encoding.

How this would perform on large tables ? 

That depends on the chosen implementation strategy - which
of course ideally should be based on common TREENODES usage.
.

Relevant Pages