in-line-view는 ASE 12.5.1에서부터 지원이 됩니다.

/**************************************************************************************
   TABLE NAME : X11_DEPT(부서)                 
   DEFINITION : 행정시스템에서 사용하는 조직 구성도 
 **************************************************************************************/

/* 부서 table */

CREATE TABLE X11_DEPT
(
   CD_DEPT          VARCHAR(8)      NOT NULL,    /* 01:부서코드                    */
   NM_DEPT          VARCHAR(20)     NOT NULL,    /* 02:부서명                      */
   CD_UDEPT         VARCHAR(8)      NOT NULL,    /* 03:상위부서코드                */
   TEL_DEPT         VARCHAR(20)     DEFAULT '',  /* 04:부서대표전화(XXX-XXXX-XXXX) */
   FAX_DEPT         VARCHAR(20)     DEFAULT '',  /* 05:부서팩스(XXX-XXXX-XXXX)     */
   MAIL_DEPT        VARCHAR(30)     DEFAULT '',  /* 06:부서대표이메일              */
   URL_DEPT         VARCHAR(30)     DEFAULT '',  /* 07:부서URL                     */
   SEQ_LEVEL        INT             DEFAULT 0,   /* 08:LEVEL                       */
   TX_HIERARCHY     VARCHAR(100)    DEFAULT '',  /* 09:계층                        */
   ST_DEPT          CHAR(1)         DEFAULT 'Y', /* 10:Y:사용, N:폐기              */
   NM_CREATE        VARCHAR(20)     DEFAULT '',  /* 11:등록자                      */
   NM_MODIFY        VARCHAR(20)     DEFAULT '',  /* 12:수정자                      */
   DT_CREATE        DATETIME        DEFAULT GETDATE(),         /* 13:등록일시      */
   DT_MODIFY        DATETIME        DEFAULT GETDATE(),         /* 14:수정일시      */
  
   CONSTRAINT PKX11_DEPT PRIMARY KEY (CD_DEPT) ,
   CONSTRAINT FK1X11_DEPT FOREIGN KEY (CD_UDEPT) REFERENCES X11_DEPT
                    
)
go

/* ---> Note <------------------------------------------------------------------------
   1) 부서는 TREE 구조로 관리된다
   2) 부서코드는 삭제할 수 없고 폐기구분으로 삭제를 대신한다 (업무 rule)
   3) LEVEL : TREE 구조를 표현하기 위한 참조 필드
   4) HIERARCHY : TREE 구조를 표현하기 위한 참조 필드
   5) 부서코드 테이블에는 TREE 구조 관리를 위해서 Trigger가 걸려있다
      (Trigger : insert event : TPSZZ80I1_X11_DEPT
                 update event : TPSZZ80U1_X11_DEPT
                 delete event : TPSZZ80D1_X11_DEPT  )
   6) 부서코드규칙
      - 규칙 :
-------------------------------------------------------------------------------------- */

/*=====================================================================================
   TRIGGER NAME : TPSZZ80I1_X11_DEPT 
   DEFINITION : 부서코드 TREE 구조로 관리하기 위한 트리거
 =====================================================================================*/

/*-------------------------------------------------------------------------------------*/
/* 부서코드 INSERT                                                                     */
/*-------------------------------------------------------------------------------------*/

CREATE TRIGGER TPSZZ80I1_X11_DEPT ON X11_DEPT FOR INSERT
AS
  DECLARE @NUMROWS  INT   
   
  SELECT  @NUMROWS = @@ROWCOUNT    /* INSERT 된 ROW 수 */
  IF @NUMROWS > 1
     BEGIN
        ROLLBACK TRIGGER WITH RAISERROR 40000, "You cannot insert more than one X11_DEPT at a time."
     END
  ELSE
  IF @NUMROWS = 1
     BEGIN
        UPDATE X11_DEPT
           SET SEQ_LEVEL    =  CASE D.CD_UDEPT WHEN D.CD_DEPT THEN 0 ELSE X.SEQ_LEVEL + 1 END
              ,TX_HIERARCHY =  CASE D.CD_UDEPT WHEN D.CD_DEPT THEN '.' ELSE X.TX_HIERARCHY END
                           + D.CD_DEPT + '.'
          FROM X11_DEPT D
              ,inserted I
              ,X11_DEPT X
         WHERE I.CD_DEPT   = D.CD_DEPT
           AND D.CD_UDEPT *= X.CD_DEPT
     END
GO   

