13주차 -권한 , 롤, TRIGGER

테이블에 대한 권한 부여하기
==================
부여할 수 있는 권한
SELECT [column list]   
DELETE 
INSERT 
UPDATE [column list] 
REFERENCES [column list] : 해당 컬럼에 대하여 외래키를 지정할 수 있다
TRIGGER : 해당 테이블에 대한 트리거를 만들 수 있다


먼저 샘플 유저를 만들자(system)
=====================
conn system
CREATE USER lee1 identified by lee1 DEFAULT TABLESPACE student;
CREATE USER kim1 identified by kim1 DEFAULT TABLESPACE student;

grant connect, resource to lee1;
grant connect, resource to kim1;

Scott계정에 create role 권한 부여(system)
===========================
grant create role to scott with admin option;


다음과 같이 권한을 주자(scott)
====================
conn scott/tiger
grant update(loc) on dept to lee1 with grant option;
grant select on dept to lee1 with grant option;

받은 권한을 실행하고 확인(lee1)
====================
conn lee1/lee1
update scott.dept set loc='SEOUL' where deptno=10;
select * from scott.dept;

받은 권한을 kim에게 전달(lee1)
===================
grant update(loc) on scott.dept  to kim1;
grant select on scott.dept to kim1;

받은 권한을 실행하고 확인(kim)
====================
conn kim1/kim1
update scott.dept set loc='NEW YORK' where deptno=10;
select * from scott.dept;

권한을 회수(scott)
============
conn scott/tiger
revoke update on emp from lee1; 
revoke update on emp from lee1;



=======
role(역할)
=======

먼저 롤을 만들고 권한 부여 (scott)
======================
create role customer;
grant select, update(sal) on emp to customer;

롤을 lee에게 주고, kim에게는 select 권한만 준다 (scott)
===================================
grant customer to lee1  with admin option;
grant select on emp to kim1;

받은 권한 실행해보자(lee)
=================
conn lee1/lee1
select * from scott.emp;
update scott.emp set sal=sal+50 where empno=7369;

받은 권한 실행해보자(kim)
=================
conn kim1/kim1
select * from scott.emp;
update scott.emp set sal=sal+50 where empno=7369;

conn lee1/lee1
update scott.emp set sal=sal+50 where empno=7369;

conn kim1/kim1
update scott.emp set sal=sal+50 where empno=7369;

롤 제거(scott)
============
conn scott/tiger
drop role customer;

롤 확인 (lee)
========
conn lee1/lee1
select * from scott.emp;
update scott.emp set sal=sal+50 where empno=7369;

롤 확인 (kim)
========
conn kim1/kim1
select * from scott.emp;;

롤 회수 (scott)
========
conn scott/tiger
revoke all on emp from kim1; 

=========================
모든 테이블에 대하여 권한 부여하기
=========================

SCOTT>conn system/Manager7083
SYSTEM>
grant select any table to scott with admin option;

SCOTT>
grant select any table to lhk7083;

LHK7083>
select * from scott.dept;

SCOTT>
revoke select any table from scott;

SYSTEM>
revoke select any table from lhk7083;


트리거(TRIGGER) 만들기
트리거란?
특정 이벤트가 발생했을 때 자동으로 실행되는 스크립트

EVENT : INSERT, DELETE, UPDATE
BEFORE/AFTER : 사전/사후
ROW/STATEMENT : 영향받는 모든행/단 한행
OLD/NEW : 기존값/새로운 입력값

===========================================
1. emp테이블에 갱신이 발생할때마다 회수를 늘려가는 트리거 만들기
===========================================

먼저 갱신횟수를 위한 보조 테이블을 만든다
============================
CREATE TABLE update_count
        (count  NUMBER(3));
INSERT INTO update_count VALUES (0);

트리거를 만들어 보자
==============
CREATE or REPLACE TRIGGER emp_update
 BEFORE update ON emp
BEGIN
update update_count set count=count+1;
END;
/

update emp set sal=sal+100 where empno=7369;
select * from update_count;
---> 여러번 실행하여 카운트가 증가하는 것을 확인

DROP TRIGGER emp_update;

---

1 행이 갱신되었습니다.

TAEHO>select * from update_count;

     COUNT
----------
         1

TAEHO>CREATE or REPLACE TRIGGER emp_update
  2   BEFORE update ON emp
  3  BEGIN
  4  update update_count set count=count+1;
  5  END;
  6  /

트리거가 생성되었습니다.

TAEHO>
TAEHO>update emp set sal=sal+100 where empno=7369;

1 행이 갱신되었습니다.

TAEHO>select * from update_count;

     COUNT
----------
         2

---

=================================
2. 부모레코드 삭제 전에 자식레코드 먼저 삭제하기
=================================

