ORACLE TRIGGER 사용 예

Trigger

!!! Insert, Update, Delete이 실행되는 시점 전에 또는 후에 동반되어야 하는 작업을 정의한다.

사용법1) – row 단위의 변화

CREATE [OR REPLACE] TRIGGER trigger_name

             BEFORE (OR AFTER)

             UPDATE (OR DELETE OR INSERT) ON table_name

             [FOR EACH ROW]

DECLARE

             변수 선언부;

BEGIN

             프로그램 코딩부;

END;

/

사용법2) – column 단위의 변화

CREATE [OR REPLACE] TRIGGER trigger_name

             BEFORE (OR AFTER)

             UPDATE [ OF column1, column2 …] ON table_name

             [FOR EACH ROW]

DECLARE

             변수 선언부;

BEGIN

             프로그램 코딩부;

END;

/

BEFORE : 데이터 처리가 실행되기 전

AFTER : 데이터 처리가 실행 된 후

FOR EACH ROW : 데이터 처리시 건건이 모두 트리거 실행

:OLD.컬럼명 : SQL 반영전의 컬럼 데이터

:NEW.컬럼명: SQL 반영 후의 컬럼 데이터

RAISE_APPLICATION_ERROR( 에러번호, 에러내용 ) : 강제 에러 처리 (에러번호는 -20000 ~ -20999까지 임의로 사용할 수 있다.)

실례1)

CREATE OR REPLACE TRIGGER REFUSE_98001

             BEFORE

             UPDATE OR DELETE OR INSERT ON PERSONNEL

             FOR EACH ROW

DECLARE

             s_msg   VARCHAR2(100) := '';

BEGIN

             IF UPDATING THEN

                           IF :OLD.EMPNO = '98001' THEN

                                        s_msg := '98001 사번은 수정 할 수 없습니다.!';

                                        raise_application_error( -20001, s_msg);

                           END IF;

             END IF;

             IF DELETING THEN

                           IF :OLD.EMPNO = '98001' THEN

                                        s_msg := '98001 사번은 삭제 할 수 없습니다.!';

                                        RAISE_APPLICATION_ERROR( -20002, s_msg);

                           END IF;

             END IF;

             IF INSERTING THEN

                           IF :NEW.EMPNO = '98001' THEN

                                        s_msg := '98001 사번은 입력 할 수 없습니다.!';

                                        raise_application_error( -20003, s_msg);

                           END IF;

             END IF;

END;

/

SQL> INSERT INTO PERSONNEL(EMPNO, EMP_NAME) VALUES('98001', 'TRIGGER');

결과)

==> Insert

INSERT INTO PERSONNEL(EMPNO, EMP_NAME) VALUES('98001', 'TRIGGER')

            *

1행에 오류:

ORA-20003: 98001 사번은 입력 할 수 없습니다.!

ORA-06512: "DALMA.REFUSE_98001", 줄 19에서

ORA-04088: 트리거 'DALMA.REFUSE_98001'의 수행시 오류

--**Trigger**--
--자료의 무결성을 유지
--변경된 자료 및 변경한 유저를 기록해서 테이블의 변경정보 감시
--지정한 이벤트가(DML)가 발생할때마다 자동으로 실행되는 PL/PQL 블럭
-->자동 호출(호출문 없음)
--데이터 흐름 제어문(TCL)은 사용할수 없다!(MS-SQL에서는 가능)

-*형식*-
create or replace trigger trigger_name
after insert [or delete] [or update] on table_name
begin
명령;
end;
/

ex)※명령하나에 트리거한번실행.....
create table ex(memo varchar2(30));

create or replace trigger t1
  after insert on dept_ex
begin
  insert into ex values('부서테이블에 행입력');
end;
/

SQL>insert into dept_ex values(60,'난잡부','감사시');

SQL> select * from ex;

MEMO
------------------------------
부서테이블에 행입력

 

ex)※이벤트가 발생한 행수많큼 트리거실행은

create or replace trigger t1
   after insert on dept_ex
   for each row ---*********붙어주면 된다...
begin
   insert into ex values('부서테이블에 행입력');
end;

---------------------------------------------------------------------------------

--부서테이블에 데이터가입렵되면 dept_bk테이블에 같은 데이터 입력

create table dept_bk
as
select deptno,dname from dept

create or replace trigger bk_dept
  after insert on dept
  for each row
begin
insert into dept_bk values(:new.deptno, :new.dname);
end;
/

--------------------------------------------------------------------------------

 ※ :new.컬럼명/ :old.컬럼명
       insert              delete     for each row 없인 못쓴다...*******

---------------------------------------------------------------------------------

