220916 - PL/SQL - 트리거

• 리턴값 있는(매개변수 밖으로 보내는) 프로시저 참고

https://rsorry.tistory.com/157

 

 

 

 

• 프로시저 문제 - 부서번호 매개변수 / 부서 근무 사원 정보 저장 (사원 번호 사원명 급여 입사일)

create or replace PROCEDURE emp_save (v_dno in employee.dno%type)
IS 
    cursor cur is select * from employee where dno=v_dno;
BEGIN
    dbms_output.put_line('사원번호   사원명   급여   입사일');
    for rec in cur loop
        exit when cur%notfound;
        dbms_output.put_line(rec.eno||'  '||rec.ename||'  '||rec.salary||'  '||rec.hiredate);
    end loop;
END;

=>

exec emp_save(10)

=>

사원번호   사원명   급여   입사일
7782  CLARK  2450  81/06/09
7839  KING  5000  81/11/17
7934  MILLER  1300  82/01/23

 

 

 

 

트리거

: 이벤트 발생에 따라 내부적으로 실행하는 데이터베이스에 저장된 프로시저

 

=> DML 수행됐을 때, 데이터베이스에서 자동 동작하도록 작성된 프로시저

 

호출 X 데이터베이스에서 자동 수행

 

1. ★AFTER 트리거
2. BEFORE 트리거


CREATE [OR REPLACE] TRIGGER 트리거이름
BEFORE | AFTER | INSTEAD OF
INSERT OR UPDATE OR DELETE [OF 컬럼]
ON 테이블명 [WHEN 조건]
[FOR EACH ROW] 
BEGIN
명령;
END;


※ FOR EACH ROW : 행레벨 트리거 / 생략 시 문장레벨 트리거

 

※

행레벨 트리거 : 
DML문이 실행되어 ROW가 추가/삭제/변경될 때마다 트리거 실행
컬럼의 각 행의 데이터 변화 시 실행 / 데이터 행 실제값 제어 O / :OLD, :NEW 사용

문장레벨 트리거 : 
정해진 DML문이 실행될 때에 처음 한 번만 트리거 실행
트리거에 의해 딱 한번 실행 (영향받는 행 없어도) / 각 데이터 행 제어 X / 데이터값에 관계없이 컬럼 변화 시 실행됨

 

※ :new: 변경 후 레코드 갖는 구조체 / :old: 변경 전 레코드 갖는 구조체

 

 

 

 

•  

CREATE OR REPLACE TRIGGER tri_dept
AFTER INSERT ON dept_original --테이블에 insert 발생 후
FOR EACH ROW
BEGIN
    insert into dept_copy values(:new.dno, :new.dname, :new.loc); 

    --트리거 발생시 dept_copy에 dept_original 입력값들 insert 
END;

=>

INSERT INTO dept_original values(10,'영업부','서울');

=> dept_original, dept_copy 에 해당 값 insert됨

 

 

 

•  

CREATE OR REPLACE TRIGGER tri_del
AFTER DELETE ON dept_original --original 테이블에 delete 발생시
FOR EACH ROW
BEGIN
    delete from dept_copy where dept_copy.dno=:old.dno; --삭제 전 dno와 copy테이블 dno 일치하는 dept_copy 행 삭제
END;

=>

delete from dept_original where dno=10;

=> 두 테이블 행 모두 삭제됨

 

 

 

•  

CREATE OR REPLACE TRIGGER tri_msg
AFTER INSERT ON empinfo
BEGIN 
    dbms_output.put_line('신입사원이 입사했습니다');
END;

=>

insert into empinfo values(1,'홍길동','도적');

=> dbms 출력됨

 

 

 

•  

create or replace trigger tri_total
after insert on pdsales
begin
    update pdsales set total=q1st+q2nd+q3rd+q4th;
end;

=>

insert into pdsales(code, q1st, q2nd, q3rd, q4th) values('ss1001',100,150,80,200);

=>

ss1001 100 150 80 200 530

 

 

 

• 입고테이블에 상품 입고 시 상품테이블 재고 추가하는 트리거

EX. insert into inpd (num, code, amount) values(1,'s003',5);

 

create or replace trigger t_input
after insert on inpd
for each row
begin
    update producttbl set amount=amount+:new.amount where code=:new.code;
end;

=>

insert into inpd (num, code, amount) values(1,'s003',5); -- 5 => :new.amount

=> producttbl amount에 5 추가

 

 

 

• inpd 테이블에 현재 입고된 총 입고 수량 구하는 저장 프로시저

create or replace procedure p_pd
is
v_amount number(3);
begin
    select sum(amount) into v_amount from inpd;
    dbms_output.put_line('총 입고 수량 = '||v_amount);
end;