s테이블에 있는 레코드는 sp에서 참조하는 부모레코드이다.
따라서 그냥 삭제하면 오류가 난다
이럴 때 부모레코드 삭제 전에 자식레코드 먼저 삭제하는 트리거를 만들고 삭제해보자


CREATE or REPLACE TRIGGER sp_del_before_s
 BEFORE DELETE ON s
FOR EACH ROW
BEGIN
  DELETE FROM sp
  WHERE S# = :OLD.S#;
END;
/


DELETE FROM s WHERE S# = 'S1';
SELECT * FROM sp;

---

TAEHO>SELECT * FROM sp;

S#  P#         QTY
--- --- ----------
S2  P1         300
S2  P2         400
S3  P2         200
S4  P2         200
S4  P4         300
S4  P5         400

6 개의 행이 선택되었습니다.

---

트리거의 일시중시/재생
===============
ALTER TRIGGER sp_del_before_s DISABLE;
DELETE FROM S WHERE S# = 'S2';

ALTER TRIGGER sp_del_before_s ENABLE;
DELETE FROM S WHERE S# = 'S2';

DROP TRIGGER sp_del_before_s;


=====================
3. NULL이 입력을  0으로 바꾸기
=====================

EMP테이블에 행을 추가해보자.
그런데 만약 SAL 값에 NULL이 입력되면 이를  0으로 바꿔주고 싶다
이러한 것은 NOT NULL 무결성 유지를 위해 매우 중요
=====================================

CREATE OR REPLACE TRIGGER null_changer
   BEFORE  INSERT ON emp
 FOR EACH ROW  
   WHEN (NEW.sal IS NULL)
   BEGIN
          :NEW.sal := 0;
   END;
 /

INSERT INTO EMP VALUES (8000, 'LEE', 'CLERK',     7782, '82-01-23', NULL, NULL, 10);
select * from emp;

DROP TRIGGER null_changer;

=====================
4. 특정 요일에 변경 막아주기
=====================

오늘은 목요일이다.
목요일에 새로운 레코드를 삽입, 삭제, 변경 시 작업을 막아주는 트리거

CREATE OR REPLACE TRIGGER day_protection
BEFORE DELETE OR INSERT OR UPDATE ON EMP
BEGIN
 IF TO_CHAR(SYSDATE, 'DAY') = '화요일'
    THEN
         RAISE_APPLICATION_ERROR(-20005, '화요일엔 자료수정 불가!');
  END IF;
END;
/
update emp set sal=sal+500 where empno=7369;

DROP TRIGGER day_protection;




==================
5.  간단한 로그화일 만들기
==================

EMP테이블의 SALARY 값이 변하면 emp_log에
사원번호, 변경시각, 변경 전, 변경 후의 , 변화율 값을 기록한다


CREATE TABLE emp_log (
       Emp_id NUMBER(4),
       Log_date DATE,
       old_salary NUMBER(7, 2),
       dept DEPT(7, 2),
       New_salary NUMBER(7, 2),
       chg_rate NUMBER(7, 2)

    );

CREATE OR REPLACE TRIGGER trace_log_salary
    BEFORE UPDATE  ON emp
    FOR EACH ROW
    WHEN (new.SAL != old.SAL)
BEGIN
    INSERT INTO emp_log(
        Emp_id
       ,Log_date
       ,old_salary
       ,New_salary
       ,chg_rate
    )
    VALUES(
        :new.Empno
       ,SYSDATE
       ,:old.SAL
       ,:new.SAL
       ,(:new.SAL - :old.SAL) / :old.SAL
    );
END;
/

다음과 같이 수행하고 로그를 살펴보자
=========================
update emp set sal=sal +100 where empno=7369;
select * from emp_log;

---

TAEHO>select * from emp_log;

    EMP_ID LOG_DATE OLD_SALARY NEW_SALARY   CHG_RATE
---------- -------- ---------- ---------- ----------
      7369 22/11/22       1000       1100         .1

---

계속해서 관찰해보자
==============
update emp set sal=700 where empno=7499;
update emp set sal=1200 where empno=7566;
select * from emp_log;

---

SQL> select * from emp_log;

    EMP_ID LOG_DATE OLD_SALARY NEW_SALARY   CHG_RATE
---------- -------- ---------- ---------- ----------
      7369 22/11/23       1300       1400        .08
      7369 22/11/23       1400       1500        .07
      7499 22/11/23        500        700         .4
      7566 22/11/23        500       1200        1.4

---

DROP TRIGGER trace_log_salary;
DROP TABLE emp_log;

