<DataBase_231114화> 트리거

// 연습문제

 

1. 아래 조건에서 주민번호의 유효성을 체크하여라.

/* 
select f_jumin_ck('주민번호 자신') from dual; -> '참' 또는 '거짓' 출력하는 함수 만들기.
1. 000413-3865464 라는 변수의 주민번호를 입력하면
2. substr함수를 이용하여 뽑아낸 숫자에 특정 숫자를 곱하고
3. 그 합을 구한 값을 변수1(ju_sum)에 넣는다.
4. mod함수를 이용해 나머지를 구하는 함수 mod(ju_sum/11)의 값을 구한다.
5. if when then을 이용하여 11-mod(ju_sum/11)인 값을 10으로 나눈 나머지 값이 substr(v_jumin, 14, 1)과 비교하여 
그 값이 substr(jumin, 14)랑 일치하면 true 아니면 false
*/

 

 

1. 내가 한 풀이

create or replace function f_jumin_ck
(v_jumin varchar2)
return varchar2

is
ju_sum number;

begin

ju_sum := (substr(v_jumin, 1, 1)*2)+(substr(v_jumin, 2, 1)*3)+(substr(v_jumin, 3, 1)*4)+(substr(v_jumin, 4, 1)*5)+
(substr(v_jumin, 5, 1)*6)+(substr(v_jumin, 6, 1)*7)+(substr(v_jumin, 8, 1)*8)+(substr(v_jumin, 9, 1)*9)+
(substr(v_jumin, 10, 1)*2)+(substr(v_jumin, 11, 1)*3)+(substr(v_jumin, 12, 1)*4)+(substr(v_jumin, 13, 1)*5);

if mod(11-mod(ju_sum,11),10)=substr(v_jumin, 14, 1) then

return '참';
else return '거짓';
end if;
end;
/

 

 

------------------------------------------------

 

<트리거>

 

create table dept_bk as select * from dept;

 

show error trigger t_dept_backup

 

insert into dept values(66, '기술3부', '서울');

 

select * from dept_bk;

 

delete dept where deptno=10;

 

 

 

-- create table dept_bk as select * from dept;

1. dept table에 입력이 발생하면 dept_bk table에 추가가 발생하는 트리거

create or replace trigger t_dept_in
after insert on dept
for each row
begin
insert into dept_bk values(:new.deptno, :new.dname, :new.loc);
end;
/


2. dept table에 삭제가 발생하면 dept_bk table에 삭제가 발생하는 트리거

create or replace trigger t_dept_del
after delete on dept
for each row
begin
delete dept_bk where deptno=:old.deptno;
end;
/

3. dept table에 변경이 발생하면 dept_bk table에 변경이 발생하는 트리거

create or replace trigger t_dept_upd
after update on dept
for each row
begin
insert into dept_bk values(:new.deptno, :new.dname, :new.loc);
delete dept_bk where deptno=:old.deptno;
         --update dept_bk set dname=:new.dname, loc=:new.loc where deptno=:old.deptno;
end;
/

 

4. 입력, 삭제, 변경을 한꺼번에 하는 트리거

create or replace trigger t_dept_backup
            after insert or delete or update on dept
            for each row
begin
           if inserting then
               insert into dept_bk values(:new.deptno, :new.dname, :new.loc);
           elsif deleting then
               delete dept_bk where deptno=:old.deptno;
           elsif updating then
               update dept_bk set dname=:new.dname, loc=:new.loc where deptno=:old.deptno;
            end if;
end;
/

 

------------------------------------------------

 

<연습문제1>

 

/*
직원의 대출여부를 업데이트 하는 프로그램을 만들어라.

<조건>
1. emp_ex 테이블 생성
colomn : empno(pk), ename, loan_ck(대출여부)

create table emp_ex(
empno number(3) constraint emp_ex_empno_pk primary key,
ename varchar2(10),
loan_ck char(4) default 'N'
);

insert into emp_ex (empno, ename) values(1, '홍동우');
insert into emp_ex (empno, ename) values(2, '최수빈');
insert into emp_ex (empno, ename) values(3, '유승민');

2. loan_ex 대출 현황 테이블 생성
colomn : eno(fk), amount, loan_date

create table loan_ex(
eno number(3);
amount number(10);
loan_date date default sysdate,
constraint loan_eno_fk foreign key(eno) references emp(empno)
);

3. 입력, 삭제, 변경을 한꺼번에 하는 트리거 생성
if
loan insert then loan_ck(from emp) N->Y 변경
loan delete then loan_ck(from emp) Y->N 변경
loan update then loan_ck(from emp) Y->N, N->Y 변경
end if

*/

 

<1. 내가 한 풀이>

-- 1. emp_ex 테이블 생성
-- colomn : empno(pk), ename, loan_ck(대출여부)

create table emp_ex(
empno number(3) constraint emp_ex_empno_pk primary key,
ename varchar2(10),
loan_ck char(4) default 'N'
);

