
IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='TR' AND name='trig_PREFS_NODE')
    DROP TRIGGER trig_PREFS_NODE
/

CREATE TRIGGER trig_PREFS_NODE
  ON PREFS_NODE 
  INSTEAD OF DELETE 
  AS 

  SET NOCOUNT ON;

  WITH cte AS 
  ( SELECT NODE_ID, PARENT_NODE_ID 
    FROM DELETED 
    UNION ALL 
    SELECT c.NODE_ID, c.PARENT_NODE_ID
    FROM PREFS_NODE AS c 
    INNER JOIN cte AS p 
    ON c.PARENT_NODE_ID = p.NODE_ID 
  ) 
  SELECT * 
  into #tmp
  FROM cte
  OPTION (MAXRECURSION 32767)
  
  DELETE FROM PREFS_PROPERTY_VALUE
    WHERE NODE_ID IN (SELECT NODE_ID FROM #tmp);   

  DELETE FROM PREFS_NODE 
    WHERE NODE_ID IN(
      SELECT NODE_ID FROM #TMP)

  drop table #tmp 
/