--부서 테이블에 데이터 삭제되면 삭제된 데이터가 dept_bk 테이블에서도 삭제되는 트리거

create or replace trigger t2
after delete on dept
for each row
begin
 delete dept_bk where deptno= :old.deptno;
end;
/

------------------------------------------------------------------------------------

사용자 정의 에러
--raise_application_error(-20001,'삭제될수없다'); -->rollback 작업을 포함한다.
                                 error번호  message

------------------------------------------------------------------------------------

1사원 테이블의 [급여가] 변경되면 변경을 시도한 사용자와 날짜(시간 포함),변경된 사원명,급여를 blick_list 테이블에 기록
2.변경할수 없도록 작업을 취소하고 메세지를 출력한다.

create table black_list(
사용자 varchar2(20), 시간 varchar2(50),
사원명 varchar2(20),급여 number(10));

1)
create or replace trigger t3
after update of sapay on sawon
for each row
begin
insert into black_list values(user,to_char(sysdate,'yyyy/mm/dd hh:mi:ss am'),:new.saname,:new.sapay);
end;
/

2)
create or replace trigger t3
after update of sapay on sawon
for each row
begin
raise_application_error(-2001,'삭제안된다');
end;
/

------------------------------------------------------------------------------------------------------

※컬럼단위의 이벤트.....after update of column_name on table_name;
※트리거 정보확인뷰....user_triggers
※user_objects 뷰를 확인하면 object 에 관한 정보를 볼수있다.

 

-----------------------------------------------------------------------------------------
--부서 테이블의 부서가 업데이트되면 해당부서번호를 참조하는 사원들도 함께변경되는 트리거

create or replace trigger t4
after update of deptno on dept
for each row
begin
update sawon set deptno=:new.deptno where deptno=:old.deptno;
end;
/

--------------------------------------------------------------------------------------------------
※제약조건으로 할수없는것은 트리거로 구현한다....
※제약조건과 트리거가 겹치면 제약조건 우선순위실행이다.

---------------------------------------------------------------------------------------------------
--사원 테이블에서 급여가 변경되는 경우, 기존 급여보다 적게 변경되거나,
   20%초과하여 변경되는경우 에러를 발생시켜라.

create or replace trigger t5
after update of sapay on sawon
for each row
begin
if (:old.sapay>:new.sapay) or (:old.sapay*1.2<:new.sapay)  then
raise_application_error(-20001,'변경범위가맞지않습니다');
end if;
end;
/

-----------------------------------------------------------------------------------------------------

--사원 테이블에서 급여가 변경되는 경우, 기존 급여보다 적게 변경되거나,
   20%초과하여 변경되는경우 에러를 발생시켜라.
   단 사원이나 대리는 적용되게하라.....(when 절)

create or replace trigger t5
after update of sapay on sawon
for each row
when (old.sajob<>'사원' or old.sajob<>'대리')
begin
if (:old.sapay>:new.sapay) or (:old.sapay*1.2<:new.sapay)  then
raise_application_error(-20001,'변경범위가맞지않습니다');
end if;
end;
/

----------------------------------------------------------------------------------
--loan  테이블에 insert 가되면 emp 테이블의 d_ck가 Y로
   loan  테이블에 delete 가되면 emp 테이블의 d_ck가 N로
   loan  테이블에 update 가되면 emp 테이블의 d_ck가 id번호에맞게변경되도록해라..

 --테이블생성
create table emp(
id number(3), name varchar2(10), d_ck varchar2(5) default 'N',
constraint emp_id_pk primary key(id));

create table loan(
emp_id number(3), amount number(10), date_lo date,
constraint loan_id_fk foreign key(emp_id) references emp(id));

--트리거 생성

create or replace trigger t8
after insert or delete or update on loan   --*******
for each row
begin
if inserting then
    update emp set d_ck='Y' where id=:new.emp_id;
elsif deleting then
    update emp set d_ck=default where id=:old.emp_id;
elsif updating then
   update emp set d_ck='Y' where id=:new.emp_id;
   update emp set d_ck=default where id=:old.emp_id;
end if;
end;
/

--------------------------------------------------------------------------------------
※ 한테이블 에 모든 트리거를 중지시키는거....
SQL> alter table table_name DISABLED all triggers;

한개만 변경..

SQL> select trigger_name, table_name, status from user_triggers; ---정보확인..

TRIGGER_NAME                   TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
T4                             DEPT                           ENABLED
T5                             SAWON                          ENABLED
T8                             LOAN                           ENABLED

 

SQL> alter trigger t4 disable;

트리거가 변경되었습니다.

[출처] ORACLE Trigger|작성자 포비코난