2022_11_28 SQL TRIGGER

/*
trigger 는 테이블에 부적한 형태로써 insert, update, delete작업이 수행될 때 특정 코드가 작동되도록 하는 구문입니다.
트리거는 실행할 때 범위를 지정하고 F5버튼으로 부분실행해야 한다.
그렇지 않으면 하나의 구문으로 인식되어 정상동작하지 않습니ㅏㄷ.
*/

CREATE TABLE tbl_test (
    id NUMBER  (10),
    text VARCHAR2(20)
);


set SERVEROUTPUT on;

/*
CREATE OR REPLACE TRIGGER trg_test 
    AFTER UPDATE OR DELETE      --삭제, 수정 이후에 동작
    ON tbl_test                 --부착할 테이블
    FOR EACH ROW                --각행에 적용
DECLARE
    
BEGIN
    DBMS_OUTPUT.PUT_LINE('트리거가 동작함');
    
END;

*/


INSERT INTO tbl_test VALUES(1,'홍길동');
INSERT INTO tbl_test VALUES(2,'이순신');
UPDATE tbl_test SET TEXT = '홍길동' WHERE id=1;
DELETE FROM tbl_test WHERE id=2; 

---
--BEFORE VS AFTER  
/*
AFTER트리거 - INSERT, UPDATE, DELETE 작업 이후에 동작하는 트리거를 의미함
BEFORE트리거 - INSERT, UPDATE, DELETE 작업 이전에 동작하는 트리거를 의미함

:OLD = 참조 전 열의값 ( INSERT : 입력 전 자료, UPDATE: 수정 전 자료, DELETE 삭제 할 자료)
:NEW = 참조 후 열의 값 ( INSERT : 입력 후 자료, UPDATE: 수정 후 자료)
*/

CREATE TABLE tbl_user_backup(
    id VARCHAR2(20),
    name VARCHAR2(20),
    address VARCHAR2(30),
    updatedate DATE DEFAULT sysdate,
    m_type CHAR(10),        --변경 타입
    m_user VARCHAR2(20)     --변경한 사용자
);

CREATE TABLE tbl_user(
    id VARCHAR2(20) PRIMARY KEY,
    name VARCHAR2(20),
    address VARCHAR2(30)
);

--UPDATE 나 DELETE 를 시도하면 수정 또는 삭제된 데이터를 별도의 테이블에 보관

CREATE OR REPLACE TRIGGER trg_user_backup
    AFTER UPDATE OR DELETE
    ON tbl_user
    FOR EACH ROW
DECLARE         --변수 선언
    vn_type VARCHAR2(10);
BEGIN
    
    IF updating THEN        --업데이트 OR 삭제
        vn_type :='수정';
    ELSIF deleting THEN
        vn_type :='삭제';
    END IF;
    
    --USER BACKUP에 INSERT
    INSERT INTO tbl_user_backup VALUES(:OLD.id, :OLD.name, :OLD.address, sysdate, vn_type, user() );



END;


INSERT INTO tbl_user VALUES( 'TEST01', 'ADMIN', '서울');
INSERT INTO tbl_user VALUES( 'TEST02', 'ADMIN', '경기');
INSERT INTO tbl_user VALUES( 'TEST03', 'ADMIN', '부산');
UPDATE tbl_user SET address = '부산' WHERE id = 'TEST01';
DELETE FROM tbl_user WHERE id = 'TEST02';


SELECT * FROM tbl_user_backup;

------------------------------------------------------------------------------
--BEFORE

CREATE OR REPLACE TRIGGER trg_user_insert
    BEFORE INSERT
    ON tbl_user
    FOR EACH ROW
DECLARE
    
BEGIN
    :NEW.name := substr(:NEW.name,1,1) || '**';     --입력전 데이터 ** 붙임
END;

INSERT INTO tbl_user VALUES( 'TEST04', 'ADMIN', '부산');
INSERT INTO tbl_user VALUES( 'TEST05', 'ADMIN', '경기');

SELECT * FROM tbl_user;