[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
|