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 _at_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 _at_RowCount = @@ROWCOUNT ;
>
> if _at_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. Received on Fri Nov 25 2005 - 13:08:43 CET