농부지기

[ Oracle - Trigger 문법_기본구문 ]

create [ or replace ] trigger [user.]tirgger { before | after | instead of } { delete | insert | update [ of column [, column]….] } [ or { delete | insert | update [ of column [, column ]…] } ] on [user.] { TABLE | VIEW } [ [ referencing { old [as] old | new [as] new } .. ] for each { row | statement } { when ( condition ) ] ] pl/sql_block

해법

- before and after : 트리거가 트리거 트랜잭션 전후에 실행 되어야 하는지 의 여부를 알려준다.

- instead of : 트리거를 실행되도록 하는 이벤트 대신에 ‘트리거의 코드’를 실행

- delete , insert, update : 트리거 이벤트를 구성할 수 데이터 변형의 유형을 가리키다.

   이전 열 값과 새로운 열 값을 참조할 때, 다른 이름을 지정하기 위해서 디폴트(‘old’, ‘new’) 를 사용하거나

   referencing 저을 사용 할 수 있다.

- for each row : 트리거를 행 레벨 트리거로 만든다.

- when : 트리거가 실행 될 때 더 규제하기 위해 사용. When 절에서 시행되는 규제는 과거와 현재의

              데이터 값의 규제를 포함한다.

             - 예, when ( new.Amount  > old.Amount )   : 신규 Amount  값이 이전값보다 클 경우에만 트리거 실행

- RAISE_APPLICATION_ERROR  프로시저 를 통해서 사용자에게 에러숫자와 메시지를 보여준다.

  (예, raise_application_error (error코드, ‘Message내용’ )

  error코드 : -2001 ~ 20999 사이

예) declare weekend_error EXCEPTION; not_finance_user EXCEPTIONL begin IF…. RAISE weekend_error; ….. RAISE not_finance_user; …. ……. EXCEPTION WHEN weekend_error THEN RAISE_APPLICATION_ERROR(-2001, ‘……’); WHEN not_finance_user THEN RAISE_APPLICATION_ERROR(-2002, ‘……’); end; 설명 ) - EXCEPTION : 예외 각각에 대해서 when 절이 나타난다. - 한 Trigger 활성화/비활성화 하기 : alter trigger trigger_name enable/disable ; table table_name enable/disable all triggers; - 모든 Trigger 활성화 하기 : table - Trigger 삭제 : drop trigger trigger_name;

1. Select 1 from …. ; exception when NO_DATA_FOUND then 변수 := 값; when others then rollback; raise_application_error(-20001,sqlerrm(sqlcode)); - NO_DATA_FOUND : select 에 대한 레코드가 존재 하지 않을 때…. 변수에 값을 setting 하라. - OTHERS : 기타 (즉 Error) 일 경우 rollback; error 조회 ) 2. if Deleting or Updating('JS_AMT10') then Updatting 후 괄호 안에 있는 것은 해당 테이블에서 해당컬럼(괄호안 컬럼) 이 수정 되었는지 에 대해서… 3. declare E_DEL_ERROR exception; begin … raise E_DEL_ERROR; … exception when E_DEL_ERROR then raise_application_error(-20002,'마감상태에서 삭제할 후 없습니다!!!'); when others then raise_application_error(-20001,sqlerrm(sqlcode)); 4. declare xx_totamt PAY.DEPOSIT.DEPO_TOTAMT%TYPE; 자료 정의 시 해당 변수의 type 은 해당 컬럼의 형식으로 한다.

[ Oracle - Trigger 특징 ]

1. Trigger Body 는 pl/sql 로 작성

2. 트리거는 유연성 때문에 참조 무결성을 보완 할 수 있다.

3. 트리거를 생성시 사용자는 그 테이블에 권한을 가지고 있어야 한다.

4.사용자는 CREATE TIRGGER 시스템권한을 가져야 한다.

    다른 사용자의 계정(Schema라고 하기도 한다)상에서 트리거를 만들기 위해서 사용자는

    Create any trigger 시스템 권한을 가져야 한다.

5. 트리거 소유주는 그 테이블의 권한을 부여받아야 한다.

6. 트리거 이름은 30자 이내

7. Trigger 는 본문만 변경 할 수 없다. 트리거의 상태(enable/disable)만이 변경 할 수 있는 유일한 부분이다.

    변경하기 위해서는 Trigger 를 다시 만들어서 대체 해야한다.

8. Trigger를 실행할 때 Trigger 내의 SQL 명령문은 잠정적으로 다른 Trigger를 실행할 수 있음

9.

[ Oracle - Trigger 특징_Trigger유형 ]

트리거 유형

1. Row-level(행 레벨) 트리거 : 하나의 트랙잭션에서 각 행에 대해 실행..

     for each row 절을 사용하면 된다.

