- 이벤트가 발생하면 DB에 의해 자동으로 수행되는 명명블록 - COMMIT, ROLLBACK, SAVEPOINT문 사용불가 ( SQL 오류: ORA-04092: cannot COMMIT in a trigger) - 주요 사용 목적은 테이블 데이터의 무결성 보장, DB관리의 자동화 등 - 딕셔너리 유
단순 DML트리거 - DML이벤트에 의해 수행되는 트리거 - 테이블과 뷰에 종속되어 있으며, 대상 객체를 삭제하면 함께 삭제된다. --단순뷰 - 단일 타이밍 포인트(TIMING POINT)를 지정한다. - DML이벤트 : INSERT, UPDATE, DELETE
SYNTAX CREATE [OR REPLACE] TRIGGER [스키마.]트리거명 {BEFORE 또는 AFTER} DML이벤트 [ OR DML이벤트 OR DML이벤트 ] ON [스키마명.]{테이블명 또는 뷰명} [REFERNCING_CLAUSE] -- 상관명 별칭 사용 [FOR EACH ROW] -- 행 단위로 수행하는 경우 기술 [FOLLOWS] --트리거 순서 정하기 [WHEN 조건] --ROW에 조건을 걺 [DECLARE 선언부 ] --IS/AS TRIGGER에서는 생략가능 BEGIN 실행문, 출력문, 조건.제어문 등등; [EXCEPTION 예외처리부] END [트리거명] ; /
--트리거 생성시 주의할 점 - 최대 코드 용량은 32KB를 넘을 수 없다. - 순환 트리거링, 재귀 트리거링이 되지 않도록 주의해야함
CREATE OR REPLACE TRIGGER TRG_T1_C2 BEFORE UPDATE OF C2 ON T1 FOLLOWS TRG_T1_C1 --TRG_T1_C1 다음에 실행 BEGIN DBMS_OUTPUT.PUT_LINE('C2');
END TRG_T1_C2; /
- 테이블에 종속된 전체 트리거 활성화/비활성화 하기 ALTER TABLE [스키마명.] 테이블명 {ENABLE 또는 DISABLE} ALL TRIGGER ;
- 해당 트리거 하나만 비활성화/활성화 하기 ALTER TRIGGER 트리거명 {ENABLE 또는 DISABLE} ;
ALTER TRIGGER TRG_T1_C1 DISABLE;
행단위 DML트리거 - OLD, NEW상관명을 이용하여 변경 전, 후 값을 참조 가능 - 단, INSERT문의 OLD값과 DELETE문의 NEW값은 무조건 NULL을 반환한다.
--FOR EACH ROW 트리거 CREATE OR REPLACE TRIGGER TRI_T1 BEFORE INSERT OR UPDATE OR DELETE ON T1 FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(:OLD.C1 ||','||:NEW.C1); END ; /
PRAGMA AUTONOMOUS_TRANSACTION; - TRIGGER에 연결된 프로시저나 함수에 트랜잭션 작업이 있어 영향을 받게 되는 경우, 해당 트랜잭션 작업에 영향을 받지 않도록 독립적으로 실행을 해주라는 명령임
ㄹㄹ
--테이블 생성
CREATE TABLE T1(C1 NUMBER);
--BEFORE 트리거
CREATE OR REPLACE TRIGGER TRI_T1
BEFORE INSERT ON T1
BEGIN
DBMS_OUTPUT.PUT_LINE('B');
END;
/
--AFTER 트리거
CREATE OR REPLACE TRIGGER TRI_T2
AFTER INSERT ON T1
BEGIN
DBMS_OUTPUT.PUT_LINE('A');
END;
/
INSERT INTO T1 VALUES (1);
[TRIGGER]
CREATE TABLE T1 AS SELECT 1 AS C1, 1 AS C2 FROM DUAL;
--BEFORE 트리거
CREATE OR REPLACE TRIGGER TRI_T1_C1
BEFORE UPDATE OF C1 ON T1 --T1 테이블 중 C1컬럼에 UPDATE이벤트 발생시
BEGIN
DBMS_OUTPUT.PUT_LINE('C1');
--COMMIT; 사용시 에러남
END TRI_T1_C1;
/
--BEFORE 트리거
CREATE OR REPLACE TRIGGER TRI_T1_C2
BEFORE UPDATE OF C2 ON T1
BEGIN
DBMS_OUTPUT.PUT_LINE('C2');
END TRI_T1_C2;
/
UPDATE T1 SET C2 = 2 WHERE C1=1;
[TRIGGER] [FOLLOWS]
CREATE TABLE T1 AS SELECT 1 AS C1, 1 AS C2 FROM DUAL;
--BEFORE 트리거
CREATE OR REPLACE TRIGGER TRG_T1_C1
BEFORE UPDATE OF C2 ON T1
BEGIN
DBMS_OUTPUT.PUT_LINE('C1');
END TRG_T1_C1;
/
--BEFORE 트리거
CREATE OR REPLACE TRIGGER TRG_T1_C2
BEFORE UPDATE OF C2 ON T1
FOLLOWS TRG_T1_C1
--TRG_T1_C1 다음에 실행
BEGIN
DBMS_OUTPUT.PUT_LINE('C2');
END TRG_T1_C2;
/
UPDATE T1 SET C2 = 2 WHERE C1=1;
[TRIGGER] [FOR EACH ROW]
--FOR EACH ROW 트리거
CREATE OR REPLACE TRIGGER TRI_T1
BEFORE INSERT OR UPDATE OR DELETE ON T1
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(:OLD.C1 ||','||:NEW.C1);
END ;
/
[TRIGGER] [REFERENCING] 상관명 별칭 사용
--상관명 별칭 사용
CREATE OR REPLACE TRIGGER TRG_DEPT
BEFORE INSERT OR UPDATE OR DELETE ON TEST.DEPT
REFERENCING OLD AS BF NEW AS AF
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('OLD DEPT :'||BF.DEPTNO||','||:BF.DNAME||','||:BF.LOC);
DBMS_OUTPUT.PUT_LINE('NEW DEPT :'||AF.DEPTNO||','||:AF.DNAME||','||:AF.LOC);
END ;
/
INSERT INTO DEPT VALUES(50, 'ABC', 'ABC');
UPDATE DEPT SET DNAME = 'HAHAHA' WHERE DEPTNO =50;
DELETE FROM DEPT WHERE DEPTNO=50;
CREATE OR REPLACE PROCEDURE PRO_COMMIT
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('프로시저 실행');
COMMIT;
END;
/
CREATE TABLE T1 (C1 NUMBER, C2 NUMBER);
CREATE OR REPLACE PROCEDURE PRO_COMMIT
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('프로시저 실행');
COMMIT;
END;
/
CREATE TABLE T1 (C1 NUMBER, C2 NUMBER);
CREATE OR REPLACE TRIGGER TRG_T1
BEFORE INSERT ON T1
FOR EACH ROW
WHEN (NEW.C1>=3) --NEW - BEFORE만 사용가능. AFTER 사용불가
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
--TRIGGER- COMMIT 진행 가능하게끔
BEGIN
:NEW.C2 :=0;
DBMS_OUTPUT.PUT_LINE(':NEW.C2의 값: ' ||:NEW.C2);
PRO_COMMIT();
END;
/
INSERT INTO T1 VALUES(5, 2);
ㅁㅁ
CREATE OR REPLACE PROCEDURE EMP_UD_PROC
( P_DEPTNO DEPT.DEPTNO%TYPE,
P_DNAME DEPT.DNAME%TYPE) --IN키워드 DEFAULT 생략되어 있음. 값 바꿀 수 없음.
IS
BEGIN
UPDATE EMP SET DNAME = P_DNAME WHERE DEPTNO = P_DEPTNO;
DBMS_OUTPUT.PUT_LINE('EMP테이블의 부서명 변경완료');
END;
/
CREATE OR REPLACE TRIGGER TRG_DEPT
BEFORE INSERT OR UPDATE OR DELETE ON DEPT
REFERENCING OLD AS BF NEW AS AF
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
--TRIGGER에 연결된 프로시저나 함수에 트랜잭션 작업이 있어 영향을 받게 되는 경우
--해당 트랜잭션 작업에 영향을 받지 않도록 독립적으로 실행을 해주라는 명령임
--V_DEPTNO DEPT.DEPTNO%TYPE;
--V_DNAME DEPT.DNAME%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('OLD DEPT : ' || :BF.DEPTNO||','||:BF.DNAME||','||:BF.LOC);
DBMS_OUTPUT.PUT_LINE('NEW DEPT : ' || :AF.DEPTNO||','||:AF.DNAME||','||:AF.LOC);
--DBMS_OUTPUT.PUT_LINE(V_DEPTNO||'/'||V_DNAME);
EMP_UD_PROC(:BF.DEPTNO, :AF.DNAME);
END;
/
BEGIN
UPDATE DEPT SET DNAME = 'SALESMAN' WHERE DEPTNO=30;
COMMIT; --TRIGGER에서 불가한 것을 한꺼번에 처리
END;
/
