Oracle(트리거생성,UPDATE,INSERT)

 일단 트리거를 쓰는 이유, 고려할점에 대해서 구글링 해본 내용등를 보자 !

 

 

활용

 1.데이터 무결성의 강화

  -전체 데이터베이스에 걸쳐 영향 받은 테이블을 연속적으로 변경함으로서 데이터 무결성을 강화하는데 사용될 수 있다.

  - 중복되었거나 파생된 데이터에 대해 트리거를 사용하는 것이 좋다.

 

 

2.참조 무결성의 강화

  -연속적인 삭제 / 갱신이 일어날 필요가 있을 때 적절한 작업을 하도록 보장하는데 유용하다.

  - FOREIGN KEY, REFERENCE 제약을 사용할 수도 있다.

  - 자신의 오류 메시지를 정의할 수 있다.

 

3.업무 규칙의 설정

  - 제약에 의해 표현될 수 있는 것보다 복잡한 제약을 표현할 수 있다.

 

4.감사기능의확장

  -상세한 변경 내역을 추적하려면, 트리거를 적용하거나 Log Miner를 이용해 아카이브 로그를 뒤지는 수 밖에 없음

  -감사 목적으로 적용되는 트리거는 성능적으로 심각한 부담을 줍니다

 

고려사항

       - 트리거는 각 테이블에 최대 3개까지 존재할 수 있다.

         UPDATE, INSERT, DELETE트리거가 각각 하나씩 존재할 수 있다.

         이미 트리거가 정의된 작업에 대해 다른 트리거를 정의하면 기존의 것을 대체한다.

       - 테이블의 소유자만이 트리거를 생성하거나 삭제할 수 있다.

       - 뷰나 임시 테이블을 참조할 수 있으나 뷰나 임시 테이블에 대해 생성할 수 는 없다.

       - 데이터 무결성이나 업무 규칙 처리에만 사용하는 것이 좋다.

       - 트리거의 동작은 이를 삭제하지 않는 한 계속된다.

       - 테이블의 삭제는 관련 트리거의 삭제를 유발한다

 

levin01.tistory.com/1156

트리거(trigger)에 대해서...

 

추가되어 있던 회원들이 아이디를 변경하면 게시판에도 변경되어야 하니까 트리거를 사용했다 원래는 아이디를 변경하지 않지만 구냥.. 음 해봤다. 

 

TRIGGER(UPDATE)

 

CREATE [OR REPLACE] TRIGGER 트리거명

BEFORE or AFTER

트리거이벤트 ON 테이블명

[FOR EACH ROW [WHEN TRIGGER 조건]]

[Referencing OLD AS {변경전 값을 참조하는 변수명} NEW AS {변경후 값을 참조하는 변수명}]
DECLARE

선언문

BEGIN

PL/SQL 코드

END;

 

트리거명 : 자신이 원하는 트리거명

트리거이벤트 : insert,update,delete 중에서 자신이 트리거를 적용하기 원하는 곳( OR을 사용해서 여러개 가능 ex. insert or update )

테이블명 : 트리거를 적용시킬 테이블 명

 

[FOR EACH ROW [WHEN TRIGGER 조건]]

FOR EACH ROW 를 쓰면 row(행) 트리거를 생성하고 WHEN 조건을 주면 WHEN 조건에 만족하는 ROW(행)만 트리거 적용 가능.

즉,FOR EACH ROW를 안쓰면 statement(문장) 트리거 생성.

[Referencing OLD AS {변경전 값을 참조하는 변수명} NEW AS {변경후 값을 참조하는 변수명}]

row(행)트리거 일때, 트리거에 영향 받는 행들의 값을 참조할때 사용(statement(문장) 트리거에는 사용 못함).

{변경전 값을 참조하는 변수명} ,{변경후 값을 참조하는 변수명} 의 기본 변수명

:OLD = 참조 전 열의 값 (INSERT : 입력 전 자료, UPDATE : 수정 전 자료, DELETE : 삭제할 자료)

:NEW = 참조 후 열의 값 (INSERT : 입력 할 자료, UPDATE : 수정할 자료)

위에 구문을 보고  트리거를 작성해보면 오류가 난다.. ㅠ

 

create or replace TRIGGER MEMBER_UPDATE
AFTER UPDATE 
ON TBL_MEMBER
FOR EACH ROW
BEGIN
 
  dbms_output.put_line('회원정보가 수정 됩니다.');
  UPDATE TBL_BOARD 
  SET MEMBER_WRITER=:NEW.MEMBER_NAME
  WHERE MEMBER_WRITER=:OLD.MEMBER_NAME;
  UPDATE TBL_BOARD_RE 
  SET RE_WRITER=:NEW.MEMBER_NAME 
  WHERE RE_WRITER=:OLD.MEMBER_NAME;
  UPDATE TBL_CUSTOMER_MANAGER
  SET CUSTOMER_MEMBER_NAME=:NEW.CUSTOMER_MEMBER_NAME,
      CUSTOMER_MEMBER_PASSWORD=:NEW.CUSTOMER_MEMBER_PASSWORD,
      CUSTOMER_MEMBER_ROLE=:NEW.CUSTOMER_MEMBER_ROLE,
      CUSTOMER_MEMBER_EMAIL=:NEW.CUSTOMER_MEMBER_EMAIL,
      CUSTOMER_MEMBER_ADDRES=:NEW.CUSTOMER_MEMBER_ADDRES
  WHERE CUSTOMER_MEMBER_NAME=:OLD.CUSTOMER_MEMBER_NAME;
  
