[오라클] DB모델링 , 1:1관계 , TRIGGER

##. DB 모델링의 요령 .##

 

1. 스토리(시나리오)를 작성한다.

2. 스토리(시나리오)에 맞도록 DB 모델링을 한다.

3. 최종으로 설계된 DB 모델링을 가지고 자바(닷넷)으로 프로그램을 작성한다.

  

 

<예> 영화예매를 온라인으로 가능하게끔 프로그램을 작성하고자 한다.

 

최과장이 부인과 아이와 함께 5세 이하 관람가능한

뽀로로를 종로극장 17:00시 2관에서 상영하는 영화예배를 하고자 한다.

성인2매, 아동 1매로 하려고 한다.

그런데 예배 전 5세 이상 관람 가능한 영화들이 무엇이 있는지

조회한 후 관전평을 보고 예매률 랭킹도 본 후 예매를 하려고 한다.

결재는 카드로, 휴대폰 결재 한다.

또한 포인트를 쌓으려고 영화사이트 회원으로 가입한다.

아님 비회원으로 예배를 할 수 있다.

 

 

## 1 : 1 관계 실습

  

 

<<TBL_MEMBER 테이블을 생성>>

CREATE TABLE TBL_MEMBER    --부모테이블 하나를 만든다.

(ID              VARCHAR2(10)

,PASSWD       VARCHAR2(10) NOT NULL

,NAME           VARCHAR2(10)

                 CONSTRAINT TBL_MEMBER_NAME_NN NOT NULL

                 --제약조건은 이름값(ID)에 NOT NULL 값으로

,MPHONE         VARCHAR2(15) --NULL

,CONSTRAINT  TBL_MEMBER_ID_PK PRIMARY KEY(ID) 

--제약조건은 멤버테이블명에 프라이머리키를 걸어둔다.

);

 

 

<<TBL_LOGIN 테이블 생성>>

CREATE TABLE TBL_LOGIN

(ID              VARCHAR2(10)

,PASSWD       VARCHAR2(10)

           CONSTRAINT TBL_LOGIN_PASSWD_NN NOT NULL

,CONSTRAINT  TBL_LOGIN_ID_PK  PRIMARY KEY(ID)

);

 

 

**************************************************************

 

▶▶ 제약조건의 종류 ◀◀

① Primary Key 제약조건  -- P

② Unique Key 제약조건  -- U

③ Foreign Key 제약조건  -- R

④ Check 제약조건       -- C

⑤ Not Null 제약조건     -- C

 

 

-- 제약조건을 조회

SELECT *

FROM USER_CONSTRAINTS

WHERE TABLE_NAME = 'TBL_MEMBER';

 

SELECT *

FROM USER_CONS_COLUMNS

WHERE TABLE_NAME = 'TBL_MEMBER';

 

**************************************************************

 

 

 

 

▶▶ TBL_MEMBER 테이블에 INSERT 되어진 행의 정보중 ID 컬럼과 PASSWD 컬럼의 값을

읽어다가 자동적으로 TBL_LOGIN 테이블에 INSERT 가 되도록 아래처럼 만든다. ◀◀

 

 

(     

   !!! 중요함!! 꼭 암기할 것 !!

           Trigger 내에서 insert 되어진 행의 정보는 :new 에 들어오고

           Trigger 내에서 delete 되어진 행의 정보는 :old 에 들어온다.

           그리고 Trigger 내에서 update 란 먼저 delete 가 실행되어진후

            insert 되어지는 것이다.

                                                                                   )

 

 

 

 

 

--TRIGGER 생성

CREATE OR REPLACE TRIGGER ATRI_TBL_MEMBER_INSERT

--이전 것이 있으면 만들고 아니면 교체해라

AFTER INSERT  --인써트 다음에

ON TBL_MEMBER

FOR EACH ROW  -- 위에 있는 TBL_MEMBER의 테이블의 값을 읽어 온다.

BEGIN

         INSERT INTO TBL_LOGIN VALUES

         (:NEW.ID, :NEW.PASSWD);

          --콜 뉴에 들어온 아이디 값과 콜론 뉴페스워드 값을 로그온에 넣어라

END;

-- ORA-01031: 권한이 불충분합니다

 

