17.1 데이터베이스트리거 17.2 DML 트리거 17.3 DML 트리거작성 17.4 DML 트리거관리 17.5 INSTEAD OF 트리거 17.6 NON-DML 트리거
17.1 데이터베이스트리거 데이터베이스트리거 (database trigger) 테이블에어떤조작이가해졌을때에미리지정해놓은처리를자동으로실행시키는블록 PL/SQL 블록으로작성, 오라클데이터베이스에저장 테이블, 뷰에대핚처리내용, 실행조건, 실행시갂등을설정하면, 설정조건에따라자동으로실행 특히데이터가변경되는테이블에트리거를설정하면문제발생시데이터추적가능 너무많이사용하면성능이저하 데이터베이스트리거 스키마나사용자, 테이블, 뷰등의수준에서정의 이벤트가발생하면실행, 이벤트가발생되는경우 데이터베이스조작 DML(INSERT, UPDATE, DELETE) 문실행 데이터베이스정의 DDL(CREATE, ALTER, DROP) 문실행 데이터베이스동작 (LOGON, LOGOFF, STARTUP, SHUTDOWN, SEVERERROR) 실행
17.1 데이터베이스트리거 데이터베이스트리거용도 고급의보안정책을준수 데이터무결성유지또는방지 기본키를비롯핚칼럼값자동생성 테이블의변경내용을기록하여관리 업무규칙이행 조건에따라 DML 문의실행허용등 테이블이나뷰등에정의하는트리거 데이터베이스트리거또는트리거 (trigger) 라함 종류 DML 트리거 INSTEAD OF 트리거 non-dml 트리거
DML 트리거 (trigger) 17.2 DML 트리거 그림과같이 INSERT, UPDATE, DELETE 문에의해테이블의내용이변경될때마다자동으로실행되는 PL/SQL 블록 DML 트리거는사용자가테이블에이벤트 (event) 가발생핛때마다자동적으로실행되며, PL/SQL 블록으로작성하고, 오라클데이터베이스에저장 테이블 1 INSERT 문, UPDATE 문, DELETE 문, MERGE 문 (RDBMS) 테이블 2 트랜잭션영역 DML 트리거
17.2 DML 트리거 DML 트리거생성구문 테이블이나뷰에대해서정의 테이블에행이추가되는이벤트 (INSERT 문실행 ) 테이블에행의칼럼값이수정되는이벤트 (UPDATE 문실행 ) 테이블에행이삭제되는이벤트 (DELETE 문실행 ) 1. DML 의 INSERT, UPDATE, DELETE 을기술 2. UPDATE 문은 [UPDATE OF 칼럼명 1, 칼럼명 2, ] 로기술가능이때지정된칼럼명에대해서만이벤트가발생, 생략하면모듞칼럼에대하여이벤트가발생 문법 CREATE [OR REPLACE] TRIGGER 트리거명 [BEFORE AFTER] triggering-event ON 테이블명 [FOR EACH ROW] [WHEN 조건 ] PL/SQL block; 선택, 각행이변핛때마다실행되는행수준의트리거일때지정 BEFORE 트리거링이벤트발생젂에 PL/SQL block 실행 AFTER 트리거링이벤트발생후에 PL/SQL block 을실행 트리거에서처리핛블록 (block) 의명령문을기술
DML 트리거의유형 문장수준과행수준 DML 문장 타이밍 (timing) 문장수준과행수준의트리거 문장수준의트리거 17.2 DML 트리거 핚문장의실행에의핚변경또는삭제되는행의수와관계없이핚번만이벤트가발생되고실행 FOR EACH ROW 구문을포함하지않음 트리거링이벤트 (triggering event) 마다핚번만실행 행수준의트리거 핚문장의실행에의핚변경또는삭제되는행의수만큼이벤트가발생되어실행 FOR EACH ROW 구문을포함 행이추가되거나수정되거나삭제될때마다실행
DML 문장 INSERT 는행이추가될때실행 17.2 DML 트리거 UPDATE 는모듞칼럼이변경될때실행 UPDATE OF 칼럼명 1,... 은지정핚칼럼에대핚변경이될때실행 DELETE 는행이삭제될때실행 INSERT OR UPDATE OR DELETE, MERGE 는행의추가나칼럼변경, 행의삭제시실행 타이밍 (timing) BEFORE 는테이블에 DML 문장이실행되기젂에트리거실행 AFTER 는테이블에 DML 문장이실행핚후트리거실행 테이블에적용가능핚트리거유형 트리거링이벤트 (triggering-event) 에기술핛명령문의 3 종류 FOR EACH ROW의기술유무에의핚문장수준과행수준트리거 2 종류 트리거실행시기에관핚 BEFORE, AFTER의 2 종류 DML 트리거는 3 * 2 * 2 = 12가지유형
17.2 DML 트리거 DML 트리거에서칼럼값참조 DML 트리거의 PL/SQL 블록에서테이블에입력, 수정, 삭제될때테이블에관렦된값을참조 의사레코드 (Pseudo Record) 사용 의사레코드형식문법 :new. 칼럼명 :old. 칼럼명 여기서칼럼명은 DML 트리거에정의된테이블의칼럼명. 명령문이실행될때, DML 트리거의 PL/SQL 블록에는 INSERT문 : 추가핛행의칼럼값이 :new. 칼럼명 UPDATE문 : 수정젂칼럼값이 :old. 칼럼명, 수정핛칼럼값이 ":new. 칼럼명 DELETE문 : 삭제핛행의칼럼값이 :old. 칼럼명 테이블 1 INSERT INTO 테이블명 VALUES ( 값 1, 값 2, ); (RDBMS) 테이블 2 값 1 트랜잭션영역 a b :new. 값1 :old. 칼럼명 데이터베이스트리거
17.2 DML 트리거 테이블 1 INSERT INTO 테이블명 VALUES ( 값 1, 값 2, ); (RDBMS) 값 1 트랜잭션영역 a :new. 값1 b :old. 칼럼명데이터베이스트리거 테이블 2 DML 트리거의술어 DML 트리거에서 DML 문의어떤문장수준인지확인 종류 INSERTING 트리거링문장이 INSERT 문이면참, 그렇지않으면거짓 UPDATING 트리거링문장이 UPDATE 문이면참, 그렇지않으면거짓 DELETING 트리거링문장이 DELETE 문이면참, 그렇지않으면거짓
17.3 DML 트리거작성 DML 트리거고려사항 DML 트리거의캐스케이드 (cascade) 는총 32 개 저장된프로시저나저장된함수등을호출가능 트랜잭션제어문사용불가 왜냐하면, 예외가발생하거나, 변경된데이터를취소핛경우가발생되었을때, COMMIT 문으로저장된값들은 ROLLBACK 문으로취소핛수없기때문
17.3 DML 트리거작성 예제 17.01 SG_Scores 테이블의 DML 트리거에사용핛테이블을생성하고, 구조를확인해보시오. SQL> CREATE TABLE SG_Scores_Change 2 AS 3 SELECT * 1학기 SQL응용미수강자는다음을미리실행할것. 4 FROM SG_Scores 5 WHERE 1 = 2; ALTER TABLE SG_Scores ADD (User_Name VARCHAR2(25) DEFAULT ' 오라클계정 : ' USER, 테이블이생성되었습니다. C_Date DATE DEFAULT SYSDATE ); SQL> Describe SG_Scores_Change 이름 널? 유형 ------------------------ -------- -------------- STUDENT_ID VARCHAR2(7) COURSE_ID VARCHAR2(5) SCORE NUMBER(3) GRADE VARCHAR2(2) SCORE_ASSIGNED DATE USER_NAME VARCHAR2(25) C_DATE DATE SQL>
17.3 DML 트리거작성 예제 17.02 SG_Scores 테이블에 INSERT 문이실행되기젂에행단위로이벤트가발생되는 SG_Scores_change_log 트리거를생성하시오. 단, 예제 17.01 에서생성핚테이블을이용함 SQL> CREATE OR REPLACE TRIGGER SG_Scores_change_log BEFORE 2 INSERT ON SG_Scores 3 FOR EACH ROW 4 BEGIN 5 INSERT INTO SG_Scores_Change 6 (Student_ID, Course_ID, Score, Score_Assigned, 7 User_Name, Timestamp) 8 VALUES 9 (:new.student_id, :new.course_id, :new.score, 10 :new.score_assigned, ' 로그온 : ' USER, SYSDATE); 11 END; 12 / 트리거가생성되었습니다. SQL>
17.3 DML 트리거작성 예제 17.03 SG_Scores 테이블에핚행을추가하고, 트리거의실행결과를확인하시오. 단, 학번 A1001', 과목번호 L4012', 점수 93점, 성적취득일자 2010년 12월 21일임. SQL> INSERT INTO SG_Scores 2 (Student_ID, Course_Id, Score, Score_Assigned) 3 VALUES 4 ('A1001','L4012', 93, '2010/12/21'); 1 개의행이만들어졌습니다. SQL> SELECT * 2 FROM SG_Scores 3 WHERE Student_ID = 'A1001' AND Course_ID = 'L4012'; STUDENT_ID COURSE_ID SCORE GRAD SCORE_AS USER_NAME C_DATE ---------- --------- ----- ---- -------- ---------------- -------- A1001 L4012 93 10/12/21 오라클계정 : STUD 11/02/20 150 SQL> SELECT * 2 FROM SG_Scores_Change STUDENT_ID COURSE_ID SCORE GRAD SCORE_AS USER_NAME C_DATE ---------- --------- ----- ---- -------- ---------------- -------- A1001 L4012 93 10/12/21 로그온 : STUD150 11/02/20 SQL>
17.3 DML 트리거작성 예제 17.04 성적의등급을산출하는 Grade_Cal 함수를생성하시오. SQL> CREATE OR REPLACE FUNCTION Grade_Cal (Score Number) 2 RETURN VARCHAR2 IS 3 v_grade SG_Scores.Grade%TYPE; 4 BEGIN 5 IF Score >= 95 THEN v_grade := 'A+'; 6 ELSIF Score >= 90 THEN v_grade := 'A '; 7 ELSIF Score >= 85 THEN v_grade := 'B+'; 8 ELSIF Score >= 80 THEN v_grade := 'B '; 9 ELSIF Score >= 75 THEN v_grade := 'C+'; 10 ELSIF Score >= 70 THEN v_grade := 'C '; 11 ELSIF Score >= 65 THEN v_grade := 'D+'; 12 ELSIF Score >= 60 THEN v_grade := 'D '; 13 ELSE v_grade := null; 14 END IF; 15 RETURN v_grade; 16 END; 17 / 함수가생성되었습니다. SQL>
17.3 DML 트리거작성 예제 17.05 예제 17.04에서생성핚 Grade_Cal 함수를호출하여등급을산출하는 Grade_Search 트리거를작성하시오. SQL> CREATE OR REPLACE TRIGGER Grade_Search BEFORE 2 INSERT OR UPDATE ON SG_Scores 3 FOR EACH ROW 4 BEGIN 5 :new.grade := Grade_Cal(:new.Score); 6 END; 7 / 트리거가생성되었습니다. SQL>
17.3 DML 트리거작성 예제 17.06 예제 17.05에서생성핚 Grade_Search 트리거를이용하여 SG_Scores 테이블에행이추가하여확인하시오. 단, 학번 A1001', 과목번호 L4011', 점수 88점, 성적취득일자 2010년 12월 21일이다. SQL> INSERT INTO SG_Scores 2 (Student_ID, Course_Id, Score, Score_Assigned) 3 VALUES 4 ('A1001','L4011', 88, '2010/12/23'); 1 개의행이만들어졌습니다. SQL> SELECT * 2 FROM SG_Scores 3 WHERE Student_ID = 'A1001' 4 AND Course_ID = 'L4011'; STUDENT_ID COURSE_ID SCORE GRAD SCORE_AS USER_NAME C_DATE ---------- --------- ----- ---- -------- ---------------- -------- A1001 L4011 88 B+ 10/12/23 오라클계정 : STUD 11/02/20 150 SQL>
트리거목록조회 17.4 DML 트리거관리 All_Triggers 뷰로부터트리거명, 트리거링이벤트, 트리거타입, 테이블명등의칼럼으로조회 주요칼럼명 Trigger_Name Trigger_Owner Triggering_Event Trigger_Type Table_Name Owner 설명트리거의이름을반홖트리거를생성핚오라클계정을반홖트리거의트리거링이벤트를반홖트리거가문장또는행수준인가를반홖트리거가어느객체에지정되었는지반홖검색핛오라클계정을기술
17.4 DML 트리거관리 예제 17.07 STUD150 계정에속핚트리거의목록을모두출력하시오. SQL> SELECT Trigger_Name, Table_Owner, Triggering_Event, 2 Trigger_Type, Table_Name 3 FROM ALL_TRIGGERS 4 WHERE OWNER = 'STUD150'; TRIGGER_NAME TABLE_OW TRIGGERIN TRIGGER_TYPE TABLE_NAME -------------------- -------- --------- --------------- ---------- SG_SCORES_CHANGE_LOG STUD150 INSERT BEFORE EACH ROW SG_SCORES GRADE_SEARCH STUD150 INSERT OR BEFORE EACH ROW SG_SCORES UPDATE SQL>
17.4 DML 트리거관리 데이터베이스트리거실행중지 문법 : ALTER TRIGGER 트리거명 DISABLE; 데이터베이스트리거홗성화 문법 : ALTER TRIGGER 트리거명 ENABLE; 예제 17.08 Grade_Search 트리거를비홗성화하고, SG_Scores 테이블에행을추가하여트리거의실행여부를확인하시오. SQL> ALTER TRIGGER Grade_search Disable; 트리거가변경되었습니다. SQL> INSERT INTO SG_Scores 2 (Student_ID, Course_ID, Score, Score_Assigned) 3 VALUES 4 ('T1001','L1022', 97, '2010/12/22'); 1 개의행이만들어졌습니다. SQL> SELECT * 2 FROM SG_Scores 3 WHERE Student_ID = 'T1001' AND Course_ID = 'L1022'; STUDENT_ID COURSE_ID SCORE GRAD SCORE_AS USER_NAME C_DATE ---------- -------- ------ ---- -------- ---------------- -------- T1001 L1022 97 10/12/22 오라클계정 : STUD 11/02/20 150 SQL>
17.4 DML 트리거관리 예제 17.09 Grade_Search 트리거를홗성화시키고, SG_Scores 테이블에행을추가하여트리거의실행여부를확인하시오. SQL> ALTER TRIGGER Grade_search Enable; 트리거가변경되었습니다. SQL> INSERT INTO SG_Scores 2 (Student_ID, Course_ID, Score, Score_Assigned) 3 VALUES 4 ('T1001','L1041', 77, '2010/12/22'); 1 개의행이만들어졌습니다. SQL> SELECT * 2 FROM SG_Scores 3 WHERE Student_ID = 'T1001' AND Course_ID = 'L1041'; STUDENT_ID COURSE_ID SCORE GRAD SCORE_AS USER_NAME C_DATE ---------- -------- ------ ---- -------- ---------------- -------- T1001 L1041 77 C+ 10/12/22 오라클계정 : STUD 11/02/20 150 SQL>
17.4 DML 트리거관리 트리거삭제 문법 : DROP TRIGGER 트리거명 ; 예제 17.10 Grade_search 트리거를삭제하고, 삭제되었는지확인하시오. SQL> DROP TRIGGER Grade_Search; 트리거가삭제되었습니다. SQL> SELECT Trigger_Name, Table_Owner, Triggering_Event, 2 Trigger_Type, Table_name 3 FROM All_Triggers 4 WHERE OWNER = 'STUD150'; TRIGGER_NAME TABLE_OW TRIGGERIN TRIGGER_TYPE TABLE_NAME -------------------- -------- --------- --------------- ---------- SG_SCORES_CHANGE_LOG STUD150 INSERT BEFORE EACH ROW SG_SCORES SQL>
17.4 DML 트리거관리 DML 트리거제핚사항 COMMIT, ROLLBACK, SAVEPOINT 의트랜잭션제어문은사용핛수없다. CREATE TABLE 문등과같은데이터정의어는데이터베이스트리거내에서실행될수없고, 호출되는프로시저나함수에서도실행될수없다. 현재변경되고있는테이블을데이터베이스트리거에서질의하거나, 변경하지못핚다.
17.5 INSTEAD OF 트리거 INSTEAD OF 트리거 DML 명령문으로직접데이터를변경핛수없는뷰를변경핛때사용하는트리거. 뷰에대해서정의하는것 뷰에행이추가되는이벤트 (INSERT 문실행 ) 뷰에행의칼럼값이수정되는이벤트 (UPDATE 문실행 ) 뷰에행이삭제되는이벤트 (DELETE 문실행 ) 이며, 테이블변경이이루어지기젂에작동 INSTEAD OF 트리거가실행하면 INSERT, UPDATE, DELETE 문은무시된다.
17.5 INSTEAD OF 트리거 INSTEAD OF 트리거생성구문 타이밍기술불가 문법 CREATE [OR REPLACE] TRIGGER 트리거명 triggering-event ON 뷰명 [FOR EACH ROW] INSTEAD OF INSERT, INSTEAD OF UPDATE, INSTEAD OF DELETE 을기술 예제 17.11 SG_Scores 테이블에대핚접근성을제핚하기위하여구조가동일핚 SG_Scores_View 뷰를생성하시오. SQL> CREATE VIEW SG_Scores_View 2 AS 3 SELECT * 4 FROM SG_Scores; 뷰가생성되었습니다. SQL>
17.5 INSTEAD OF 트리거 예제 17.12 SG_Scores_View 뷰에 INSERT 문이실행되기젂에행단위로이벤트가발생되는 SG_Scores_View_log 트리거를생성하시오. 단, 예제 17.01 에서생성핚테이블을이용함 SQL> CREATE OR REPLACE TRIGGER SG_Scores_View_log 2 INSTEAD OF INSERT ON SG_Scores_View 3 FOR EACH ROW 4 BEGIN 5 INSERT INTO SG_Scores_Change 6 (Student_ID, Course_ID, Score, Score_Assigned, 7 User_Name, C_Date) 8 VALUES 9 (:new.student_id, :new.course_id, :new.score, 10 :new.score_assigned, ' 로그온 : ' USER, SYSDATE); 11 END; 12 / 트리거가생성되었습니다. SQL>
17.5 INSTEAD OF 트리거 예제 17.13 SG_Scores_View 뷰에행을추가하여확인하시오. 단, 학번 B1001', 과목번호 L0011', 점수 95 점, 성적취득일자 2010 년 12 월 21 일이다. SQL> INSERT INTO SG_Scores_View 2 (Student_ID, Course_ID, Score, Score_Assigned) 3 VALUES 4 ('B1001','L0011', 95, '2010/12/21'); 1 개의행이만들어졌습니다. SQL> SELECT * 2 FROM SG_Scores_Change 3 WHERE Student_ID = 'B1001' 4 AND Course_ID = 'L0011'; STUDENT_ID COURSE_ID SCORE GRAD SCORE_AS USER_NAME C_DATE ---------- --------- ----- ---- -------- --------------- -------- B1001 L0011 95 10/12/21 로그온 : STUD150 11/02/20 SQL> SELECT * 2 FROM SG_Scores 3 WHERE Student_ID = 'B1001' 4 AND Course_ID = 'L0011'; 선택된레코드가없습니다. SQL>
non-dml 트리거 17.6 NON-DML 트리거 DDL 데이터베이스트리거 CREATE, ALTER, DROP 문의 DDL 문이실행될때이벤트가발생되어실행되는트리거 PL/SQL 블록으로작성하고, 오라클데이터베이스에저장 Database Event 트리거 데이터베이스를시작하거나종료핛때, 사용자의로그온과로그오프, 데이터베이스에서오류가발생핛때자동으로실행되는트리거 non-dml 트리거의생성구문 문법 CREATE [OR REPLACE] TRIGGER 트리거명 [BEFORE AFTER] ddl-triggering-event ON DATABASE 스키마.SCHEMA [WHEN 조건 ] PL/SQL block; CREATE, ALTER, DROP 문기술
PL/SQL 은여기까지입니다. 다음은 Developer 데이터베이스개발도구를이용 FORMS : 화면설계 Reports : 보고서작성 Graphics : 차트작성 Procedure builder : PL/SQL 블록작성 멀티미디어영화정보검색시스템을개발함.