insert into emp_ex (empno, ename) values(1, '홍동우');
insert into emp_ex (empno, ename) values(2, '최수빈');
insert into emp_ex (empno, ename) values(3, '유승민');

-- 2. loan_ex 대출 현황 테이블 생성
-- colomn : eno(fk), amount, loan_date

create table loan_ex(
eno number(3);
amount number(10);
loan_date date default sysdate,
constraint loan_eno_fk foreign key(eno) references emp(empno)
);

-- 3. 입력, 삭제, 변경을 한꺼번에 하는 트리거 생성
/*

if
loan insert then loan_ck(from emp) N->Y 변경
loan delete then loan_ck(from emp) Y->N 변경
loan update then loan_ck(from emp) Y->N, N->Y 변경
end if

*/

 

create or replace trigger t_loan_backup
            after insert or delete or update of eno on loan_ex
            for each row
begin
           if inserting then -- 대출기록 추가
     update emp_ex set loan_ck = 'Y' where empno = :new.eno; 

           elsif deleting then -- 대출기록 삭제
   update emp_ex set loan_ck = 'N' where empno = :old.eno;

           elsif updating then -- 대출기록 업데이트
               update emp_ex set loan_ck = 'Y' where empno = :new.eno;
               update emp_ex set loan_ck = 'N' where empno = :old.eno;
     end if;
          
end;
/

 

<1. 선생님이 정리해주신 풀이>

 

------------------------------------------------

 

<update cascade 구현>

-- 부서 테이블의 부서번호가 변경되면, 그 부서를 참조하는 사원의 부서번호도 함께 변경

create or replace trigger t_cas
   after update of deptno on dept
   for each row
begin
   update sawon set deptno=:new.deptno where deptno=:old.deptno;
end;
/

 

------------------------------------------------

 

<사용자 정의 에러 함수>

 

------------------------------------------------

 

<연습문제>

 

/* 사원의 급여가 변경되면
1. 에러가 발생하고
2. 변경한 정보 (사용자, 시간포함한 날짜, 변경한사번, 변경전급여, 변경후급여)를 black_list 테이블에 입력
3. 변경하려는 급여가 기존 급여보다 적거나, 기존 급여의 20%를 초과하면 에러가 발생. (단, 부장은 제외)
다시 말해서,
부장이 아니면서 기존급여가 변경하는 급여보다 크면 블랙리스트에 넣고 실행.
부장이 아니면서 기존급여의 20%보다 작으면 블랙리스트에 넣고 실행.
4. 급여를 변경할 수 없으면서, 변경을 시도한 정보는 black_list 테이블에 입력
*/

 

<선생님이 정리해주신 풀이>

 

 

------------------------------------------------

 

<선생님이 정리해주신>

 

--ed23

 

--PL/SQL에서만 사용하는 데이터 타입 : boolean (true | false)
--주민번호를 가지고 유효성을 체크하는 함수
--select f_jumin_check('041201-3182518') from dual;  // 유효 or 비유효

--1. 0 4 1 2 0 1 - 3 1 8 2 5 1 8
--    * 
--   2 3 4 5 6 7   8 9 2 3 4 5 = (0*2)+(4*3)+... = 0+12+4+10+0+7+24+9+16+6+20+5 = 113
--2. 1번결과/11 나머지 = 3
--3. 11-2번결과           = 8
--4. 3번결과/10 나머지 = 8 => 주민번호 마지막 숫자와 같다면 유효

create or replace function f_jubun_ck
   (v_jubun varchar2)
   return varchar2
--   return boolean
is
   v_sum number;
begin
   v_sum:=substr(v_jubun, 1, 1)*2+substr(v_jubun, 2, 1)*3+substr(v_jubun, 3, 1)*4+substr(v_jubun, 4, 1)*5
             +substr(v_jubun, 5, 1)*6+substr(v_jubun, 6, 1)*7+substr(v_jubun, 8, 1)*8+substr(v_jubun, 9, 1)*9
             +substr(v_jubun, 10, 1)*2+substr(v_jubun, 11, 1)*3+substr(v_jubun, 12, 1)*4+substr(v_jubun, 13, 1)*5;
   if mod(11-mod(v_sum, 11), 10) = substr(v_jubun, 14, 1) then
    return '참';
-- return true;
  else return '거짓';
--else return false;
  end if;
end;
/

-- 적용예)
--select f_jumin_check('041201-3182518') from dual;
--select goname, f_jumin_check(gojumin) jumin_ck from gogek;

--*회원가입시 = 또다른 PL/SQL 프로그램내에서 사용
--if (f_jubun_ck(v_jumin)) then
-- insert~

 

--ed24

 

*** Trigger(트리거)
-- 지정한 이벤트(DML)가 발생할 때마다 자동*으로 실행되는 PL/SQL 프로그램 객체 (호출문 없다)
-- 데이터 흐름 제어문 사용할 수 없음(기본값)