/*-------------------------------------------------------------------------------------*/
/* 부서코드 UPDATE                                                                     */
/*-------------------------------------------------------------------------------------*/
   
CREATE TRIGGER TPSZZ80U1_X11_DEPT ON X11_DEPT FOR UPDATE
AS
 IF UPDATE(CD_DEPT)
 BEGIN
    ROLLBACK TRIGGER WITH RAISERROR 40001, "Updates to CD_DEPT not allowed!"
 END
 
 ELSE
 
 IF UPDATE(CD_UDEPT)
   BEGIN
      UPDATE X11_DEPT
         SET SEQ_LEVEL = D.SEQ_LEVEL - I.SEQ_LEVEL + CASE I.CD_UDEPT WHEN I.CD_DEPT THEN 0  ELSE X.SEQ_LEVEL + 1 END
              ,TX_HIERARCHY = ISNULL(X.TX_HIERARCHY, '.') + I.CD_DEPT + '.' + RIGHT(D.TX_HIERARCHY, CHAR_LENGTH(D.TX_HIERARCHY) - CHAR_LENGTH(I.TX_HIERARCHY))
         FROM X11_DEPT D
             ,inserted I
             ,X11_DEPT X
        WHERE D.TX_HIERARCHY LIKE I.TX_HIERARCHY + '%'
           AND I.CD_UDEPT *= X.CD_DEPT
  END

/*-------------------------------------------------------------------------------------*/
/* 부서코드 DELETE                                                                     */
/*-------------------------------------------------------------------------------------*/

CREATE TRIGGER TPSZZ80D1_X11_DEPT ON X11_DEPT FOR DELETE
AS
  DECLARE @NUMROWS     INT          /* DELETE 된 ROW수  */
  DECLARE @wCD_DEPT    VARCHAR(8)
  DECLARE @wCD_UDEPT   VARCHAR(8)
  DECLARE @wRET_STATUS INT 
 
  SELECT  @NUMROWS = @@ROWCOUNT
 
  IF @NUMROWS > 1
    BEGIN
        ROLLBACK TRIGGER WITH RAISERROR 40010, "You cannot delete more than one X11_DEPT at a time."
    END
   
  ELSE
 
  IF @NUMROWS = 1
     BEGIN

         SELECT @wCD_DEPT = CD_DEPT, @wCD_UDEPT = CD_UDEPT FROM deleted    
        
         EXEC @wRET_STATUS = PPSZZ31M1_DEPT_DELETE @wCD_DEPT, @wCD_UDEPT
        
         IF @wRET_STATUS <> 0
         BEGIN
            ROLLBACK TRIGGER WITH RAISERROR 40011, "You cannot delete!! "
         END

     END
GO

/*=====================================================================================
   PROCEDURE NAME : PPSZZ31M1_DEPT_DELETE                 
   DEFINITION : 부서삭제 프로시져

   EXEC PPSZZ31M1_DEPT_DELETE '부서코드', '상위부서코드'
 =====================================================================================*/

CREATE PROCEDURE PPSZZ31M1_DEPT_DELETE
    @pCD_DEPT                  VARCHAR(8),                   /* 부서코드      */
    @pCD_UDEPT                 VARCHAR(8)                    /* 상위부서코드  */
AS

    UPDATE X11_DEPT
       SET CD_UDEPT = @pCD_UDEPT
      FROM X11_DEPT D
          ,X11_DEPT X
     WHERE D.CD_UDEPT = X.CD_DEPT
       AND X.CD_DEPT  = @pCD_DEPT
    
    IF @@ERROR <> 0 RETURN -1 

    
    DELETE FROM X11_DEPT
     WHERE CD_DEPT = @pCD_DEPT   
    
    IF @@ERROR <> 0 RETURN -1
   
    RETURN 0

GO
      
      
//test

      
insert into X11_DEPT (CD_DEPT, NM_DEPT, CD_UDEPT) VALUES ('TOP','TOP','TOP')
insert into X11_DEPT (CD_DEPT, NM_DEPT, CD_UDEPT) VALUES ('LEVEL1','LEVEL1','TOP')
insert into X11_DEPT (CD_DEPT, NM_DEPT, CD_UDEPT) VALUES ('LEVEL2','LEVEL2','TOP')
insert into X11_DEPT (CD_DEPT, NM_DEPT, CD_UDEPT) VALUES ('LEVEL11','LEVEL11','LEVEL1')

select * from X11_DEPT where TX_HIERARCHY like '.TOP.%' order by TX_HIERARCHY