-- SYS에서 아래처럼 TRIGGER 를 생성할 수 있는 권한을 부여해준다.

GRANT CREATE TRIGGER TO NEWSCOTT;

 

 

***************************************************************************** 

 

INSERT INTO TBL_MEMBER

VALUES('HONGKD', 'JAVA302', '홍길동', '010-777-8888');

INSERT INTO TBL_MEMBER

VALUES('LEESS', 'SUPER777', '이순신', '011-333-5454');

 

-- ROLLBACK;

 

COMMIT;

 

*****************************************************************************  

--조회

SELECT *

FROM TBL_MEMBER;

 

SELECT *

FROM TBL_LOGIN;

 

***************************************************************************** 

 

 

## trigger로 패스워드 바꾸기 ##

 

( 우선 트리거를 생성 )

CREATE OR REPLACE TRIGGER ATRI_TBL_MEMBER_UPDATE -- 에프터 트리거

AFTER UPDATE OF PASSWD -- 패스워드 컬럼에만

ON TBL_MEMBER 

FOR EACH ROW

BEGIN

          UPDATE TBL_LOGIN

         SET PASSWD = :NEW.PASSWD

         WHERE ID = :OLD.ID; --:NEW.ID

END;

 

( 그리고 나서 바꿀 패드워드 설정 )

UPDATE TBL_MEMBER

SET PASSWD ='ORACLE302'

WHERE ID ='HONGKD';

   

 

COMMIT;

 

 

******************************************************************

 

## trigger로 지우기##

 

( 트리거를 생성 )

CREATE OR REPLACE TRIGGER ATRI_TBL_MEMBER_DELETE

AFTER DELETE

ON TBL_MEMBER

FOR EACH ROW

BEGIN

         DELETE TBL_LOGIN

         WHERE ID = :OLD.ID;

END;

 

( 그리고 지울 아이디 설정 )

DELETE TBL_MEMBER

WHERE ID ='HONGKD';

 

COMMIT;

 

 

***************************************************************

 

 

## 부모 컬럼을 지우면 자식 컬럼도 지워진다. ##

 

<< TBL_NEWMEMBER 테이블 생성 >>

CREATE TABLE TBL_NEWMEMBER

(ID                VARCHAR2(10)

,PASSWD          VARCHAR2(10) NOT NULL

,NAME             VARCHAR2(10)

CONSTRAINT TBL_NEWMEMBER_NAME_NN NOT NULL

,MPHONE         VARCHAR2(15) --NULL

,CONSTRAINT TBL_NEWMEMBER_ID_PK PRIMARY KEY(ID)

);

 

<< TBL_NEWLOGIN 테이블 생성 >>

CREATE TABLE TBL_NEWLOGIN

(ID                VARCHAR2(10)

,PASSWD          VARCHAR2(10)

CONSTRAINT TBL_NEWLOGIN_PASSWD_NN NOT NULL

,CONSTRAINT  TBL_NEWLOGIN_ID_PK  PRIMARY KEY(ID)

,CONSTRAINT TBL_NEWLOGIN_ID_FK FOREIGN KEY(ID)

            REFERENCES TBL_NEWMEMBER(ID)  -- 참조한다. 이것은 1:다 관계

ON DELETE CASCADE  --부모의 컬럼의 행을 지우면 자동적으로 자식 행도 지워진다.

                          -- 1:1관계에서만 쓰는 것(1:다 관계에서는 안된다.)

);                  

 

****************************************************************

 

INSERT INTO TBL_NEWMEMBER VALUES

('YOUKS','JAVA302$','유관순','010-999-8888');

INSERT INTO TBL_NEWMEMBER VALUES

('AHNJK','ABC302$','안중근','011-333-8888');

 

 

 

INSERT INTO TBL_NEWLOGIN VALUES

('YOUKS','JAVA302$');

INSERT INTO TBL_NEWLOGIN VALUES

('AHNJK','ABC302$');

 

COMMIT;

 

****************************************************************

 

 

--조회

SELECT *

FROM TBL_NEWMEMBER;

 

SELECT *

FROM TBL_NEWLOGIN;

 

****************************************************************

 

-- 컬럼지움 ( 두개가 동시에 지워짐 )

DELETE TBL_NEWMEMBER

WHERE ID = 'AHNJK';.