[Oracle 기초강좌 - 10] DataBase Trigger

[Trigger] DataBase Trigger 1. Trigger란? - 임의의 Table에 대해 Insert/Update/Delete 등의 SQL이 실행되면 암시적으로 실행되는 프로시저 - Trigger는 관련된 Table과는 별개로 DB내에 저장된다. - Trigger는 뷰가 아닌 Table에 대해서만 정의가 가능하다. 그러나 View의 기본 Table에 있는 Trigger는 View에 대해 Insert/Update/Delete 가 발생하게 되면 동작한다. ** Oracle Forms에서의 Trigger와는 구분되어야 한다. Forms에서 애기되는 Trigger는 일반 Tools(VB,,,)의 Event와 같은 개념이다. 즉 Oracle Forms 응용프로그램의 한부분이며 특정 Trigger 지점이 특정 Forms 응용프로그램 내에서 실행될때만 수행된다. 그와반해 DB Trigger는 Table에 대해 Insert/Update/Delete시 할 행위를 정의하는 것이다. 2. Trigger의 구성 a. Triggering Event 또는 명령문 - Trigger를 실행시키는 SQL 명령 - 예를들면 아래와 같은것이 해당된다. create trigger 다음의 bdfore update of amount on sales.... 이는 sales Table에 대해 amount column의 변경될때 동작하는 Trigger를 만든다는 의미이다. b. Trigger 제한사항 - 트리거 제한사항의 TRUE일때 Trigger는 동작한다. - 각 행당 실행되는 트리거에 대해 사용가능한 Option으로 조건에 따라 Trigger 실행을 제어한다. when (new.amount < old.amount) 즉 이같은 조건은 갱신되는 금액이 이전의 값보다 작을때 Trigger를 실행하라는 의미이다. c. Trigger 작업 - PL/SQL Code를 포한하는 Trigger 처리부 d. Trigger 유형 - 행 트리거 에를들면 Update 명령이 Table의 여러행에 실행될때 행 트리거는 Update 명령에 의해 영향을 받는 각행에 한번씩 실행된다. - 명령문 트리거 Table에 대해 명령에 영향을 받는 행에 관계없이 한번 실행된다. e. Before대 after Trigger - Before Trigger 명령문이 수행되기 전에 실행되는 Trigger Insert또는 Update 명령문을 완료하기전에 특정 열의 값을 구할때 사용한다. - After Trigger 명령문이 수행된 후에 실행되는 Trigger f. Trigger예제 아래의 Trigger는 Statement Trigger임(Statement Trigger) Oracle Tip에 Trigger Mutation Error처리예가 있는데 아래의 Trigger도 처음 작성시 sa_contrct Table에 대해 Mutating Error가 발생하여 Package를 만들어 구성한 예이다. Mutate Error에 대해 궁굼하다면 Oracle Tip을 살펴보시면 설면해 놓았습니다. /* ********************************** */ /* 계약변경시 매출,입금원장 변경 */ /* ********************************** */ create or replace trigger tr_sa_contrct after insert or update on sa_contrct declare begin for i in 1..pkg_sa_contrct.sa_contrct_index loop if inserting then /************* 매출원장 생성 **************/ insert into sa_ledger (contrct_ilja, emp_id, cust_cd, brand_cd, adv_cd, contrct_gbn, supply_amt, receipt_amt, regi_ilja, modi_ilja, misu) values (pkg_sa_contrct.contrct_ilja_new(i), pkg_sa_contrct.emp_id_new(i), pkg_sa_contrct.cust_cd_new(i), pkg_sa_contrct.brand_cd_new(i), pkg_sa_contrct.adv_cd_new(i), pkg_sa_contrct.contrct_gbn_new(i), pkg_sa_contrct.amount_new(i), pkg_sa_contrct.contrct_amt_new(i), to_char(sysdate,'yyyymmdd'), to_char(sysdate,'yyyymmdd'), pkg_sa_contrct.amount_new(i) - pkg_sa_contrct.contrct_amt_new(i) ); end if; if updating then update sa_ledger set contrct_ilja = pkg_sa_contrct.contrct_ilja_new(i) , emp_id = pkg_sa_contrct.emp_id_new(i) , cust_cd = pkg_sa_contrct.cust_cd_new(i) , brand_cd = pkg_sa_contrct.brand_cd_new(i) , adv_cd = pkg_sa_contrct.adv_cd_new(i) , contrct_gbn = pkg_sa_contrct.contrct_gbn_new(i) , supply_amt = pkg_sa_contrct.amount_new(i) , receipt_amt = receipt_amt - (pkg_sa_contrct.contrct_amt_old(i) - pkg_sa_contrct.contrct_amt_new(i)), modi_ilja = to_char(sysdate,'yyyymmdd') , misu = misu - (pkg_sa_contrct.amount_new(i) - pkg_sa_contrct.contrct_amt_old(i)) where contrct_ilja = pkg_sa_contrct.contrct_ilja_old(i) and emp_id = pkg_sa_contrct.emp_id_old(i) and cust_cd = pkg_sa_contrct.cust_cd_old(i) and brand_cd = pkg_sa_contrct.brand_cd_old(i) and adv_cd = pkg_sa_contrct.adv_cd_old(i) and contrct_gbn = pkg_sa_contrct.contrct_gbn_old(i); end if; end loop; pkg_sa_contrct.sa_contrct_index := 0; end; / create or replace package pkg_sa_contrct as type contrct_ilja is table of sa_contrct.contrct_ilja%type index by binary_integer; type emp_id is table of sa_contrct.emp_id%type index by binary_integer; type cust_cd is table of sa_contrct.cust_cd%type index by binary_integer; type brand_cd is table of sa_contrct.brand_cd%type index by binary_integer; type adv_cd is table of sa_contrct.adv_cd%type index by binary_integer; type contrct_gbn is table of sa_contrct.contrct_gbn%type index by binary_integer; type amount is table of sa_contrct.amount%type index by binary_integer; type contrct_amt is table of sa_contrct.contrct_amt%type index by binary_integer; type ipgum_cd is table of sa_contrct.ipgum_cd%type index by binary_integer; type card_gbn is table of sa_contrct.card_gbn%type index by binary_integer; type nurak_amt is table of sa_contrct.nurak_amt%type index by binary_integer; sa_contrct_index binary_integer; contrct_ilja_old contrct_ilja; emp_id_old emp_id; cust_cd_old cust_cd; adv_cd_old adv_cd; brand_cd_old brand_cd; contrct_gbn_old contrct_gbn; amount_old amount; contrct_amt_old contrct_amt; ipgum_cd_old ipgum_cd; card_gbn_old card_gbn; nurak_amt_old nurak_amt; contrct_ilja_new contrct_ilja; emp_id_new emp_id; cust_cd_new cust_cd; adv_cd_new adv_cd; brand_cd_new brand_cd; contrct_gbn_new contrct_gbn; amount_new amount; contrct_amt_new contrct_amt; ipgum_cd_new ipgum_cd; card_gbn_new card_gbn; nurak_amt_new nurak_amt; end pkg_sa_contrct; / create or replace trigger tr_aft_sa_contrct after insert or update on sa_contrct for each row begin pkg_sa_contrct.sa_contrct_index := pkg_sa_contrct.sa_contrct_index + 1; pkg_sa_contrct.contrct_ilja_old(pkg_sa_contrct.sa_contrct_index) := :old.contrct_ilja; pkg_sa_contrct.contrct_ilja_new(pkg_sa_contrct.sa_contrct_index) := :new.contrct_ilja; pkg_sa_contrct.emp_id_old(pkg_sa_contrct.sa_contrct_index) := :old.emp_id; pkg_sa_contrct.emp_id_new(pkg_sa_contrct.sa_contrct_index) := :new.emp_id; pkg_sa_contrct.cust_cd_old(pkg_sa_contrct.sa_contrct_index) := :old.cust_cd; pkg_sa_contrct.cust_cd_new(pkg_sa_contrct.sa_contrct_index) := :new.cust_cd; pkg_sa_contrct.adv_cd_old(pkg_sa_contrct.sa_contrct_index) := :old.adv_cd; pkg_sa_contrct.adv_cd_new(pkg_sa_contrct.sa_contrct_index) := :new.adv_cd; pkg_sa_contrct.brand_cd_old(pkg_sa_contrct.sa_contrct_index) := :old.brand_cd; pkg_sa_contrct.brand_cd_new(pkg_sa_contrct.sa_contrct_index) := :new.brand_cd; pkg_sa_contrct.contrct_gbn_old(pkg_sa_contrct.sa_contrct_index) := :old.contrct_gbn; pkg_sa_contrct.contrct_gbn_new(pkg_sa_contrct.sa_contrct_index) := :new.contrct_gbn; pkg_sa_contrct.amount_old(pkg_sa_contrct.sa_contrct_index) := :old.amount; pkg_sa_contrct.amount_new(pkg_sa_contrct.sa_contrct_index) := :new.amount; pkg_sa_contrct.contrct_amt_old(pkg_sa_contrct.sa_contrct_index) := :old.contrct_amt; pkg_sa_contrct.contrct_amt_new(pkg_sa_contrct.sa_contrct_index) := :new.contrct_amt; pkg_sa_contrct.ipgum_cd_old(pkg_sa_contrct.sa_contrct_index) := :old.ipgum_cd; pkg_sa_contrct.ipgum_cd_new(pkg_sa_contrct.sa_contrct_index) := :new.ipgum_cd; pkg_sa_contrct.card_gbn_old(pkg_sa_contrct.sa_contrct_index) := :old.card_gbn; pkg_sa_contrct.card_gbn_new(pkg_sa_contrct.sa_contrct_index) := :new.card_gbn; pkg_sa_contrct.nurak_amt_old(pkg_sa_contrct.sa_contrct_index) := :old.nurak_amt; pkg_sa_contrct.nurak_amt_new(pkg_sa_contrct.sa_contrct_index) := :new.nurak_amt; end; / create or replace trigger tr_bef_sa_contrct before insert or update on sa_contrct begin pkg_sa_contrct.sa_contrct_index := 0; end; / [Data Dictionary]데이타 딕셔너리 1. Data Dictionary 란? - DB의 가장 중요한 부분의 하나로 Data Dictionat에는 연관된 DataBase 정보를 제공하는 일기전용 Table의 집합이다. 아래와 같은 정보들로 구성된다. Oracle 사용자 이름 권한과 롤 Schema 개체이름(Table, View, Snapshot, index, cluster, Synonym,,,) 무결성제약 조건에 관한 정보 Column에 대한 기본값 DB의 개체에 대한 할당된 영역과 현재 사용중인 양 기타 일반적인 DataBase 정보 2. 구성 - 기본 Table과 사용자가 Access 할수있는 View로 구성된다. - 기본 Table 연관된 DB의 정보를 저장하는 Table Oracle 만이 Read/Write가 가능하며 사용자가 직접 Access할수 없다. - 사용자 Access 가능 View Dictionary 기본 Table의 내용을 요약하여 편리하게 보여줌 3. Dictionary의 소유자 SYS - Oracle 사용자 SYS는 데이타 딕셔널리의 모든 기본 Table과 View를 소유한다. - 기본 Dictionary Table의 내용을 변경하면 DataBase에 치명적인 악영향을 끼칠수 있다. 4. Dictionary 작용 - Oracle은 DDL 명령이 실행될때 마다 Data Dictionary를 Access한다. - 모든 Oracle 사용자는 DB정보에 대한 읽기 전용 참조로 Data Dictionary 사용할 수 있다. - DB작업동안 Oracle은 Data Dictionary를 읽어 개체의 존재여부와 사용자에게 적 합한 Access 권한이 있는지 확인한다. 또한 Oracle은 Data Dictionary를 계속 갱신하여 DataBAse 구조, 감사, 사용자권한,데이터등의 변경사항을 반영한다. - DataBase 작업동안 Oracle은 항상 Data Dictionary에 Access하여 사용자 Access권한을 확인하고 개체 상태를 확인 하므로 Data Dictionary의 많은 정보 가 SGA에 저장된다. 모든 정보는 LRU(Least Recently Used) 알고니즘을 사용하여 메모리에 저장되며 Table과 열을 설명하는 Comments는 column을 자주 Access하지 않으면 저장되지 않는다. - Data Dictionary에 새로운 Table이나 View를 추가할수 있는데 소유자는 SYSTEM이나 제3의 ORacle 사용자여야 하며 SYS사용자에게 속한 새로운 개체는 생성하면 안된다. 5. User 접두어를 가진 View - 사용자와 밀접하게 관련된 View - 사용자 자신의 전용환경을 참조 - ALL_ View의 모든 정보의 부분 집합이다. - Public Synonym을 가질수 있다. <예> 아래의 예는 Scott 사용자의 Table을 조회항 결과이다. SQL> connect scott/tiger Connected. SQL> select table_name, tablespace_name from user_tables; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ BONUS TOOLS DEPT TOOLS DUMMY TOOLS EMP TOOLS SALGRADE TOOLS 6. ALL 접두어를 가진 View - 사용자가 소유한 개체와 공용 권한과 명시적으로 부여된 권한및 롤을 사용하여 사용자가 Access할수 있는 개체정보를 돌려준다. <예>라애의 예는 Scott 사용자가 Access 가능한 DB Link를 보이는 예이다. SQL> connect scott/tiger Connected. SQL> select owner, db_link from all_db_links; OWNER ------------------------------ DB_LINK -------------------------------------------------------------------------------- PUBLIC LINUX.WORLD PUBLIC SANAE.WORLD PUBLIC WINK.WORLD 7. DBS 접두어를 가진 View - 전체 DB에 대한 전역 View를 제공 - DataBase 관리자만이 질의가 가능하며 select any table 권한이 있는 사용자 또한 질의가 가능하다. - 이러한 View에 대한 동의어는 생성되지 않으며, 다른 사용자가 질의 하려면 앞에 sys.이라는 접두어를 붙여야 한다. select owner, object_name from sys.dba_objects; - select any table권한이 있는 사용자는 dba_sysnonyms.sql을 실행하여 자신의 계정에 DBA View에 대한 동의어를 생성할수 있다. 이를 사용하면 현재 사용자에게 해당되는 동의어가 생성된다. 8. DUAL Table - Oracle과 사용자 작성 프로그램이 알려져 있는 결과를 보장하도록 참조할수 있는 작은 Table이며, 하나의 열과 하나의 행으로 구성되어 있다. [Role]롤(Role)이란? 1. Role이란? - 사용자에게 허가할수 있는 권한들의 집합 - 이 롤을 이용하여 권한 부여와 회수를 쉽게할수 있다. - 한 사용자가 여러롤을 엑세스할수 있고 다른 여러사용자에게 같은 롤을 지정할수 있다. 2. 롤의 작성과 지정 - DBA가 role을 생성해야하며, 다음에 권한을 롤에 지정하고 사용자에게 롤을 부여한다. SQL> create role myrole; Role created. SQL> grant create table, select any table to myrole; Grant succeeded. - 위의 예에서 myrole이라는 롤을 생성하고 해당 롤에는 create table 권한과 select any table이란 권한을 할당했다. SQL> grant myrole to office; Grant succeeded. - 그런다음 2개의 권한이 있는 Role을 office라는 사용자에게 부여했다. 즉 office라는 사용자는 create table 권한과 select ant table 2개의 권한을 부여받은 것이다. 객체권한부여에 관하여 1. 개요 - DBA는 사용자가 특정 객체(Table , View, Sequence, Procedure, Snapshot) 에 대한 작업을 수행할수 있게 합니다. - 객체에 대한 권한은 객체 유형에 따라 매우 다양합니다. - 소유자(Owner) 객체에 관한 모든 권한을 가지고 있다. - 소유자는 소유한 객체에 대해 특정권한을 다른 사용자에게 줄수 있다. - 형식> Grant 객체권한 On 객체 to 사용자 [ With Grant Option ] 2. 예 SQL> grant select on test to des2; Grant succeeded. des2라는 사용자에게 test Table에 대한 select 권한을 부여한다. SQL> desc test Name Null? Type ------------------------------- -------- ---- A NUMBER(15) B VARCHAR2(2) SQL> grant update(b) on test to des2; Grant succeeded. des2 사용자에게 test Table의 column b를 변경할수 있는 권한을 부여한다. 3. 기타 - 객체에 대한 권한을 주려면 자신이 생성한 개체이거나 with grant option 으로 객체권한을 받아야 한다. - 객체의 소유자는 다른 사용자나 데이타베이스의 role에게 객체 권한을 부여 할수 있다.(with grant option) - 객체의 소유자는 객체에 대한 모든 권한을 자동적으로 받는다. - With Grant Option으로 권한을 받은 사용자는 다른 사용자나 role에게 그 권한을 다시 Grant 할수 있다. - With Grant Option으로 허가된 객체 권한은 허가자의 권한이 취소되면 같이 취소된다. SQL> connect office/office_pass; Connected. SQL> grant select on test to des2 with grant option; Grant succeeded. SQL> connect des2/des2_pass Connected. SQL> grant select on office.test to apt; Grant succeeded. - Public 키워드를 사용하면 시스템의 모든 사용자에게 권한 부여가 가능하다. SQL>grant select on test to public; test Table에대한 모든 사용자가 select가 가능하게 된다. SQL*Plus에서 치환변수에 관해 ----------------------------------- - 치환변수(Substitution Variable) - ----------------------------------- 1. 일시적으로 값을 저장하기위해 SQL*Plus의 치환변수를 사용한다. a. 단일 엠퍼센드(&) - 사용자는 단일엠퍼센드가 앞에 붙은 치환변수를 써서 동적으로 행을 제한한다. - 명령이 실행될때마다 사용자에게 입력을 요구한다. SQL>select id, last_name, salary from s_emp where dept_id = &department_number; Enter value for department_number: 31 SQL>select id, &column_name from s_emp where &condition; Enter value for column_name : total Enter value for condition : payment_type = 'CASH' b. 이중 엠퍼센드 치환변수(&&) - 매번 사용자에게 Prompt하지않고 변수값을 재사용하려 할때 사용한다. - 프롬프트는 한번만 나타남 - SQL*Plus에서는 입력된 값을 저장하고 해당 변수 이름을 참조할때마다 값을 재사용한다. SQL>select id, &&column_name from s_ord order by &column_name; Enter value for column_name : total c Set Verify명령 SQL문장상에 대한 변경사항을 확인 할려면 SQL*Plus의 SET VERIFY 명령을 사용한다. SET VERIFY를 ON으로 설정하면 SQL*Plus는 치환변수를 값으로 바꾸기 전후의 값을 출력한다. SQL*Plus에서 사용자변수정의 1. 두개의 SQL명령을 사용하여 변수를 미리 정의한다. - DEFINE : CHAR Data유형의 사용자 변수생성, UNDEFINE명령으로 해제함 - ACCEPT : 사용자의 입력을 읽어에 저장 - 두명령모두 변수가 존재하지 않을때는 변수를 생성하며,존재할경우엔 자동으로 변수값을 재정의한다. - SQL*Plus는 이중 엠퍼센드에대해 DEFINE명령을 실행한다. - DEFINE 사용시 한개의 공백이 필요하면 공백을 단일 따옴표로 묶어야 한다. - ACCEPT 명령에서 치환변수를 참조할때 SQL*Plus 치환변수에 &를 붙이면 안된다. a. ACCEPT - 사용자로부터 입력값을 받아들일때 작성 ACCEPT p_dname PROMPT 'Provide the department name: ' - Number또는 Date DataType의 변수를 명시적으로 정의한다. ACCEPT p_salary NUMBER PROMPT 'Salary Amount : ' - 보안상의 이유로 사용자의 입력을 숨길때 ACCEPT passwd CHAR PROMPT 'PassWord : ' HIDE 예> SQL> select ename 2 from emp 3 where job like '%&myJob%'; Enter value for myjob: CLERK old 3: where job like '%&myJob%' new 3: where job like '%CLERK%' ENAME ---------- SMITH ADAMS JAMES MILLER SQL> set echo off SQL> accept myJob prompt ' Enter Job : ' Enter Job : CLERK SQL> select ename 2 from emp 3 where job like '%&myJob%'; old 3: where job like '%&myJob%' new 3: where job like '%CLERK%' ENAME ---------- SMITH ADAMS JAMES MILLER SQL> set verify off SQL> accept myJob prompt ' Enter Job : ' Enter Job : CLERK SQL> select ename 2 from emp 3 where job like '%&myJob%'; ENAME ---------- SMITH ADAMS JAMES MILLER b. UNDEFINE을 실행하거나 SQL*Plus를 빠져나올때까지 존재 DEFINE명령으로 내용을 확인할수 있다. 세션마다 특정변수를 지정할려면 변수가 시스템 시동시에 생성될수 있게 login.sql 파일을 이용하면 된다. 예> SQL> define myJob = CLERK SQL> define dname symbol dname is UNDEFINED SQL> define myJob DEFINE MYJOB = "CLERK" (CHAR) SQL> select ename 2 from emp 3 where job like '%&myJob%'; ENAME ---------- SMITH ADAMS JAMES MILLER