082 - [Oracle PL/SQL] DML Triggers - stament-level sample

 

 

 

 

• 간단한 샘플 코드

delete from departments;
-----------------------------
Error report -
ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found


-- let us do statment level trigger
create or replace trigger dept_check_time
before
insert or update or delete -- time, event
on DEPARTMENTS             -- table
begin

    if  to_number (to_char(sysdate,'hh24') ) not between 8 and 16 then
    raise_application_error(-20010, 'DML operations not allowed now ');
    end if;

end;



-- try to test the trigger
-- 트리거 옵션이 before 이므로, constraint 오류 이전에 raise_application_error 발생.
delete from departments; 
-----------------------------
Error report -
SQL Error: ORA-20010: DML operations not allowed now
ORA-06512: at "HR.DEPT_CHECK_TIME", line 4
ORA-04088: error during execution of trigger 'HR.DEPT_CHECK_TIME'



delete from departments
where department_id=-4;--영향을 받는 오류가 없어도 트리거가 실행되어, 오류 메시지 출력함.
-----------------------------
Error report -
SQL Error: ORA-20010: DML operations not allowed now
ORA-06512: at "HR.DEPT_CHECK_TIME", line 4
ORA-04088: error during execution of trigger 'HR.DEPT_CHECK_TIME'


--트리거 조회 테이블명
select * from user_objects
where object_name='DEPT_CHECK_TIME';

select * from user_triggers
where trigger_name='DEPT_CHECK_TIME';

 

 

 

user conditional Predicates

create or replace trigger dept_check_time
before
insert or update or delete
on DEPARTMENTS
begin

  if  to_number (to_char(sysdate,'hh24') ) not between 8 and 16 then
     -- user conditional Predicates, 
     if inserting then
     raise_application_error(-20010, 'Insert operations not allowed now ');
     elsif deleting then
     raise_application_error(-20011, 'Delete operations not allowed now ');
     elsif updating then
     raise_application_error(-20012, 'Update operations not allowed now ');
     end if;
  end if;

end;



delete from departments;
------------------------------
Error report -
SQL Error: ORA-20011: Delete operations not allowed now
ORA-06512: at "HR.DEPT_CHECK_TIME", line 7
ORA-04088: error during execution of trigger 'HR.DEPT_CHECK_TIME'



update departments
set department_name='x'
where department_id = -5;
------------------------------
Error report -
SQL Error: ORA-20012: Update operations not allowed now
ORA-06512: at "HR.DEPT_CHECK_TIME", line 9
ORA-04088: error during execution of trigger 'HR.DEPT_CHECK_TIME'