-- ------------------------------------------------------------------------------------------------
-- [ 인사관리 ] 조직도상세 테이블 생성
conn system/built123$@ndb;
-- ------------------------------------------------------------------------------------------------
-- 테이블 생성
drop table bhrm.hrm_dept_hierarchy_detail cascade constraints;
create table bhrm.hrm_dept_hierarchy_detail
(
dept_hierarchy_detail_id number not null
,dept_hierarchy_id number not null
,parent_dept_id number not null
,child_dept_id number not null
,sort_order number default 1
,create_date timestamp default systimestamp
,create_by varchar2(50) default '-1'
,update_date timestamp default systimestamp
,update_by varchar2(50) default '-1'
) tablespace bdb_data;
-- 테이블 설명
comment on table bhrm.hrm_dept_hierarchy_detail is '[인사관리] 조직도상세';
comment on column bhrm.hrm_dept_hierarchy_detail.dept_hierarchy_detail_id is '조직도상세ID';
comment on column bhrm.hrm_dept_hierarchy_detail.dept_hierarchy_id is '조직도ID';
comment on column bhrm.hrm_dept_hierarchy_detail.parent_dept_id is '상위부서ID';
comment on column bhrm.hrm_dept_hierarchy_detail.child_dept_id is '하위부서ID';
comment on column bhrm.hrm_dept_hierarchy_detail.sort_order is '정렬순서';
comment on column bhrm.hrm_dept_hierarchy_detail.create_date is '생성일시';
comment on column bhrm.hrm_dept_hierarchy_detail.create_by is '생성자';
comment on column bhrm.hrm_dept_hierarchy_detail.update_date is '수정일시';
comment on column bhrm.hrm_dept_hierarchy_detail.update_by is '수정자';
-- primary 인덱스
alter table bhrm.hrm_dept_hierarchy_detail add constraint hrm_dept_hierarchy_detail_pk primary key (dept_hierarchy_detail_id) using index tablespace bdb_idx;
-- unique 인덱스
create unique index bhrm.hrm_dept_hierarchy_detail_uk01 on bhrm.hrm_dept_hierarchy_detail (dept_hierarchy_id, parent_dept_id, child_dept_id) tablespace bdb_idx;
-- 권한부여
grant select, insert, update, delete on bhrm.hrm_dept_hierarchy_detail to bsys with grant option;
grant select, insert, update, delete on bhrm.hrm_dept_hierarchy_detail to bcom with grant option;
-- 트리거 생성
create or replace trigger bhrm.hrm_dept_hierarchy_detail_trg
before insert or update on bhrm.hrm_dept_hierarchy_detail
for each row
begin
if :new.dept_hierarchy_detail_id is null then
:new.dept_hierarchy_detail_id := hrm_dept_hierarchy_detail_s.nextval;
end if;
if inserting then
:new.create_date := systimestamp;
:new.create_by := nvl(wwv_flow.g_user, user);
end if;
:new.update_date := systimestamp;
:new.update_by := nvl(wwv_flow.g_user, user);
end;
/
-- 시노님 생성
conn bsys/1234@ndb;
create or replace synonym hrm_dept_hierarchy_detail for bhrm.hrm_dept_hierarchy_detail;
conn bcom/1234@ndb;
create or replace synonym hrm_dept_hierarchy_detail for bhrm.hrm_dept_hierarchy_detail;
'오라클 > 테이블(인사관리)' 카테고리의 다른 글
[ 임직원기본 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |
---|---|
[ 임직원 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |
[ 조직도 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |
[ 사업장 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |
[ 사업장 ] 테이블 생성을 아래와 같이 진행합니다. (0) | 2024.04.12 |