==================
6. 변경사항 화면 출력하기
==================
CREATE or REPLACE TRIGGER show_s_update
BEFORE
UPDATE ON s
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('변경 전 컬럼 값 : ' || :old.SNAME);
DBMS_OUTPUT.PUT_LINE('변경 후 컬럼 값 : ' || :new.SNAME);
END;
/

set serveroutput on; 
UPDATE S SET SNAME='HHH' WHERE S#='S1';

DROP TRIGGER show_s_update;


**********
연습문제
**********

1. dept 테이블에 변경이 실행되면 변경전의 부서명과 변경 후의 부서명을 출력하는 트리거를 만드시오

---

dept테이블의 dept 값이 변하면 dept_log에
변경전, 변경후 값을 기록한다


SQL> CREATE TABLE dept_log(id int, dept varchar(20));

CREATE OR REPLACE TRIGGER trace_log_dept
    BEFORE UPDATE  ON dept_log
    FOR EACH ROW
    WHEN (new.SAL != old.SAL)
BEGIN
    INSERT INTO dept_log(
        Emp_id
       ,Log_date
       ,old_salary
       ,New_salary
       ,chg_rate
    )
    VALUES(
        :new.Empno
       ,SYSDATE
       ,:old.SAL
       ,:new.SAL
       ,(:new.SAL - :old.SAL) / :old.SAL
    );
END;
/

다음과 같이 수행하고 로그를 살펴보자
=========================
update emp set sal=sal +100 where empno=7369;
select * from dept_log;

---

2. 다음과 같이 고객, 상품, 판매 3개의 테이블을 만들자

create table customer (
    c_no    number(3),
    c_name  varchar2(10),
    purchase_count  number(5),
    constraint customer_pk primary key (c_no));
create table goods (
    g_no    number(3),
    g_name  varchar2(10),
    inventory  number(3),
    constraint goods_pk primary key (g_no));

create table purchase (
    purchase_no number(3),
    c_no    number(3),
    g_no    number(3),
    amount number(3),
    purchase_date date,
    constraint purchase_pk primary key (purchase_no),
    constraint rent_fk1 foreign key (c_no) references customer(c_no),
    constraint rent_fk2 foreign key (g_no) references goods(g_no));

insert into customer values(101,'홍길동',0);
insert into customer values(102,'일지매',0);
insert into customer values(103,'임꺽정',0);

insert into goods values(501,'지우개', 100);
insert into goods values(502,'연필', 200);
insert into goods values(503,'노트', 150);


select * from customer;
select * from goods;
select * from purchase;


=====================================
자, 여기에서 고객이 상품을 하나 구입하면 
고객의 상품구매회수(purchase_count)는 1만큼 증가
구매한 상품의 재고(inventory)는 구매량(amount)만큼 감소
하는 트리거를 만들자.
=====================================

단, 구매일시 (purchase_date)는 시스템 시각을 이용하고
     구매번호 (purchase_no)는 다음과 같은 시퀀스를 이용한다

CREATE SEQUENCE purchase_no
   INCREMENT BY 1
   START WITH 1
   MAXVALUE 100
   NOCYCLE
   NOCACHE;

다음과 같이 입력해보자

insert into purchase(purchase_no, c_no, g_no, purchase_date, amount) values(purchase_no.NEXTVAL, 101, 501, SYSDATE, 10);
insert into purchase(purchase_no, c_no, g_no, purchase_date, amount) values(purchase_no.NEXTVAL, 101, 502, SYSDATE, 10);
insert into purchase(purchase_no, c_no, g_no, purchase_date, amount) values(purchase_no.NEXTVAL, 101, 503, SYSDATE, 10);
insert into purchase(purchase_no, c_no, g_no, purchase_date, amount) values(purchase_no.NEXTVAL, 102, 501, SYSDATE, 20);
insert into purchase(purchase_no, c_no, g_no, purchase_date, amount) values(purchase_no.NEXTVAL, 103, 502, SYSDATE, 30);

==================================
위 삽입을 수행했을 때 다음과 같은 결과가 나와야 함
이를 만족하는 트리거를 만드시오
===================================

select * from purchase;

PURCHASE_NO     C_NO       G_NO     AMOUNT   PURCHASE
-----------------     ----------  ---------- ----------     -----------
          2               101         501            10          21/11/24
          3               101         502            10          21/11/24
          4               101         503            10          21/11/24
          5               102         501            20          21/11/24
          6               103         502            30           21/11/24

select * from goods;

   G_NO   G_NAME      INVENTORY
---------- ---------- ----------
       501  지우개             70
       502  연필              160
       503  노트              140

select * from customer;

   C_NO C_NAME     PURCHASE_COUNT
---------- ---------- --------------
    101    홍길동                  3
    102    일지매                  1
    103    임꺽정                  1