Trigger

***************************************************************************
**** 트리거
***************************************************************************

- 트리거는 DB에 저장되고 지정된 이벤트에 대한 응답으로 실행되는 PL/SQL 블록
- Oracle DB는 지정된 조건이 발생할 떄 트리거를 자동으로 실행함

EXPERT @ prod > create table t_order
  2  (no number, ord_code varchar2(10), ord_date date);

Table created.

EXPERT @ prod > create or replace trigger t_order before insert on t_order begin if
  2  (
  3  to_char(sysdate, 'HH24:MI') not between '07:40' and '08:00'
  4  )
  5  then raise_application_error
  6  (
  7  -20100, '허용시간 아닙니다.'
  8  );
  9  end if;
 10  end;
 11  /

Trigger created.

EXPERT @ prod > insert into t_order values(2,'c200',sysdate);
insert into t_order values(2,'c200',sysdate)
            *
ERROR at line 1:
ORA-20100: 허용시간 아닙니다.
ORA-06512: at "EXPERT.T_ORDER", line 5
ORA-04088: error during execution of trigger 'EXPERT.T_ORDER'

EXPERT @ prod > drop trigger t_order;

Trigger dropped.

***************************************************************************
**** 트리거 예제2
***************************************************************************

EXPERT @ prod > create or replace trigger t_order2 before
  2  insert on t_order for each row
  3  -- 행레벨 트리거
  4  begin if
  5  (
  6  :NEW.ord_code
  7  )
  8  not in
  9  (
 10  'c100'
 11  )
 12  then raise_application_error
 13  (
 14  -20200, '제품코드가 틀리다.'
 15  )
 16  end if;
 17  end;
 18  /

Warning: Trigger created with compilation errors.

EXPERT @ prod > show errors;
Errors for TRIGGER T_ORDER2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
13/1     PLS-00103: Encountered the symbol "END" when expecting one of
         the following:
         := . ( % ;
         The symbol ";" was substituted for "END" to continue.

EXPERT @ prod > create or replace trigger t_order2 before
  2  insert on t_order for each row
  3  begin if(:NEW.ord_code)
  4  not in('c100')
  5  then raise_application_error(-20200, '제품코드가 틀리다.');
  6  end if;
  7  end;
  8  /

Trigger created.

 

***************************************************************************
**** 트리거 예제3
***************************************************************************

EXPERT @ prod > -- 'c500' 제품에 대해서만 07:40분 부터 08:00분까지만
EXPERT @ prod > -- 입력을 허용하는 트리거 생성
EXPERT @ prod >
EXPERT @ prod > drop trigger t_order2;

Trigger dropped.

EXPERT @ prod > create or replace trigger t_order3 before
  2  insert on t_order for each row when(NEW.ord_code='c500') begin if
  3  (
  4  to_char(sysdate,'HH24:MI') not between '17:40' and '08:00'
  5  )
  6  then raise_application_error
  7  (
  8  -20300, 'c500 제품의 입력허용시간이 아니다.'
  9  );
 10  end if;
 11  end;
 12  /

Trigger created.

EXPERT @ prod > insert into t_order values(1,'c100',sysdate);

1 row created.

EXPERT @ prod > insert into t_order values(5,'c500',sysdate);
insert into t_order values(5,'c500',sysdate)
            *
ERROR at line 1:
ORA-20300: c500 제품의 입력허용시간이 아니다.
ORA-06512: at "EXPERT.T_ORDER3", line 5
ORA-04088: error during execution of trigger 'EXPERT.T_ORDER3'

 

***************************************************************************
**** 트리거 예제4
**** employees 테이블에서 salary값을 변경하면 audit_employees에 내용을 남기는
**** 트리거를 만든다.
***************************************************************************

EXPERT @ prod > conn system/Manager123
Connected.
SYSTEM @ prod > create table audit_employees
  2  (username varchar2(150),
  3  mod_date date,
  4  ipaddr varchar2(30),
  5  mod_conts varchar2(2000))
  6  tablespace example;

Table created.

SYSTEM @ prod > desc audit_employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                           VARCHAR2(150)
 MOD_DATE                                           DATE
 IPADDR                                             VARCHAR2(30)
 MOD_CONTS                                          VARCHAR2(2000)

SYSTEM @ prod > create or replace trigger system.hrsalary_audit
  2  AFTER UPDATE OF salary
  3  ON hr.employees
  4  REFERENCING NEW AS NEW_VAL OLD AS OLD_VAL
  5  FOR EACH ROW
  6  BEGIN
  7  IF :OLD_VAL.salary != :NEW_VAL.salary THEN
  8  INSERT INTO system.audit_employees
  9  VALUES(sys_context('userenv','os_user'), sysdate,
 10  sys_context('userenv','ip_address'),
 11  :NEW_VAL.employee_id ||' salary changed from '||:OLD_VAL.salary
 12  ||' to '||:NEW_VAL.salary);
 13  END IF;
 14  END;
 15  /

Trigger created.

-- HR 원격으로 접속한다

SYS @ PROD > conn hr/hr@prod

HR @ prod > update employees set salary=5300 where employee_id=198;

1 row updated.

HR @ prod > commit;

Commit complete.

HR @ prod > select salary from employees where employee_id=198;

    SALARY
----------
      5300

SYSTEM @ PROD> select * from audit_employees;

USERNAME                       MOD_DATE     IPADDR     MOD_CONTS
------------------------------ ------------ ---------- --------------------
NS25-PC\Administrator          08-JUN-16    127.0.0.1  198 salary changed f
                                                       rom 2600 to 5300