반응형
Trigger (트리거)
: 테이블이 변경되면 이를 이벤트로 다른 테이블이 자동으로(연쇄적으로) 변경되도록 하기 위해서 사용된다.
특정 동작을 이벤트로 그로 인해서만 실행되는 프로시저의 일종이다.
-- 트리거 사용 방법
create trigger trigger_name
timing[before | after] event[insert | update | delete]
on table_name
[for each row] -- 튜플 단위로 변경할 때 사용
[when conditions] -- 조건을 줄 수 있음
begin
statement
end;
before 타이밍은 문장이 실행될 때, 실행되기 전에 가지고 있는 begin ~ end 사이의 문장 실행
after 타이밍은 문장이 실행되고 난 후 begin ~ end 사이의 문장 실행
for each row 사용 시 행 레벨 트리거
사용하지 않을 시 문장 레벨 트리거
- 상품분류 테이블에 새로운 데이터가 들어오면 ＇상품분류가 추가되었습니다.'란 메시지를 출력도록 문장 레벨 트리거를 작성하기
-- 상품분류 테이블에 새로운 데이터가 들어오면 ＇상품분류가 추가되었습니다.'란 메시지를 출력도록 문장 레벨 트리거를 작성하기
-- 1. 다음 파일이름을 입력하여 새로 생긴 SQL 파일에 다음과 같이 입력하시오.(파일이름:TRIG01.SQL)
create or replace trigger tg_lprod_id
after insert
on lprod
begin
dbms_output.put_line('상품분류가 추가되었습니다.');
end;
/
-- 2. 만들어진 트리거 확인
select trigger_name from user_triggers;
-- 3. 상품분류 테이블에 로우를 추가
set serveroutput on;
insert into lprod(lprod_id, lprod_gu, lprod_nm) values (
(select max(lprod_id) + 1 from lprod),
(select 'P' || (substr(max(lprod_gu), 2) + 1) from lprod),
'트리거 추가 값1');
select * from lprod;
- 사원 테이블에 새로운 데이터가 들어오면(즉, 신입 사원이 들어오면) 급여 테이블에 새로운 데이터(즉 신입 사원의 급여 정보)를 자동으로 생성하도록 하기 위해서 사원 테이블에 트리거를 작성해 봅시다. (신입사원의 급여는 일괄적으로 200으로 합니다.)
-- 사원 테이블에 새로운 데이터가 들어오면(즉, 신입 사원이 들어오면) 급여 테이블에 새로운 데이터(즉 신입 사원의 급여 정보)를 자동으로 생성하도록 하기 위해서 사원 테이블에 트리거를 작성해 봅시다. (신입사원의 급여는 일괄적으로 200으로 합니다.)
-- 1. 직원을 저장할 테이블 생성
create table emp01 (
empno number(4) primary key, -- 이럴 경우 key name이 존재하지 않음
empname varchar2(45),
empjob varchar2(60)
);
-- 2. 급여를 저장할 테이블 생성
create table sal01 (
salno number(4) primary key,
sal number(7, 2),
empno number(4) references emp01 (empno) -- 사원 테이블의 사원 번호를 연결해서 사용하겠다는 뜻
);
-- 3. 급여번호를 자동 생성하는 시퀀스를 정의하고 이 시퀀스로부터 일련번호를 얻어 급여번호를 부여
-- 초기값 지정하지 않을 시 1부터 진행됨
create sequence sal01_salno_seq;
-- 4. 급여 테이블 입력
-- 사원 테이블에서 insert 된 후에 trg_02가 돌아감
create or replace trigger trg_02
after insert
on emp01
for each row -- 행 단위로 돌아감
begin
-- 급여 테이블을 자동으로 삽입 해줌
insert into sal01 values(sal01_salno_seq.nextval, 200, :new.empno); -- :new.mepno : 입력되는 번호가 들어감.
end;
/
-- 5. 신입사원 입사후 데이터 생성
insert into emp01 values(1, '전수빈', '프로그래머'); -- 이 경우 new.empno에 들어가는 값은 1임
-- 6. 결과 확인
select * from emp01;
select * from sal01;
- 사원이 삭제되면 그 사원의 급여 정보도 자동 삭제되는 트리거
-- 사원이 삭제되면 그 사원의 급여 정보도 자동 삭제되는 트리거
-- 참조하고 있기에 사원번호 2를 삭제가 불가능함
-- 삭제를 하기 위해선 사원의 급여 정보도 삭제되어야 함.
create or replace trigger trg_03
after delete on emp01
for each row
begin
delete from sal01 where empno = :old.empno; -- 삭제된 것을 표현하기 위해 old.empno로 사용해야 한다.
end;
/
-- 삭제
delete from emp01 where empno = 2;
-- 확인
select * from emp01 where empno = 2;
select * from sal01;
- 입고 테이블에 상품이 입력되면 입고 수량을 상품 테이블의 재고 수량에 추가하는 트리거를 작성
-- 입고 테이블에 상품이 입력되면 입고 수량을 상품 테이블의 재고 수량에 추가하는 트리거를 작성
-- 1. 상품 테이블 생성
create table product (
prod_id varchar2(6) primary key, -- 상품코드
prod_name varchar2(12) not null, -- 상품명
prod_jejo varchar(12), -- 제조사
prod_sale number(8), -- 소비자가격
prod_jaego number default 0 -- 재고수량
);
-- 2. 입고 테이블 생성
create table ipgo(
ipgo_id number(6) primary key, -- 입고 번호
prod_id varchar2(6) references -- product(prod_id)를 참조
product(prod_id), -- 상품코드
ipgo_day date default sysdate, -- 입고일자
ipgo_qty number(6), -- 입고수량
ipgo_cost number(8), -- 입고단가
ipgo_amount number(8) -- 입고금액
);
-- 3. 상품 테이블에 샘플 데이터 입력
insert into product(prod_id, prod_name, prod_jejo, prod_sale)
values('A00001', '세탁기', 'LG', 500);
insert into product(prod_id, prod_name, prod_jejo, prod_sale)
values('A00002', '컴퓨터', 'LG', 700);
insert into product(prod_id, prod_name, prod_jejo, prod_sale)
values('A00003', '냉장고', '삼성', 600);
-- 4. 입고 트리거
create or replace trigger trg_04
after insert
on ipgo
for each row
begin
update product
set prod_jaego = prod_jaego + :new.ipgo_qty -- insert 이기에 new 사용
where prod_id = :new.prod_id;
end;
/
-- 5. 입고 테이블에 행 추가
insert into ipgo(ipgo_id, prod_id, ipgo_qty, ipgo_cost, ipgo_amount)
values(11, 'A00001', 5, 320, 1600);
insert into ipgo(ipgo_id, prod_id, ipgo_qty, ipgo_cost, ipgo_amount)
values(2, 'A00001', 2, 320, 640);
insert into ipgo(ipgo_id, prod_id, ipgo_qty, ipgo_cost, ipgo_amount)
values(3, 'A00002', 100, 200, 20000);
-- 6. 입고 수량 변경 시 재고 수량 변경 트리거
create or replace trigger trg_05
after update on ipgo
for each row
begin
update product
set prod_jaego = prod_jaego + (-:old.ipgo_qty + :new.ipgo_qty)
where prod_id = :new.prod_id;
end;
/
update ipgo set ipgo_qty=200, ipgo_amount=40000
where ipgo_id=3;
-- 7. 삭제 트리거
create or replace trigger trg_06
after delete on ipgo
for each row
begin
update product
set prod_jaego = prod_jaego + -:old.ipgo_qty
where prod_id = :old.prod_id;
end;
/
delete ipgo where ipgo_id=3;
- 분류테이블에 추가되거나, 변경될 때 분류코드를 항상 대문자로 처리하는 트리거
-- 트리거 생성
create or replace trigger tg_lprod_upper
before insert or update
on lprod
for each row
begin
:new.lprod_gu :=upper(:new.lprod_gu);
end;
/
-- 시퀀스 사용하기 위해 시퀀스 생성
create sequence lprod_seq minvalue 12;
-- 데이터 삽입
insert into lprod
values (lprod_seq.nextval, 'tt07', '트리거 테스트 ');
- 장바구니 테이블에 입력 발생시 재고수 테이블에 출고, 현재고를 변경하는 트리거
-- 장바구니 테이블에 입력 발생시 재고수 테이블에 출고, 현재고를 변경하는 트리거
create or replace trigger tg_cart_qty_change
after insert or update or delete on cart
for each row
declare
v_qty number;
v_prod varchar2(20);
begin
if inserting then -- 입력(주문 시)
v_qty := nvl(:new.cart_qty, 0);
v_prod := :new.cart_prod;
elsif updating then -- 수정 (주문 수량 변경시)
v_qty := nvl(:new.cart_qty, 0) - nvl(:old.cart_qty, 0);
v_prod := :new.cart_prod;
elsif deleting then -- 삭제 (주문 취소 시)
v_qty := -(nvl(:old.cart_qty, 0));
v_prod := :old.cart_prod;
end if;
update remain set
remain_o = remain_o + v_qty, -- 출고
remain_j_99 = remain_j_99 - v_qty -- 재고
where remain_year = '2020' and remain_prod = v_prod;
dbms_output.put_line('수량 : ' || v_qty);
exception
when others then
dbms_output.put_line('예외 발생 : ' || sqlerrm);
end;
/
-- 확인
select * from remain where remain_prod = 'P101000001';
- 한 줄씩 해석
CREATE or REPLACE TRIGGER tg_cart_qty_change
-- tg_cart_qty_change 이름의 트리거 생성 또는 갱신
AFTER insert or update or delete ON cart
-- cart 테이블에서 삽입, 수정, 삭제가 이루어진 후에 트리거 발생하도록 함
FOR EACH ROW
-- 각 행(=ROW=RECORD)이 변경될 때마다 트리거를 발생시키는 방법, 없을때는 단 한번만 트리거를 발생시킬 때 사용(문장레밸트리거)
DECLARE
-- PL/SQL 구조로 선언부문-변수, 상수, 사용자예외 등
v_qty NUMBER;
-- v_qty 정수숫자타입 변수 선언
v_prod VARCHAR2(20);
-- v_prod 가변문자타입 20바이트 크기로 변수 선언
BEGIN
-- PL/SQL 구조로 시작부문-실행부문 시작
IF INSERTING THEN
-- 만약 트리거 문장이 삽입이면(즉, 상품이 팔리면)
v_qty := NVL(:NEW.cart_qty,0);
-- v_qty에 데이터가 삽입될 때 들어온 새로운 값(상품이 팔린 수량) 저장
-- 이 값(NEW.cart_qty)이 NULL이 아니면 자신값이고 NULL이면 0으로 대체
v_prod := :NEW.cart_prod;
-- v_prod에 데이터가 삽입될 때 들어온 새로운 값(팔린 상품번호) 저장,
ELSIF UPDATING THEN
v_qty := NVL(:NEW.cart_qty,0) - NVL(:OLD.cart_qty,0);
-- v_qty에 데이터가 수정될 때 들어온 수량에서 기존의 수량을 빼서 저장(이 값을 변경사항에 고려)
v_prod := :NEW.cart_prod;
-- v_prod에 데이터가 수정될 때 들어온 상품번호 저장,
ELSIF DELETING THEN
v_qty := -(NVL(:OLD.cart_qty,0));
v_prod := :OLD.cart_prod;
END IF;
UPDATE remain SET remain_o = remain_o + v_qty,
remain_j_99 = remain_j_99 - v_qty
WHERE remain_year = '2023' AND remain_prod = v_prod;
DBMS_OUTPUT.PUT_LINE('수량 :' || v_qty);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('예외 발생:' || SQLERRM);
END;
-- PL/SQL 구조로 끝부문-실행부문 끝
/
-- Ctrl+Enter 실행시 마감처리
SET SERVEROUTPUT ON;
-- INSERTING
-- 주문테이블에 P101000001상품 20개 주문하기(20개팔림) =>
INSERT INTO cart VALUES ('a001','2023041000001','P101000001',20);
-- 출고수량에서 + 20개, 재고수량에서 - 20개 되어짐
-- 결과확인
SELECT * FROM remain
WHERE REMAIN_YEAR = '2023' AND REMAIN_PROD = 'P101000001';
INSERT INTO cart VALUES ('a001','2023041000001','P101000001',100);
INSERT INTO cart VALUES ('a001','2023041000002','P101000001',10);
-- 출고수량에서 + 10개, 재고수량에서 - 10개 되어짐
SELECT * FROM CART
ORDER BY CART_NO DESC;
SELECT * FROM MEMBER;
-- UPDATING
-- 주문테이블에서 판매된 P101000001상품에서 판매수량이 변경됨(10개에서 5개로 수정) => 출고에서는 -5개, 재고에서는 +5개로 적용도어야 함
UPDATE cart SET CART_QTY=5
WHERE CART_MEMBER='a001' AND CART_NO = '2023041000002';
-- DELETING
-- 주문테이블에서 판매번호 2023041000001로 판매된 P101000001상품에서 판매수량이 취소됨(20개 취소) => 출고에서는 -20개, 재고에서는 +20개로 적용도어야 함
- 주문 순량 변경 테스트
-- 요구사항2
-- 회원번호 : c001
-- 주문번호 : 2020080100001
-- 상품번호 : P101000003
-- 주문수량 : 입력할때 20
-- 수정할 때 10 (20 => 10)
-- 삭제할때 (해당수량 취소함)
select * from cart where cart_prod='P101000003'; -- P101000003 상품의 판매 내역
select * from member where mem_id = 'c001'; -- 회원번호 c001의 정보
select * from prod where prod_id = 'P101000003'; -- P101000003의 상품 정보
select * from remain where remain_prod='P101000003'; -- P101000003 상품 재고 수량
select * from cart where cart_no='2020080100001'; -- 2020080100001 주문 번호의 판매 내역
-- 주문 수량 20개
insert into cart values('c001', '2020080100001', 'P101000003', 20);
select * from cart; -- 주문 내역이 쌓임
select * from remain; -- 재고 테이블
select * from remain
where remain_prod='P101000003';
-- 주문 수량 10개로 수정
update cart set cart_qty=10
where cart_member='c001' and cart_no='2020080100001';
-- 주문 내역 삭제
delete from cart
where cart_member='c001' and cart_no='2020080100001';
- 해당 회원의 마일리지도 변경되는 트리거
-- 해당 회원의 마일리지도 변경되는 트리거
create or replace trigger tg_mem_mileage_chage
after insert or update or delete on cart
for each row
declare
v_qty number(9);
v_prod varchar2(10);
v_member varchar2(15);
v_prod_sale prod.prod_sale%type;
v_sum number := 10;
begin
if inserting then
v_qty := nvl(:new.cart_qty, 0);
v_prod := :new.cart_prod;
v_member := :new.cart_member;
elsif updating then
v_qty := nvl(:new.cart_qty, 0) - nvl(:old.cart_qty, 0);
v_prod := :new.cart_prod;
v_member := :new.cart_member;
elsif deleting then
v_qty := -(nvl(:old.cart_qty, 0));
v_prod := :old.cart_prod;
v_member := :old.cart_member;
end if;
select prod_sale into v_prod_sale from prod where prod_id = v_prod;
v_sum := (v_prod_sale*v_qty)*0.01;
update member set mem_mileage = mem_mileage + v_sum
where mem_id = v_member;
dbms_output.put_line('변경 마일리지 ==> ' || v_sum);
exception
when others then
dbms_output.put_line('예외 발생: ' || sqlerrm);
end;
/
select * from member where mem_id = 'a001';
-- 삽입
insert into cart values ('a001', '2020100100001', 'P201000001', 3);
-- 확인
select * from cart;
select * from remain;
select * from prod;
select * from cart where cart_no = '2020090100001';
select * from remain where remain_prod='P201000001';
select * from prod where prod_id='P201000001';
select * from member where mem_id = 'a001'; -- 삽입전 마일리지 1000
Package (패키지)
: 꾸러미, 작은 잠, 포장한 상품이라는 의미로 쓰인다.
업무적으로 관련 있는 것을 하나로 묶어서 사용하며 여러 변수, 커서, 함수, 프로시저, 예외를 묶어 캡슐화 한다.
- 장점
모듈화로 어플리케이션 개발이 용이해짐
공유하여 사용 가능
반응형
'데이터베이스' 카테고리의 다른 글
| [Database] 20장 PL/SQL 2 (0) | 2024.01.05 |
|---|---|
| [Database] 19장 PL/SQL 1 (2) | 2024.01.03 |
| [Database] 18장 Sequence, Synonym, Index, Data Dictionary 객체 (0) | 2024.01.02 |
| [Database] 17장 연습문제5 (0) | 2023.12.29 |
| [Database] 16장 다른 Table로부터 데이터 입력, 테이블 복사 (0) | 2023.12.28 |