END;

잘못된 바인드 변수 라고 나온다.. 분명히 테이블에 컬럼이 있는데..ㅠㅠ 왜 자꾸 그러니.. 어디가 문제인거니.... 그래서 결국 다시 검색을 해보쟈규.. 구글링 하가  UPDATE OF 를 봤다 그래서 변경되는 컬럼을 작성해주고 그 컬럼을 UPDATE에 작성해봤다.

 

create or replace TRIGGER MEMBER_UPDATE
AFTER UPDATE OF MEMBER_NAME,MEMBER_PASSWORD,MEMBER_ROLE,MEMBER_EMAIL,MEMBER_ADDRES
ON TBL_MEMBER
FOR EACH ROW
BEGIN
 
  dbms_output.put_line('회원정보가 수정 됩니다.');
  UPDATE TBL_BOARD 
  SET MEMBER_WRITER=:NEW.MEMBER_NAME
  WHERE MEMBER_WRITER=:OLD.MEMBER_NAME;
  UPDATE TBL_BOARD_RE 
  SET RE_WRITER=:NEW.MEMBER_NAME 
  WHERE RE_WRITER=:OLD.MEMBER_NAME;
  UPDATE TBL_CUSTOMER_MANAGER
  SET CUSTOMER_MEMBER_NAME=:NEW.MEMBER_NAME,
      CUSTOMER_MEMBER_PASSWORD=:NEW.MEMBER_PASSWORD,
      CUSTOMER_MEMBER_ROLE=:NEW.MEMBER_ROLE,
      CUSTOMER_MEMBER_EMAIL=:NEW.MEMBER_EMAIL,
      CUSTOMER_MEMBER_ADDRES=:NEW.MEMBER_ADDRES
  WHERE CUSTOMER_MEMBER_NAME=:OLD.MEMBER_NAME;
  
END;

이렇게 하면 오류가 없다 구문을 설명하면 멤버에서 아이디,비밀번호.권한.주소 가 변경될때 게시판과 댓글에 적성된 아이디가 변경되고

어드민 테이블에는 모두가 변경될수 있도록 했다 구글링 보면 이런경우가 없던데.. 내가 잘못찾은건가?.. 오잉??.. 결국 찾아서 하긴했는데 찜찜하다 ㅜ 

 

SELECT * FROM TBL_MEMBER;
SELECT * FROM TBL_BOARD;
SELECT * FROM TBL_BOARD_RE;
SELECT * FROM TBL_CUSTOMER_MANAGER;

UPDATE TBL_MEMBER
SET MEMBER_NAME ='member5'
WHERE MEMBER_NAME='member2';

일단 현재 테이블에 member2라는 데이터가 있다

업데이트를 통해 수정된 데이터를 한번 보자 결과를 보면 'member5' 로 수정되어야 한다

 

오? 깔끔하게 됬당 

 

TRIGGER(INSERT)

 

인서트도 다를거 없다 한번보자

CREATE OR REPLACE TRIGGER CUSTOMER_INSERT 
AFTER INSERT 
ON TBL_MEMBER
FOR EACH ROW
BEGIN
   
    INSERT INTO TBL_CUSTOMER_MANAGER 
    (CUSTOMER_MEMBER_NAME,
      CUSTOMER_MEMBER_PASSWORD,
      CUSTOMER_MEMBER_ROLE,
      CUSTOMER_MEMBER_EMAIL,
      CUSTOMER_MEMBER_ADDRES,
      CUSTOMER_MEMBER_AGE)
      VALUES
      (:NEW.MEMBER_NAME,
             :NEW.MEMBER_PASSWORD,
             :NEW.MEMBER_ROLE,
             :NEW.MEMBER_EMAIL,
             :NEW.MEMBER_ADDRES,
             :NEW.MEMBER_AGE);
      
END;

 

구문을 설명하자면 TBL_MEMBER가 추가 되면 admin 테이블에도 추가될수 있도록 추가 했다 여기서 TBL_CUSTOMER_MANAGER의 컬럼값이 아니라 TBL_MEMBER 컬럼값을 작성해야 한다. 처음에는 TBL_CUSTOMER_MANAGER 컬럼값을 넣고 왜 안되냐고.. 하면서 시간을 보냈다 

데이터를 확인해보자 

 

 INSERT INTO TBL_MEMBER VALUES('member2','1','ROLE_USER','SDF@SDF.COM','DF','1999',SYSDATE);

TBL_MEMBER 테이블에 멤버를 인서트 해보자 

 

 

멤버2가 추가되었다. 그러면 트리거 실행됐을까?

확인해보자

오? 정상 이다!