DATABASE TRIGGER INTRODUCTION 프로그램단위 1 의하나인트리거 2 (Trigger) 는테이블, 뷰, 스키마또는데이터베이스에관련된 PLSQL 블록 ( 또는프로시저 ) 으로관련된특정사건 (Event) 이발생될때마다묵시적 ( 자동 ) 으로실행이이루어진다 (Fire). 트리거를생성할때관련된사건 (Event) 도함께정의되며, 실행부에서저장된프로시저 ( 함수, 패키지 ) 를호출할수도있다. 생성이된후에는바로활성화가되어모든사용자에대해그리고정의된특정사건 (Event) 에대해실행 (Fire) 된다. 트리거는데이터베이스내에오브젝트로서저장되어관리된다. 트리거는명시적인호출 ( 실행 ) 을할수없으며, 특정사건 (Event) 에의해서만묵시적인자동실행 (Fire) 이이루어진다. 데이터베이스트리거를너무많이생성하게되면관련오브젝트에대한복잡한종속관계가구성되므로성능저하를가져오게된다. 그러므로순환적이거나상호참조가이루어지지않도록트리거에대한설계를신중히해야한다. 데이터베이스트리거는기존의오라클기능을중복하거나치환해서정의할수있으나이는매우치명적인결과를초래할수도있으므로지양하기바란다. 데이터베이스를트리거를사용하는이유는여러가지가있지만, 크게두가지로요약해볼수있다. 첫째는향상된데이터보안 (Data Security) 을구현할수있다는것인데, 이는기존의보안을강화하고복잡한보안점검도할수있으며더불어데이터베이스에대한감사 (Audit) 기능을제공하기도한다. 둘째는향상된데이터무결성 (Data Integrity) 을보장한다는것인데, 이는기존의제약조건에비즈니스로직을추가할수도있으며, 복잡한참조무결성에대한구현도가능하며, 기존의제약조건에서처리할수없던기능도추가할수도있다. 1 PLSQL 블록은데이터베이스에오브젝트로서저장될수있는데, 이를서브프로그램또는프로그램단위라부르며, 그유형은프로시저 (Procedure), 함수 (Function), 패키지 (Package), 트리거 (Trigger) 가있다 2 이장에서언급되는트리거는데이터베이스트리거를의미한다. 트리거는응용프로그램내에서도정의되어사용될수있는데, 이를응용프로그램트리거라고한다. 이는응용프로그램 ( 예 : 오라클디벨로퍼 ) 내에서정의된특정사건 (Event) - 예를들면, 마우스가클릭되었을때또는메뉴가선택되었을때등 - 이발생할때마다자동실행되는프로그램단위이다. 이는데이터베이스트리거와는독립적으로실행된다. Written by AngelaLEE 7-1 www.muhanedu.net
일반적으로트리거에대한조작은 CREATEALTERDROP(ANY) TRIGGER 의시스템권한이필요하며, DATABASE 레벨에서의트리거조작은 ADMINISTER DATABASE TRIGGER 시스템권한이필요하다. 트리거에대한 EXECUTE 권한에대해서는트리거관련오브젝트또는대상에대한접근권한이있다면간접적인권한부여가묵시적으로이루어진다. 트리거에대한정보는 USER_OBJECTS, USER_TRIGGERS, USER_ERRORS 딕셔너리뷰들을참조한다. 트리거를이루는트리거몸체 ( 실행부 ) 에트랜잭션관련명령, 즉 COMMIT, ROLLBACK, SAVEPOINT 명령이포함될수없음에주의한다. 데이터베이스트리거는오라클서버에구현되지않은기능을보강할수도있으며, 오라클서버가제공하는기능을대체하기도한다. 아래의표는데이터베이스트리거가활용 ( 구현 ) 되는분야에대해요약해놓은것이다. Security Auditing Feature Enhancement The Oracle Server allows table access to users or roles. Triggers allow table access according to data values. The Oracle Server tracks data operations on tables. Triggers track values for data operation on tables. Data Integrity The Oracle Server enforces integrity constraints. Triggers implement complex integrity rules. Referential Integrity The Oracle Server enforces standard referential integrity rules. Triggers implement nonstandard functionality. Table Replication The Oracle Server copies tables asynchronously into snapshots. Triggers copy tables synchronously into replicas. Derived data Event logging The Oracle Server computes derived data values manually. Triggers compute derived data values automatically. The Oracle Server logs events explicitly. Triggers log events transparently. Written by AngelaLEE 7-2 www.muhanedu.net
TRIGGER Structure 데이터베이스트리거는트리거가실행되는시점 (Timing), 트리거를실행 (Firing) 시키는사건 (Event), 트리거와관련된테이블 뷰 스키마 데이터베이스그리고트리거몸체부 (Body) 로구성된다. 데이터베이스트리거는트리거를실행 (Firing) 시키는사건 (Event) 에따라구분해볼수있는데, 하나는특정테이블 뷰에대한 DML 문과관련된트리거이며다른하나는데이터베이스또는스키마레벨에서의시스템사건 (System Event) 과관련된트리거이다. 데이터베이스레벨에서정의된트리거는모든유저에대해실행 (Fire) 되며, 스키마 (Schema) 나테이블에정의된트리거는관련된스키마나테이블에포함된트리거사건 (Triggering Event) 에대해서만실행 (Fire) 된다. Written by AngelaLEE 7-3 www.muhanedu.net
DATABASE TRIGGER 생성 > SYNTAX: CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [ OR event2 OR event3 ] ON {table_name view_name SCHEMA DATABASE} [REFERENCING OLD AS old NEW AS new] [FOR EACH ROW [WHEN ( condition ) ] ] trigger_body OR REPLACE 생성하고자하는패키지가기존에동일명으로존재할경우, 기존의내용을현재의내용으로수정하는옵션.( 만약기존에존재하지않는다면큰의미는없다.) trigger_name 생성되는트리거의이름으로같은도메인내에서는중복되어서는안된다. 기본적으로오라클의 Naming Rule 을준수해야한다. timing 트리거가실행되는시점을지정하는것으로 event 발생전과후를의미하는 BEFORE 와 AFTER 가있으며, 트리거가특정뷰 (View) 에대한 DML 일경우에는이 timing 부분에 INSTEAD OF 를사용한다. 이들은상호배타적으로사용된다. event 트리거를실행 (Fire) 시키는사건 (Event) 으로테이블 뷰에관련된 DML_Event 와 SCHEMADATABASE 에관련된 DDL_Event 와 DATABASE_Event 로구분된다. > DML Event INSERT UPDATE [OF column] DELETE Written by AngelaLEE 7-4 www.muhanedu.net
> DDL Event CREATE ALTER DROP > DATABASE Event [AFTER] SERVERERROR [AFTER] LOGON [BEFORE] LOGOFF [AFTER] STARTUP* [BEFORE] SHUTDOWN* * 은 DATABASE 에대해서만가능 ON 트리거가발생되는레벨또는대상을지정하는절로트리거의대상인테이블 뷰이름을기술하거나트리거의레벨인 SCHEMA 또는 DATABASE 를지정한다. 이레벨이나대상에따라사건 (Event) 이구분된다. REFERENCING 테이블 뷰의행트리거 (Row Trigger) 와관련된키워드로, 처리되는각각의행에대해변경전의값과변경후의값을참조할수있도록참조할수있는이름을재명명할수있는절로디폴트 ( 기본값 ) 는 OLD 와 NEW 이다. FOR EACH ROW 테이블 뷰의트리거를행트리거 (Row Trigger) 로명시하는절로, 뷰의 INSTEAD OF 에대해서는기본적으로행트리거로서정의되므로생략이가능하다. WHEN (condition) 테이블 뷰의행트리거 (Row Trigger) 의각행에대해제약을주는절로트리거몸체부의실행여부에무관하게실행되는절이다. 조건 (Condition) 은반드시괄호로둘러싼다. trigger_body 트리거에의해실행될부분을정의하는곳으로, Anonymous PLSQL Block 구조가올수있으며또한프로시저 (PLSQL, C, JAVA) 를호출할수도있다.(CALL 문을사용하며 ; 을붙이지않는다.) Written by AngelaLEE 7-5 www.muhanedu.net
DATABASE TRIGGER 관리 데이터베이스트리거의활성화 비활성화 ALTER TRIGGER trigger_name DISABLE ENABLE ; 모든데이터베이스트리거의활성화 비활성화 ALTER TABLE table_name DISABLE ENABLE ALL TRIGGERS ; 데이터베이스트리거의재번역 ALTER TRIGGER trigger_name COMPILE ; DATABASE TRIGGER 삭제 DROP TRIGGER trigger_name ; Written by AngelaLEE 7-6 www.muhanedu.net
실습 DATABASE TRIGGER 1. [ TRIGGER I : DML Statement Trigger(1) ] 보안 (Security) 접근제한과관련된예제로, EMP 테이블에 INSERT 문이실행되기전마다묵시적인호출이되는트리거. CREATE OR REPLACE TRIGGER secure_emp BEFORE INSERT ON emp IF END IF ; (TO_CHAR(SYSDATE, DY ) IN ( SAT, SUN )) OR (TO_CHAR(SYSDATE, HH24:MI ) NOT BETWEEN 08:00 AND 18:00 ) THEN RAISE_APPLICATION_ERROR(-20500, You may insert into EMP table only during business hours. ) ; Hint : 생성된트리거는 EMP 테이블에 INSERT 문이실행될때에만자동호출되므로트리거의검증을위해서 EMP 테이블에행을삽입해본다. INSERT INTO EMP (EMPNO, ENAME, SAL) VALUES (9100, ANGELA,4000) ; 만약 INSERT 문이성공했다면, 정상적인시간대에작업한것이나 ORA-20500 예외가발생했다면, 비정상적인시간대에작업한결과일것이다. Point : 트리거의실패는 INSERT 문의실패와직결된다. 즉, 트리거가비정상종료되면관련 DML 문도실패한다. 예를들어, 한테이블에대해 INSERT 문에관련된트리거가여러개있을경우 INSERT 문의성공은관련트리거가모두성공해야만한다. Written by AngelaLEE 7-7 www.muhanedu.net
2. [ TRIGGER II : DML Statement Trigger(2)] 1 번의트리거를수정한것으로, DML 문의종류에따라다르게처리하는트리거예제.. CREATE OR REPLACE TRIGGER secure_emp01 BEFORE INSERT OR UPDATE OR DELETE ON emp IF (TO_CHAR(SYSDATE, DY ) IN ( SAT, SUN )) OR (TO_CHAR(SYSDATE, HH24:MI ) NOT BETWEEN 08:00 AND 18:00 ) THEN IF DELETITNG THEN RAISE_APPLICATION_ERROR(-20501, You may delete from EMP table only during business hours. ) ; ELSIF INSERTING THEN RAISE_APPLICATION_ERROR(-20502, You may insert into EMP table only during business hours. ) ; ELSIF UPDATING( SAL ) THEN ELSE RAISE_APPLICATION_ERROR(-20503, You may update SAL column only during business hours. ) ; RAISE_APPLICATION_ERROR(-20504, You may update EMP table only during business hours. ) ; END IF ; END IF ; QUIZ : ORA-20501 예외를발생시키려면어떤문장을실행해야하는가? ORA-20504 예외를발생시키려면어떤문장을실행해야하는가? Written by AngelaLEE 7-8 www.muhanedu.net
3. [ TRIGGER III : DML Row Trigger(1)] DML 문에의해처리되는각각의행마다수행되는예제로변경전 후의값을참조하는트리거. CREATE OR REPLACE TRIGGER validate_sal BEFORE INSERT OR UPDATE OF sal ON emp FOR EACH ROW IF :NEW.sal <= 100 THEN END IF ; QUIZ : RAISE_APPLICATION_ERROR(-20600, Wrong InputUpdate : SAL column value must be greater than 100. ) ; ORA-20600 예외를발생시키려면어떤문장을실행해야할까요? 참고 : OLD, NEW 식별자에대한다른예제. CREATE OR REPLACE TRIGGER audit_emp_values AFTER DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW INSERT INTO audit_emp_table(user_name, timestamp, id, old_ename, new_ename, old_job, new_job, old_sal, new_sal) VALUES(USER,SYSDATE, :OLD.empno, :OLD.ename,:NEW.ename,:OLD.job,:NEW.job,:OLD.sal,:NEW.sal); END ; Written by AngelaLEE 7-9 www.muhanedu.net
4. [ TRIGGER IV : DML Row Trigger(2)] DML 문에의해처리되는각각의행마다수행되는행트리거예제로, 처리되기전에행에대한제한을두는트리거. CREATE OR REPLACE TRIGGER comm_derive BEFORE INSERT OR UPDATE OF sal ON emp FOR EACH ROW WHEN (NEW.ename LIKE %A% ) IF INSERTING THEN :NEW.comm := 0 ; ELSIF :OLD.comm IS NULL THEN :NEW.comm := 0 ; ELSE :NEW.comm := :OLD.comm + 50 ; END IF ; NOTE : Written by AngelaLEE 7-10 www.muhanedu.net
5. [ TRIGGER V : INSTEAD OF Trigger ] 뷰에대한트리거예제 CREATE OR REPLACE TRIGGER new_emp_dept INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_detail FOR EACH ROW IF INSERTING THEN INSERT INTO new_emp VALUES(:NEW.empno,:NEW.ename,:NEW.sal,:NEW.deptno,:NEW.job, SYSDATE); UPDATE new_dept SET tot_dept_sal = tot_dept_sal + :NEW.sal WHERE deptno = :NEW.deptno ; ELSIF DELETING THEN DELETE new_emp WHERE empno = :OLD.empno ; UPDATE new_dept SET tot_dept_sal = tot_dept_sal - :OLD.sal WHERE deptno = :OLD.deptno ; ELSIF UPDATING( sal ) THEN UPDATE new_emp SET sal = :NEW.sal WHERE empno = :NEW.empno ; UPDATE new_dept SET tot_dept_sal = tot_dept_sal + (:NEW.sal - :OLD.sal) WHERE deptno = :OLD.deptno ; ELSIF UPDATING( deptno) THEN UPDATE new_emp SET deptno = :NEW.dpetno WHERE empno = :NEW.empno ; UPDATE new_dept SET tot_dept_sal = tot_dept_sal - :OLD.sal) WHERE deptno = :OLD.deptno ; UPDATE new_dept SET tot_dept_sal = tot_dept_sal + :NEW.sal WHERE deptno = :NEW.deptno ; END IF ; ( 다음페이지에계속 ) Written by AngelaLEE 7-11 www.muhanedu.net
HINT : 관련테이블과뷰 CREATE TABLE NEW_EMP AS SELECT EMPNO, ENAME, SAL, DEPTNO, JOB, HIRE_DATE FROM EMP ; CREATE TABLE NEW_DEPT AS SELECT D.DEPTNO, D.DNAME, D.LOC, SUM(E.SAL) TOT_DEPT_SAL FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO GROUP BY D.DEPTNO, D.DNAME, D.LOC ; CREATE VIEW EMP_DETAIL AS SELECT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, E.JOB, D.DNAME, D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO ; MEMO : Written by AngelaLEE 7-12 www.muhanedu.net
6. [ TRIGGER VI : CALL 문 ] 트리거에서외부에저장된프로시저 (PLSQL, C, JAVA) 를트리거내부에서호출하는 CALL 문에대한예제. CREATE OR REPLACE TRIGGER log_emp BEFORE INSERT ON emp CALL log_execution CREATE OR REPLACE TRIGGER sal_check BEFORE UPDATE OF sal, ename ON emp FOR EACH ROW WHEN (NEW.ename = UPPER(NEW.ename) ) CALL check_sal(:new.sal ) HINT : Written by AngelaLEE 7-13 www.muhanedu.net
7. [ TRIGGER VII : SCHEMADATABASE Trigger ] 특정사용자에대해로그온할때와로그오프할때처리하고자하는트리거예제. CREATE OR REPLACE TRIGGER logon_trigger AFTER LOGON ON SCHEMA INSERT INTO log_trigger_table(user_id, log_date, action) VALUES(USER, SYSDATE, Logging On ) ; CREATE OR REPLACE TRIGGER logooff_trigger AFTER LOGON OFF SCHEMA INSERT INTO log_trigger_table(user_id, log_date, action) VALUES(USER, SYSDATE, Logging Off ) ; QUIZ: 만약위의트리거들을 ON DATABASE 로생성할때의의미는? Written by AngelaLEE 7-14 www.muhanedu.net
8. [ TRIGGER VIII : MUTATING TABLE related Trigger ] 특정사용자에대해로그온할때와로그오프할때처리하고자하는트리거예제. CREATE OR REPLACE TRIGGER mutating_emp BEFORE INSERT OR UPDATE OF sal, job ON emp FOR EACH ROW WHEN (NEW.job <> PRESIDENT ) DECLARE v_minsal emp.sal%type ; v_maxsal emp.sal%type ; SELECT MIN(sal), MAX(sal) INTO v_minsal, v_maxsal FROM emp WHERE job = :NEW.job ; IF (:NEW.sal < v_minsal) OR (:NEW.sal > v_maxsal) THEN RAISE_APPLICATION_ERROR(-20700, Out of range!!! ) ; END IF ; Written by AngelaLEE 7-15 www.muhanedu.net
HINT: 위의트리거를활성화시키는예문. UPDATE EMP SET SAL = 3500 WHERE ENAME = SCOTT ; POINT: Mutating Table 은현재 DML(INSERT, UPDATE, INSERT) 에의해수정되고있는테이블이거나참조무결성 (DELETE CASCADE option) 이설정된경우자동적으로갱신될필요가있는테이블을일컫는다. 만약트리거가발생한테이블이 Mutating Table 일경우, 그리고트리거가행트리거인경우트리거내부에서다시자기테이블을참조하고자할때오라클은 ORA-04091 예외를발생한다. 왜냐하면데이터의일관성이무너지기때문이다. Written by AngelaLEE 7-16 www.muhanedu.net
9. [ TRIGGER VIIII : Data Integrity vs. Trigger (1) ] 오라클서버에서의데이터무결성과트리거에서의데이터무결성보장을이해하는예제 ALTER TABLE emp ADD CONSTRAINT emp_sal_ck CHECK (sal > 100) ; CREATE OR REPLACE TRIGGER val_sal BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (NEW.sal < OLD.sal ) RAISE_APPLICATION_ERROR(-20800, Do not decrease salary ) ; NOTE: Written by AngelaLEE 7-17 www.muhanedu.net
10. [ TRIGGER X : Data Integrity vs. Trigger (2) ] 오라클서버에서의데이터무결성과트리거에서의데이터무결성보장을이해하는예제 ALTER TABLE emp ADD CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE CASCADE ; CREATE OR REPLACE TRIGGER cascade_update AFTER UPDATE OF deptno ON dept FOR EACH ROW UPDATE emp SET emp.deptno = :NEW.deptno WHERE emp.deptno = :OLD.deptno ; UPDATE SET WHERE job_history department_id = :NEW.department_id department_id = :OLD.department_id NOTE: Written by AngelaLEE 7-18 www.muhanedu.net