[Oracle] Trigger

Trigger

--자료의 무결성을 유지
--변경된 자료 및 변경한 유저를 기록해서 테이블의 변경정보 감시
--지정한 이벤트가(DML)가 발생할때마다 자동으로 실행되는 PL/SQL 블럭 자동 호출(호출문 없음)
--데이터 흐름 제어문(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;

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

 

 

 

 

https://blog.naver.com/frdevil/38360518