-- 목적 
1. 업무의 자동화(연쇄작용, 예=급여계산, 연금계산, 재고관리,....)
2. 데이터 백업(복제) 기능
3. 이벤트를 발생시키는 사용자에 대한 감시 기능
4. 제약조건으로 구현할 수 없는 데이터 무결성 구현

-- 형식
create or replace trigger trigger_name
[after | before] insert or update or delete on table_name  -- 이벤트 지정
--declare
-- 지역변수;
begin
명령;
end;
/

--행단위 트리거에서만 사용하는 키워드
--[:new.컬럼명] : 입력이 발생한 행의 컬럼값을 표현
--[:old.컬럼명] : 삭제가 발생한 행의 컬럼값을 표현

 

--ed25

create table dept_info(info varchar2(100));

create or replace trigger t_ex
after insert on dept_ex
for each row
begin
insert into dept_info values('부서행 입력');
end;
/

 

--ed26

create table dept_bk as select * from dept;

--dept table에 입력이 발생하면 dept_bk table에 입력이 발생하는 트리거
create or replace trigger t_dept_in
after insert on dept
for each row
begin
insert into dept_bk values(:new.deptno, :new.dname, :new.loc);
end;
/

--dept table에 삭제가 발생하면 dept_bk table에 삭제가 발생하는 트리거
create or replace trigger t_dept_del
after delete on dept
for each row
begin
delete dept_bk where deptno=:old.deptno;
end;
/

--dept table에 변경이 발생하면 dept_bk table에 변경이 발생하는 트리거
create or replace trigger t_dept_up
after update on dept
for each row
begin
delete dept_bk where deptno=:old.deptno;
insert into dept_bk(deptno, dname, loc) values(:new.deptno, :new.dname, :new.loc);
-- update dept_bk set dname=:new.dname, loc=:new.loc where deptno=:old.deptno;
end;
/
---------------------------------------
create or replace trigger t_dept_backup
after insert or delete or update on dept
for each row
begin
if inserting then
insert into dept_bk values(:new.deptno, :new.dname, :new.loc);
elsif deleting then
delete dept_bk where deptno=:old.deptno;
elsif updating then
  update dept_bk set dname=:new.dname, loc=:new.loc where deptno=:old.deptno;
end if;
end;
/

 

--ed27
create table emp(
empno number(3) constraint emp_empno_pk primary key,
ename varchar2(10),
loan_ck char(4) default 'N'
);

insert into emp(empno, ename) values(1,'홍동우');
insert into emp(empno, ename) values(2,'최수빈');
insert into emp(empno, ename) values(3,'유승민');

create table loan(
eno number(3),
amount number(10),
loan_date date default sysdate,
constraint loan_eno_fk foreign key(eno) references emp(empno)
);

create or replace trigger loan_tri
   after insert or delete or update of eno on loan
   for each row
begin
   if inserting then
      update emp set loan_ck = 'Y' where empno= :new.eno;
   elsif deleting then
      update emp set loan_ck = 'N' where empno= :old.eno;
   elsif updating then
      update emp set loan_ck = 'Y' where empno = :new.eno;
      update emp set loan_ck = 'N' where empno = :old.eno;
   end if;
end;
/

 

--ed28

--update cascade 구현

--부서 테이블의 부서번호가 변경되면, 그 부서를 참조하는 사원의 부서번호도 함께 변경
create or replace trigger t_cas
   after update of deptno on dept
   for each row
begin
   update sawon set deptno=:new.deptno where deptno=:old.deptno;
end;
/

 

--ed29

-- 사용자 정의 에러 함수 : raise_application_error(-20001~20999, '메세지':1024byte이내 작성)
-- rollback명령 포함, 프로그램 종료

--부서 테이블에서 부서정보를 삭제하면 에러 사용
create or replace trigger t_dept_undel 
after delete on dept
for each row
begin
raise_application_error(-20001, '부서정보를 삭제할 수 없습니다.');
end;
/

 

--ed30
-- 사원의 급여가 변경되면,

--1. 에러 발생
--2. 변경한 정보(사용자, 날짜(시간포함), 변경한 사번, 변경전급여, 변경후급여)를 black_list 테이블 입력(black_list 테이블 생성 후)
--3. 변경하려는 급여가 기존 급여보다 적거나, 기존 급여의 20% 초과하면 에러 발생(단, 부장은 제외)
--4. 급여를 변경할 수 없으면서, 변경을 시도한 정보는 black_list 테이블 입력 ***

drop table black_list;
create table black_list
(session_name varchar2(20) default user,
 time varchar2(30) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
 sabun varchar2(10),
 before_sapay number(10),
 after_sapay number(10)
);

create or replace trigger t_gamsi
after update of sapay on sawon
for each row
begin
--1.  raise_application_error(-20001, '급여의 변경이 불가합니다');
--2.    insert into black_list(sabun, before_sapay, after_sapay) values(:new.sabun, :old.sapay, :new.sapay);


end;
/