2. Statement-level(문장 레벨)트리거 : 각 트랜잭션을 위해 한 번 시행 한다.

    디폴트 유형의 트리거 이다.

3. Before  트리거와 After 트리거 :

     새로운 값을 액세스하기 위해 BEFORE INSERT 트리거를 사용

4. INSTEAD OF 트리거

   뷰가 두 개의 테이블의 조인을 포함하는 경우 레크드에 UPDATE 명령을 사용할 수 있는 능력을 제한된다.

 그러나 INSTEAD OF 트리거를 사용하는 경우 사용자가 뷰를 통해 값을 변경하고자 할 때 뷰의 기저 테이블에

 레코드를 업데이트하거나 삭제 하거나 또는 삽입하는 방법을 ORACLE 에게 알려 줄 수 있다.

유효한 트리거 유형

   1. BEFORE INSERT 행

   2. BEFORE INSERT 문

   3. AFTER INSERT 행

   4. AFTER INSERT 문

   5. BEFORE UPDATE 행

   6. BEFORE UPDATE 문

   7. AFTER UPDATE 행

   8 . AFTER UPDATE 문

   9. BEFORE DELETE 행

   10. BEFORE DELETE 문

   11. AFTER DELETE 행

   12. AFTER DELETE 문

   13. INSTEAD OF 행

   14. INSTEAD OF 문

[ Oracle - Function 공통.두 날자의 년수를 구함 ]

CREATE OR REPLACE FUNCTION OF_COMM_YEAR_CALU ( aSta_ymd VARCHAR2, --시작일자 aEnd_ymd VARCHAR2, --종료일자 aDay_Add_yn VARCHAR2, --처음일자 포함여부 aRound_gubn VARCHAR2 --반올림 여부 (1:올림, 2:반올림, 3:버림) ) RETURN VARCHAR2 IS /**************************************************************************************** PROCEDURE NAME : OF_COMM_YEAR_CALU DESCRIPTION : 두 날자의 년수를 구함 AUTHOR : FARMER-KYH CREATE DATE : 2008.01.17 HISTORY : *****************************************************************************************/ nMonth NUMBER(10,5) := ''; --개월수 BEGIN dbms_output.enable; --처음일자 or 종료일자 포함여부 IF aDay_Add_yn = 'Y' THEN nMonth := months_between(to_char(to_date(aEnd_ymd) + 1, 'yyyymmdd'), aSta_ymd) / 12; ELSE nMonth := months_between(to_char(to_date(aEnd_ymd), 'yyyymmdd'), aSta_ymd) / 12; END IF; --반올림여부 --올림 IF aRound_gubn = '1' THEN nMonth := ROUND(nMonth + 0.4, 0) ; --반올림 ELSIF aRound_gubn = '2' THEN nMonth := ROUND(nMonth, 0) ; --버림 ELSE nMonth := TRUNC(nMonth, 0) ; END IF; RETURN nMonth; EXCEPTION WHEN OTHERS THEN RETURN 0; END;

[ Oracle - Function 공통.두 날자의 개월수를 구함 ]

[ Oracle - Function 공통.당월 말 구하기 ]

[ Oracle - Function 공통.두 날자 차이 구하기 ]

[ Oracle - Function 월 더하기.빼기 ]

[ Oracle - Function 예제2 ]

[ Oracle - Function 예제1 ]

Stored Function Sample 2 /* SAL601T에서 거래처별 금월판매액 */ CREATE OR REPLACE FUNCTION DF_SAL601T_AMT_G (df_vndno in sal601t.vndno%type, df_yy in sal601t.yy%type, df_mm in sal109t.mm%type ) RETURN number is out_num number(12); BEGIN IF df_mm = '01' THEN SELECT m01_gum + m01_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '02' THEN SELECT m02_gum + m02_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '03' THEN SELECT m03_gum + m03_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '04' THEN SELECTm04_gum + m04_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '05' THEN SELECT m05_gum + m05_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '06' THEN SELECT m06_gum + m06_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '07' THEN SELECT m07_gum + m07_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '08' THEN SELECT m08_gum + m08_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '09' THEN SELECT m09_gum + m09_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '10' THEN SELECT m10_gum + m10_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '11' THEN SELECT m11_gum + m11_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; ELSIF df_mm = '12' THEN SELECT m12_gum + m12_vat INTO out_num FROM sal601t WHERE vndno = df_vndno AND yy = df_yy ; END IF ; RETURN(out_num); EXCEPTION WHEN NO_DATA_FOUND THEN RETURN (' '); END; / 발주처 : http://myhome.netsgo.com/mino70/main_03_oracle03.htm