SQL#12-フリップフロップの利用
0. TRIGGER
:
CREATE TABLE MEMBER2_BK AS SELECT * FROM MEMBER2;
:同じテーブル1. INSERT TRIGGER
1.トリガーの作成
CREATE OR REPLACE TRIGGER TRI_MEMBER2_INSERT
-- INSERT가 실행되기 전에 수행됨
BEFORE INSERT ON MEMBER2
-- 행 단위로 실행
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블에 자료 추가 됨');
END;
/
:NEW.컬럼명
CREATE OR REPLACE TRIGGER TRI_MEMBER2_INSERT
-- INSERT가 실행되고 수행됨
AFTER INSERT ON MEMBER2
-- 행 단위로 실행
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블에 자료 추가 됨');
INSERT INTO MEMBER2_BK(USERID, USERPW, USERNAME, USERAGE, USERDATE)
VALUES(:NEW.USERID,:NEW.USERPW,:NEW.USERNAME,:NEW.USERAGE,CURRENT_DATE);
END;
/
2.トリガの確認
INSERTは
INSERT INTO MEMBER2(USERID, USERPW, USERNAME, USERAGE, USERDATE)
VALUES('D','D','DMAN',32,CURRENT_DATE);
COMMIT;
SELECT * FROM MEMBER2;
2. UPDATE TRIGGER
1.トリガーの作成
-- UPDATE 트리거
CREATE OR REPLACE TRIGGER TRI_MEMBER2_UPDATE
AFTER UPDATE ON MEMBER2
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블 자료 변경됨');
DBMS_OUTPUT.PUT_LINE('변경 전 : '||:OLD.USERNAME);
DBMS_OUTPUT.PUT_LINE('변경 후 : '||:NEW.USERNAME);
UPDATE MEMBER2_BK SET USERNAME = :NEW.USERNAME WHERE USERID = :OLD.USERID;
END;
/
CREATE OR REPLACE TRIGGER TRI_MEMBER2_UPDATE_USERAGE
AFTER UPDATE OF USERAGE ON MEMBER2
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블 자료 변경됨');
DBMS_OUTPUT.PUT_LINE('변경 전 : '||:OLD.USERNAME);
DBMS_OUTPUT.PUT_LINE('변경 후 : '||:NEW.USERNAME);
UPDATE MEMBER2_BK SET USERNAME = :NEW.USERNAME WHERE USERID = :OLD.USERID;
END;
/
2.トリガの確認
UPDATE MEMBER2 SET USERNAME = '별이댕댕이' WHERE USERID='C';
COMMIT;
SELECT * FROM MEMBER2;
SELECT * FROM MEMBER2_BK;
3. DELETE TRIGGER
1.トリガーの作成
-- DELETE 트리거
CREATE OR REPLACE TRIGGER TRI_MEMBER2_DELETE
BEFORE DELETE ON MEMBER2
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블 자료 삭제됨');
DBMS_OUTPUT.PUT_LINE('변경 전 : '||:OLD.USERID);
DBMS_OUTPUT.PUT_LINE('변경 후 : '||:NEW.USERID);
DELETE FROM MEMBER2_BK WHERE USERID= :OLD.USERID;
END;
/
2.トリガの確認
-- 트리거 확인
DELETE FROM MEMBER2 WHERE USERID='G';
COMMIT;
SELECT * FROM MEMBER2;
SELECT * FROM MEMBER2_BK;
4.統合TRIGGGER
-- 통합 트리거
CREATE OR REPLACE TRIGGER TRI_MEMBER2_IUD
AFTER INSERT OR DELETE OR UPDATE ON MEMBER2
FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블에 자료 추가 됨');
INSERT INTO MEMBER2_BK(USERID, USERPW, USERNAME, USERAGE, USERDATE)
VALUES(:NEW.USERID,:NEW.USERPW,:NEW.USERNAME,:NEW.USERAGE,CURRENT_DATE);
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블 자료 변경됨');
DBMS_OUTPUT.PUT_LINE('변경 전 : '||:OLD.USERNAME);
DBMS_OUTPUT.PUT_LINE('변경 후 : '||:NEW.USERNAME);
UPDATE MEMBER2_BK SET USERNAME = :NEW.USERNAME WHERE USERID = :OLD.USERID;
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블 자료 삭제됨');
DBMS_OUTPUT.PUT_LINE('OLD : '||:OLD.USERID);
DBMS_OUTPUT.PUT_LINE('NEW : '||:NEW.USERID);
DELETE FROM MEMBER2_BK WHERE USERID= :OLD.USERID;
END IF;
END;
/
5. DISABLE / ENABLE
1.トリガーの無効化
ALTER TRIGGER TRI_MEMBER2_INSERT DISABLE;
2.トリガのアクティブ化
ALTER TRIGGER TRI_MEMBER2_DELETE ENABLE;
6.実習
1.発注時に在庫数量を変更する
SELECT * FROM ORDER2;
SELECT * FROM ITEM2;
CREATE OR REPLACE TRIGGER TRI_ORDER2_INSERT
-- INSERT가 실행되고 수행됨
AFTER INSERT ON ORDER2
-- 행 단위로 실행
FOR EACH ROW
DECLARE
V_QTY NUMBER := 0;
BEGIN
-- ITEM2 테이블에서 ITEMNO가 일치하는 물품의 ITEMQTY를 가져와서 V_QTY에 넣음
SELECT I.ITEMQTY INTO V_QTY FROM ITEM2 I WHERE I.ITEMNO = :NEW.ITEMNO;
-- 주문수량이 재고수량보다 작거나 같아야 됨
IF :NEW.ORDCNT <= V_QTY THEN
-- 물품의 재고수량에서 주문수량만큼을 뺌
UPDATE ITEM2 SET ITEMQTY = ITEMQTY - :NEW.ORDCNT
WHERE ITEMNO = :NEW.ITEMNO;
ELSE
-- 크면 오류출력
RAISE_APPLICATION_ERROR(-20022, '재고부족');
END IF;
-- DBMS_OUTPUT.PUT_LINE('ORDER2 테이블에 자료 추가 됨');
END;
/
-- 주문추가
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
VALUES(SEQ_ORDER2_ORDNO.NEXTVAL, 120, CURRENT_DATE, 1001, 'A');
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
VALUES(SEQ_ORDER2_ORDNO.NEXTVAL, 25, CURRENT_DATE, 1002, 'B');
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
VALUES(SEQ_ORDER2_ORDNO.NEXTVAL, 25, CURRENT_DATE, 1007, 'B');
SELECT * FROM MEMBER2;
SELECT * FROM ORDER2;
SELECT * FROM ITEM2;
SELECT FROM MEMBER2;
SELECT FROM ORDER2;
SELECT * FROM ITEM2;
CREATE OR REPLACE TRIGGER TRI_ORDER2_IUD
AFTER INSERT OR UPDATE OR DELETE ON ORDER2
FOR EACH ROW
DECLARE
V_QTY NUMBER := 0;
V_CNT NUMBER := 0;
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT LINE(「ORDER 2表資料追加済み」);
INSERT INTO ORDER2_BK(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
VALUES(:NEW.ORDNO, :NEW.ORDCNT, CURRENT_DATE, :NEW.ITEMNO, :NEW.USERID);
SELECT ITEMQTY INTO V_QTY FROM ITEM2 WHERE ITEMNO = :NEW.ITEMNO;
IF :NEW.ORDCNT <= V_QTY THEN
--在庫数量から受注数量を差し引く
UPDATE ITEM2 SET ITEMQTY = ITEMQTY - :NEW.ORDCNT
WHERE ITEMNO = :NEW.ITEMNO;
ELSE
--大きい場合は出力エラー
RAISE APPLICATION ERROR(-20022、「在庫不足」);
END IF;
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT LINE(「ORDER 2テーブルデータ修正済」);
DBMS_OUTPUT.PUT LINE(「変更前:」|:OLD.ORDCNT);
DBMS_OUTPUT.PUT LINE(「変更後:」|:NEW.ORDCNT);
UPDATE ORDER2_BK SET ORDCNT = :NEW.ORDCNT WHERE ORDNO = :NEW.ORDNO;
UPDATE ITEM2 SET ITEMQTY = ITEMQTY + (:OLD.ORDCNT - :NEW.ORDCNT)
WHERE ITEMNO = :NEW.ITEMNO;
ELSIF DELETING THEN
DBMS_OUTPUT.PUT LINE(「ORDER 2テーブルデータ削除済」);
DBMS_OUTPUT.PUT_LINE(':OLD.ORDNO : '||:OLD.ORDNO);
DELETE FROM ORDER2_BK WHERE ORDNO = :OLD.ORDNO;
UPDATE ITEM2 SET ITEMQTY = ITEMQTY + :OLD.ORDCNT WHERE ITEMNO = :OLD.ITEMNO;
END IF;
END;
/
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
VALUES(SEQ_ORDER2_ORDNO.NEXTVAL, 2, CURRENT_DATE, 1003, 'A');
UPDATE ORDER2 SET ORDCNT = 110 WHERE ORDNO = 512;
DELETE FROM ORDER2 WHERE ORDNO = 510;
SELECT FROM MEMBER2;
SELECT FROM ORDER2;
SELECT * FROM ITEM2;
完全なコード -- 트리거
-- INSERT, UPDATE, DELETE 등을 하면 자동으로 수행하는 명령
SET SERVEROUT ON;
-- 트리거 생성 (TRI_MEMBER2_INSERT)
CREATE OR REPLACE TRIGGER TRI_MEMBER2_INSERT
-- INSERT가 실행되기 전에 수행됨
BEFORE INSERT ON MEMBER2
-- 행 단위로 실행
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블에 자료 추가 됨');
END;
/
-- 트리거 확인
INSERT INTO MEMBER2(USERID, USERPW, USERNAME, USERAGE, USERDATE)
VALUES('D','D','DMAN',32,CURRENT_DATE);
COMMIT;
SELECT * FROM MEMBER2;
-- 추가, 수정, 삭제 등을 하면 자동으로 백업
CREATE TABLE MEMBER2_BK AS SELECT * FROM MEMBER2;
CREATE TABLE ORDER2_BK AS SELECT * FROM ORDER2;
DROP TABLE MEMBER2_BK;
SELECT * FROM MEMBER2_BK;
-- 자동백업 트리거 생성
-- 트리거 생성 (TRI_MEMBER2_INSERT)
CREATE OR REPLACE TRIGGER TRI_MEMBER2_INSERT
-- INSERT가 실행되고 수행됨
AFTER INSERT ON MEMBER2
-- 행 단위로 실행
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블에 자료 추가 됨');
INSERT INTO MEMBER2_BK(USERID, USERPW, USERNAME, USERAGE, USERDATE)
VALUES(:NEW.USERID,:NEW.USERPW,:NEW.USERNAME,:NEW.USERAGE,CURRENT_DATE);
END;
/
-- 트리거 확인
INSERT INTO MEMBER2(USERID, USERPW, USERNAME, USERAGE, USERDATE)
VALUES('G','G','GIRL',21,CURRENT_DATE);
COMMIT;
SELECT * FROM MEMBER2;
SELECT * FROM MEMBER2_BK;
-----------------------------------------------------------------------------
-- 트리거 생성
-- UPDATE 트리거
CREATE OR REPLACE TRIGGER TRI_MEMBER2_UPDATE
AFTER UPDATE ON MEMBER2
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블 자료 변경됨');
DBMS_OUTPUT.PUT_LINE('변경 전 : '||:OLD.USERNAME);
DBMS_OUTPUT.PUT_LINE('변경 후 : '||:NEW.USERNAME);
UPDATE MEMBER2_BK SET USERNAME = :NEW.USERNAME WHERE USERID = :OLD.USERID;
END;
/
-- 트리거 확인
UPDATE MEMBER2 SET USERNAME = '별이댕댕이' WHERE USERID='C';
COMMIT;
SELECT * FROM MEMBER2;
SELECT * FROM MEMBER2_BK;
-----------------------------------------------------------------------------
-- UPDATE 트리거 감시조건 세밀하게
CREATE OR REPLACE TRIGGER TRI_MEMBER2_UPDATE_USERAGE
AFTER UPDATE OF USERAGE ON MEMBER2
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블 자료 변경됨');
DBMS_OUTPUT.PUT_LINE('변경 전 : '||:OLD.USERNAME);
DBMS_OUTPUT.PUT_LINE('변경 후 : '||:NEW.USERNAME);
UPDATE MEMBER2_BK SET USERNAME = :NEW.USERNAME WHERE USERID = :OLD.USERID;
END;
/
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
-- 트리거 생성
-- DELETE 트리거
CREATE OR REPLACE TRIGGER TRI_MEMBER2_DELETE
BEFORE DELETE ON MEMBER2
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블 자료 삭제됨');
DBMS_OUTPUT.PUT_LINE('변경 전 : '||:OLD.USERID);
DBMS_OUTPUT.PUT_LINE('변경 후 : '||:NEW.USERID);
DELETE FROM MEMBER2_BK WHERE USERID= :OLD.USERID;
END;
/
-- 트리거 확인
DELETE FROM MEMBER2 WHERE USERID='G';
COMMIT;
SELECT * FROM MEMBER2;
SELECT * FROM MEMBER2_BK;
-----------------------------------------------------------------------------
-- 트리거 비활성화
ALTER TRIGGER TRI_MEMBER2_INSERT DISABLE;
-- 트리거 활성화
ALTER TRIGGER TRI_MEMBER2_DELETE ENABLE;
-----------------------------------------------------------------------------
-- 통합 트리거
CREATE OR REPLACE TRIGGER TRI_MEMBER2_IUD
AFTER INSERT OR DELETE OR UPDATE ON MEMBER2
FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블에 자료 추가 됨');
INSERT INTO MEMBER2_BK(USERID, USERPW, USERNAME, USERAGE, USERDATE)
VALUES(:NEW.USERID,:NEW.USERPW,:NEW.USERNAME,:NEW.USERAGE,CURRENT_DATE);
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블 자료 변경됨');
DBMS_OUTPUT.PUT_LINE('변경 전 : '||:OLD.USERNAME);
DBMS_OUTPUT.PUT_LINE('변경 후 : '||:NEW.USERNAME);
UPDATE MEMBER2_BK SET USERNAME = :NEW.USERNAME WHERE USERID = :OLD.USERID;
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블 자료 삭제됨');
DBMS_OUTPUT.PUT_LINE('OLD : '||:OLD.USERID);
DBMS_OUTPUT.PUT_LINE('NEW : '||:NEW.USERID);
DELETE FROM MEMBER2_BK WHERE USERID= :OLD.USERID;
END IF;
END;
/
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
-- 주문했을때 주문수량만큼 물품테이블의 재고수량 변경
SELECT * FROM ORDER2;
SELECT * FROM ITEM2;
CREATE OR REPLACE TRIGGER TRI_ORDER2_INSERT
-- INSERT가 실행되고 수행됨
AFTER INSERT ON ORDER2
-- 행 단위로 실행
FOR EACH ROW
DECLARE
V_QTY NUMBER := 0;
BEGIN
-- ITEM2 테이블에서 ITEMNO가 일치하는 물품의 ITEMQTY를 가져와서 V_QTY에 넣음
SELECT I.ITEMQTY INTO V_QTY FROM ITEM2 I WHERE I.ITEMNO = :NEW.ITEMNO;
-- 주문수량이 재고수량보다 작거나 같아야 됨
IF :NEW.ORDCNT <= V_QTY THEN
-- 물품의 재고수량에서 주문수량만큼을 뺌
UPDATE ITEM2 SET ITEMQTY = ITEMQTY - :NEW.ORDCNT
WHERE ITEMNO = :NEW.ITEMNO;
ELSE
-- 크면 오류출력
RAISE_APPLICATION_ERROR(-20022, '재고부족');
END IF;
-- DBMS_OUTPUT.PUT_LINE('ORDER2 테이블에 자료 추가 됨');
END;
/
-- 주문추가
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
VALUES(SEQ_ORDER2_ORDNO.NEXTVAL, 120, CURRENT_DATE, 1001, 'A');
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
VALUES(SEQ_ORDER2_ORDNO.NEXTVAL, 25, CURRENT_DATE, 1002, 'B');
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
VALUES(SEQ_ORDER2_ORDNO.NEXTVAL, 25, CURRENT_DATE, 1007, 'B');
SELECT * FROM MEMBER2;
SELECT * FROM ORDER2;
SELECT * FROM ITEM2;
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
- 트리거 명 : TRI_ORDER2_IUD
1. 주문 시 재고수량 변경
2. 주문이 추가되면 주문백업 테이블(ORDER2_BK)에 자료 백업
3. 추가, 수정, 삭제
4. 추가시는 재고수량 감소
5. 수정때는 재고수량을 감소, 증가
6. 삭제시는 재고수량 증가
-----------------------------------------------------------------------------
SELECT * FROM MEMBER2;
SELECT * FROM ORDER2;
SELECT * FROM ITEM2;
CREATE OR REPLACE TRIGGER TRI_ORDER2_IUD
AFTER INSERT OR UPDATE OR DELETE ON ORDER2
FOR EACH ROW
DECLARE
V_QTY NUMBER := 0;
V_CNT NUMBER := 0;
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('ORDER2 테이블 자료 추가 됨');
INSERT INTO ORDER2_BK(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
VALUES(:NEW.ORDNO, :NEW.ORDCNT, CURRENT_DATE, :NEW.ITEMNO, :NEW.USERID);
SELECT ITEMQTY INTO V_QTY FROM ITEM2 WHERE ITEMNO = :NEW.ITEMNO;
IF :NEW.ORDCNT <= V_QTY THEN
-- 물품의 재고수량에서 주문수량만큼을 뺌
UPDATE ITEM2 SET ITEMQTY = ITEMQTY - :NEW.ORDCNT
WHERE ITEMNO = :NEW.ITEMNO;
ELSE
-- 크면 오류출력
RAISE_APPLICATION_ERROR(-20022, '재고부족');
END IF;
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('ORDER2 테이블 자료 수정 됨');
DBMS_OUTPUT.PUT_LINE('변경 전 : '||:OLD.ORDCNT);
DBMS_OUTPUT.PUT_LINE('변경 후 : '||:NEW.ORDCNT);
UPDATE ORDER2_BK SET ORDCNT = :NEW.ORDCNT WHERE ORDNO = :NEW.ORDNO;
UPDATE ITEM2 SET ITEMQTY = ITEMQTY + (:OLD.ORDCNT - :NEW.ORDCNT)
WHERE ITEMNO = :NEW.ITEMNO;
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE('ORDER2 테이블 자료 삭제 됨');
DBMS_OUTPUT.PUT_LINE(':OLD.ORDNO : '||:OLD.ORDNO);
DELETE FROM ORDER2_BK WHERE ORDNO = :OLD.ORDNO;
UPDATE ITEM2 SET ITEMQTY = ITEMQTY + :OLD.ORDCNT WHERE ITEMNO = :OLD.ITEMNO;
END IF;
END;
/
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
VALUES(SEQ_ORDER2_ORDNO.NEXTVAL, 2, CURRENT_DATE, 1003, 'A');
UPDATE ORDER2 SET ORDCNT = 110 WHERE ORDNO = 512;
DELETE FROM ORDER2 WHERE ORDNO = 510;
SELECT * FROM MEMBER2;
SELECT * FROM ORDER2;
SELECT * FROM ITEM2;
Reference
この問題について(SQL#12-フリップフロップの利用), 我々は、より多くの情報をここで見つけました
https://velog.io/@aro9515/SQL활용-12-트리거
テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol
-- 트리거
-- INSERT, UPDATE, DELETE 등을 하면 자동으로 수행하는 명령
SET SERVEROUT ON;
-- 트리거 생성 (TRI_MEMBER2_INSERT)
CREATE OR REPLACE TRIGGER TRI_MEMBER2_INSERT
-- INSERT가 실행되기 전에 수행됨
BEFORE INSERT ON MEMBER2
-- 행 단위로 실행
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블에 자료 추가 됨');
END;
/
-- 트리거 확인
INSERT INTO MEMBER2(USERID, USERPW, USERNAME, USERAGE, USERDATE)
VALUES('D','D','DMAN',32,CURRENT_DATE);
COMMIT;
SELECT * FROM MEMBER2;
-- 추가, 수정, 삭제 등을 하면 자동으로 백업
CREATE TABLE MEMBER2_BK AS SELECT * FROM MEMBER2;
CREATE TABLE ORDER2_BK AS SELECT * FROM ORDER2;
DROP TABLE MEMBER2_BK;
SELECT * FROM MEMBER2_BK;
-- 자동백업 트리거 생성
-- 트리거 생성 (TRI_MEMBER2_INSERT)
CREATE OR REPLACE TRIGGER TRI_MEMBER2_INSERT
-- INSERT가 실행되고 수행됨
AFTER INSERT ON MEMBER2
-- 행 단위로 실행
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블에 자료 추가 됨');
INSERT INTO MEMBER2_BK(USERID, USERPW, USERNAME, USERAGE, USERDATE)
VALUES(:NEW.USERID,:NEW.USERPW,:NEW.USERNAME,:NEW.USERAGE,CURRENT_DATE);
END;
/
-- 트리거 확인
INSERT INTO MEMBER2(USERID, USERPW, USERNAME, USERAGE, USERDATE)
VALUES('G','G','GIRL',21,CURRENT_DATE);
COMMIT;
SELECT * FROM MEMBER2;
SELECT * FROM MEMBER2_BK;
-----------------------------------------------------------------------------
-- 트리거 생성
-- UPDATE 트리거
CREATE OR REPLACE TRIGGER TRI_MEMBER2_UPDATE
AFTER UPDATE ON MEMBER2
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블 자료 변경됨');
DBMS_OUTPUT.PUT_LINE('변경 전 : '||:OLD.USERNAME);
DBMS_OUTPUT.PUT_LINE('변경 후 : '||:NEW.USERNAME);
UPDATE MEMBER2_BK SET USERNAME = :NEW.USERNAME WHERE USERID = :OLD.USERID;
END;
/
-- 트리거 확인
UPDATE MEMBER2 SET USERNAME = '별이댕댕이' WHERE USERID='C';
COMMIT;
SELECT * FROM MEMBER2;
SELECT * FROM MEMBER2_BK;
-----------------------------------------------------------------------------
-- UPDATE 트리거 감시조건 세밀하게
CREATE OR REPLACE TRIGGER TRI_MEMBER2_UPDATE_USERAGE
AFTER UPDATE OF USERAGE ON MEMBER2
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블 자료 변경됨');
DBMS_OUTPUT.PUT_LINE('변경 전 : '||:OLD.USERNAME);
DBMS_OUTPUT.PUT_LINE('변경 후 : '||:NEW.USERNAME);
UPDATE MEMBER2_BK SET USERNAME = :NEW.USERNAME WHERE USERID = :OLD.USERID;
END;
/
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
-- 트리거 생성
-- DELETE 트리거
CREATE OR REPLACE TRIGGER TRI_MEMBER2_DELETE
BEFORE DELETE ON MEMBER2
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블 자료 삭제됨');
DBMS_OUTPUT.PUT_LINE('변경 전 : '||:OLD.USERID);
DBMS_OUTPUT.PUT_LINE('변경 후 : '||:NEW.USERID);
DELETE FROM MEMBER2_BK WHERE USERID= :OLD.USERID;
END;
/
-- 트리거 확인
DELETE FROM MEMBER2 WHERE USERID='G';
COMMIT;
SELECT * FROM MEMBER2;
SELECT * FROM MEMBER2_BK;
-----------------------------------------------------------------------------
-- 트리거 비활성화
ALTER TRIGGER TRI_MEMBER2_INSERT DISABLE;
-- 트리거 활성화
ALTER TRIGGER TRI_MEMBER2_DELETE ENABLE;
-----------------------------------------------------------------------------
-- 통합 트리거
CREATE OR REPLACE TRIGGER TRI_MEMBER2_IUD
AFTER INSERT OR DELETE OR UPDATE ON MEMBER2
FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블에 자료 추가 됨');
INSERT INTO MEMBER2_BK(USERID, USERPW, USERNAME, USERAGE, USERDATE)
VALUES(:NEW.USERID,:NEW.USERPW,:NEW.USERNAME,:NEW.USERAGE,CURRENT_DATE);
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블 자료 변경됨');
DBMS_OUTPUT.PUT_LINE('변경 전 : '||:OLD.USERNAME);
DBMS_OUTPUT.PUT_LINE('변경 후 : '||:NEW.USERNAME);
UPDATE MEMBER2_BK SET USERNAME = :NEW.USERNAME WHERE USERID = :OLD.USERID;
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE('MEMBER2 테이블 자료 삭제됨');
DBMS_OUTPUT.PUT_LINE('OLD : '||:OLD.USERID);
DBMS_OUTPUT.PUT_LINE('NEW : '||:NEW.USERID);
DELETE FROM MEMBER2_BK WHERE USERID= :OLD.USERID;
END IF;
END;
/
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
-- 주문했을때 주문수량만큼 물품테이블의 재고수량 변경
SELECT * FROM ORDER2;
SELECT * FROM ITEM2;
CREATE OR REPLACE TRIGGER TRI_ORDER2_INSERT
-- INSERT가 실행되고 수행됨
AFTER INSERT ON ORDER2
-- 행 단위로 실행
FOR EACH ROW
DECLARE
V_QTY NUMBER := 0;
BEGIN
-- ITEM2 테이블에서 ITEMNO가 일치하는 물품의 ITEMQTY를 가져와서 V_QTY에 넣음
SELECT I.ITEMQTY INTO V_QTY FROM ITEM2 I WHERE I.ITEMNO = :NEW.ITEMNO;
-- 주문수량이 재고수량보다 작거나 같아야 됨
IF :NEW.ORDCNT <= V_QTY THEN
-- 물품의 재고수량에서 주문수량만큼을 뺌
UPDATE ITEM2 SET ITEMQTY = ITEMQTY - :NEW.ORDCNT
WHERE ITEMNO = :NEW.ITEMNO;
ELSE
-- 크면 오류출력
RAISE_APPLICATION_ERROR(-20022, '재고부족');
END IF;
-- DBMS_OUTPUT.PUT_LINE('ORDER2 테이블에 자료 추가 됨');
END;
/
-- 주문추가
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
VALUES(SEQ_ORDER2_ORDNO.NEXTVAL, 120, CURRENT_DATE, 1001, 'A');
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
VALUES(SEQ_ORDER2_ORDNO.NEXTVAL, 25, CURRENT_DATE, 1002, 'B');
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
VALUES(SEQ_ORDER2_ORDNO.NEXTVAL, 25, CURRENT_DATE, 1007, 'B');
SELECT * FROM MEMBER2;
SELECT * FROM ORDER2;
SELECT * FROM ITEM2;
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
- 트리거 명 : TRI_ORDER2_IUD
1. 주문 시 재고수량 변경
2. 주문이 추가되면 주문백업 테이블(ORDER2_BK)에 자료 백업
3. 추가, 수정, 삭제
4. 추가시는 재고수량 감소
5. 수정때는 재고수량을 감소, 증가
6. 삭제시는 재고수량 증가
-----------------------------------------------------------------------------
SELECT * FROM MEMBER2;
SELECT * FROM ORDER2;
SELECT * FROM ITEM2;
CREATE OR REPLACE TRIGGER TRI_ORDER2_IUD
AFTER INSERT OR UPDATE OR DELETE ON ORDER2
FOR EACH ROW
DECLARE
V_QTY NUMBER := 0;
V_CNT NUMBER := 0;
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('ORDER2 테이블 자료 추가 됨');
INSERT INTO ORDER2_BK(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
VALUES(:NEW.ORDNO, :NEW.ORDCNT, CURRENT_DATE, :NEW.ITEMNO, :NEW.USERID);
SELECT ITEMQTY INTO V_QTY FROM ITEM2 WHERE ITEMNO = :NEW.ITEMNO;
IF :NEW.ORDCNT <= V_QTY THEN
-- 물품의 재고수량에서 주문수량만큼을 뺌
UPDATE ITEM2 SET ITEMQTY = ITEMQTY - :NEW.ORDCNT
WHERE ITEMNO = :NEW.ITEMNO;
ELSE
-- 크면 오류출력
RAISE_APPLICATION_ERROR(-20022, '재고부족');
END IF;
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('ORDER2 테이블 자료 수정 됨');
DBMS_OUTPUT.PUT_LINE('변경 전 : '||:OLD.ORDCNT);
DBMS_OUTPUT.PUT_LINE('변경 후 : '||:NEW.ORDCNT);
UPDATE ORDER2_BK SET ORDCNT = :NEW.ORDCNT WHERE ORDNO = :NEW.ORDNO;
UPDATE ITEM2 SET ITEMQTY = ITEMQTY + (:OLD.ORDCNT - :NEW.ORDCNT)
WHERE ITEMNO = :NEW.ITEMNO;
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE('ORDER2 테이블 자료 삭제 됨');
DBMS_OUTPUT.PUT_LINE(':OLD.ORDNO : '||:OLD.ORDNO);
DELETE FROM ORDER2_BK WHERE ORDNO = :OLD.ORDNO;
UPDATE ITEM2 SET ITEMQTY = ITEMQTY + :OLD.ORDCNT WHERE ITEMNO = :OLD.ITEMNO;
END IF;
END;
/
INSERT INTO ORDER2(ORDNO, ORDCNT, ORDDATE, ITEMNO, USERID)
VALUES(SEQ_ORDER2_ORDNO.NEXTVAL, 2, CURRENT_DATE, 1003, 'A');
UPDATE ORDER2 SET ORDCNT = 110 WHERE ORDNO = 512;
DELETE FROM ORDER2 WHERE ORDNO = 510;
SELECT * FROM MEMBER2;
SELECT * FROM ORDER2;
SELECT * FROM ITEM2;
Reference
この問題について(SQL#12-フリップフロップの利用), 我々は、より多くの情報をここで見つけました https://velog.io/@aro9515/SQL활용-12-트리거テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol