데이터베이스 질문과 답변
-- 0. 패키지에서 변경된 자료를 저장할 변수 정의 --
CREATE OR REPLACE PACKAGE pkg_test
IS
TYPE tp_order_no IS TABLE OF lt_inaudit.order_no %TYPE INDEX BY BINATY_INTEGER;
TYPE tp_orderseq IS TABLE OF lt_inaudit.orderseq %TYPE INDEX BY BINATY_INTEGER;
TYPE tp_item_no IS TABLE OF lt_inaudit.item_no %TYPE INDEX BY BINATY_INTEGER;
TYPE tp_saup_no IS TABLE OF lt_inaudit.saup_no %TYPE INDEX BY BINATY_INTEGER;
TYPE tp_issue_qty IS TABLE OF lt_inaudit.issue_qty%TYPE INDEX BY BINATY_INTEGER;
v_order_no tp_order_no ;
v_orderseq tp_orderseq ;
v_item_no tp_item_no ;
v_saup_no tp_saup_no ;
v_issue_qty tp_issue_qty;
i BINARY_INTEGER;
END;
/
-- 1. 문장-트리거 에서 의 DML 실행전(Before) 패키지 변수 초기화 --
CREATE OR REPLACE TRIGGER tr_test_1
BEFORE DELETE ON lt_inaudit
BEGIN
pkg_test.i := 0;
END;
/
-- 2. 행-트리거 에서 패키지 변수에 변경된 값을 저장 --
CREATE OR REPLACE TRIGGER tr_test_2
BEFORE DELETE ON lt_inaudit
FOR EACH ROW
BEGIN
pkg_test.i := pkg_test.i + 1;
pkg_test.v_order_no (pkg_test.i) := :OLD.order_no ;
pkg_test.v_orderseq (pkg_test.i) := :OLD.orderseq ;
pkg_test.v_item_no (pkg_test.i) := :OLD.item_no ;
pkg_test.v_saup_no (pkg_test.i) := :OLD.saup_no ;
pkg_test.v_issue_qty(pkg_test.i) := :OLD.issue_qty;
END;
/
-- 3. 문장-트리거 에서 의 DML 실행후(After) 별도 DML 처리 --
CREATE OR REPLACE TRIGGER tr_test_3
AFTER DELETE ON lt_inaudit
BEGIN
FOR i IN 1..pkg_test.i LOOP
UPDATE lt_inaudit
SET roll_qty = roll_qty - pkg_test.v_issue_qty(i)
WHERE insrl_no = pkg_test.v_order_no (i)
AND insrl_seq = pkg_test.v_orderseq (i)
AND item_no = pkg_test.v_item_no (i)
AND saup_no = pkg_test.v_saup_no (i)
;
END LOOP;
END;
/
CREATE OR REPLACE PACKAGE pkg_test
IS
TYPE tp_order_no IS TABLE OF lt_inaudit.order_no %TYPE INDEX BY BINATY_INTEGER;
TYPE tp_orderseq IS TABLE OF lt_inaudit.orderseq %TYPE INDEX BY BINATY_INTEGER;
TYPE tp_item_no IS TABLE OF lt_inaudit.item_no %TYPE INDEX BY BINATY_INTEGER;
TYPE tp_saup_no IS TABLE OF lt_inaudit.saup_no %TYPE INDEX BY BINATY_INTEGER;
TYPE tp_issue_qty IS TABLE OF lt_inaudit.issue_qty%TYPE INDEX BY BINATY_INTEGER;
v_order_no tp_order_no ;
v_orderseq tp_orderseq ;
v_item_no tp_item_no ;
v_saup_no tp_saup_no ;
v_issue_qty tp_issue_qty;
i BINARY_INTEGER;
END;
/
-- 1. 문장-트리거 에서 의 DML 실행전(Before) 패키지 변수 초기화 --
CREATE OR REPLACE TRIGGER tr_test_1
BEFORE DELETE ON lt_inaudit
BEGIN
pkg_test.i := 0;
END;
/
-- 2. 행-트리거 에서 패키지 변수에 변경된 값을 저장 --
CREATE OR REPLACE TRIGGER tr_test_2
BEFORE DELETE ON lt_inaudit
FOR EACH ROW
BEGIN
pkg_test.i := pkg_test.i + 1;
pkg_test.v_order_no (pkg_test.i) := :OLD.order_no ;
pkg_test.v_orderseq (pkg_test.i) := :OLD.orderseq ;
pkg_test.v_item_no (pkg_test.i) := :OLD.item_no ;
pkg_test.v_saup_no (pkg_test.i) := :OLD.saup_no ;
pkg_test.v_issue_qty(pkg_test.i) := :OLD.issue_qty;
END;
/
-- 3. 문장-트리거 에서 의 DML 실행후(After) 별도 DML 처리 --
CREATE OR REPLACE TRIGGER tr_test_3
AFTER DELETE ON lt_inaudit
BEGIN
FOR i IN 1..pkg_test.i LOOP
UPDATE lt_inaudit
SET roll_qty = roll_qty - pkg_test.v_issue_qty(i)
WHERE insrl_no = pkg_test.v_order_no (i)
AND insrl_seq = pkg_test.v_orderseq (i)
AND item_no = pkg_test.v_item_no (i)
AND saup_no = pkg_test.v_saup_no (i)
;
END LOOP;
END;
/
) 버튼을 클릭하여 작성 하시면 됩니다.