오라클 트리거, trigger 정리

▶ Triggers are procedures that are stored in the database and are implicitly run, or fired, when something happens. Traditionally, triggers supported the execution of a procedural code, in Oracle procedural SQL is called a PL/SQL block. PL stands for procedural language. When an INSERT, UPDATE, or DELETE occurred on a table or view.

Triggers support system and other data events on DATABASE and SCHEMA.

Triggers are frequently used to automatically populate table primary keys, the trigger examples below show an example trigger to do just this. We will use a built in function to obtain a globallally unique identifier or GUID.

---

▶ 트리거 Form.

 

CREATE OR REPLACE TRIGGER 트리거이름
   [선택1 필수] BEFORE or AFTER  ---------------------------------------> 트리거의 발생 시점, 보통 Before 를 많이 사용
   [선택1 이상 필수] DELETE OR INSERT OR UPDATE ON 테이블명----> 3개를 선택해서 사용 (INSERT/UPDATE/DELETE) 
   [선택] REFERENCING NEW AS NEW OLD AS OLD  ------------------> NEW , OLD 에 대한 alias 사용 가능, 
   [선택] FOR EACH ROW [statement]  --------------> 생략될경우 statement 실행
                                                    --------------> statement : 해당 이벤트에 한번만 실행됨
   [선택] WHEN (new.column = '~~')  --------------> 트리거의 조건을 생성
   
   DECLARE --- PL/SQL 구문  
     n number;
   BEGIN
    -- NEW : 새로 추가되거나 변경된 후의 값에 트리거가 적용된다.(INSERT : 입력할 값, UPDATE : 수정할 값)
    --  OLD : 변경 전의 값에 트리거가 적용된다. (UPDATE : 수정 전 값, DELETE : 삭제할 값)
     IF INSERTING THEN      ~~ ;  ------> insert 이벤트 실행 
     ELSIF UPDATING THEN    ~~ :  ----> update 이벤트 실행
     ELSIF DELETING THEN    ~~ :  ----> delete 이벤트 실행
     END IF;
     
   END;

 

 

▶ 트리거 Form. Sample

CREATE OR REPLACE TRIGGER 트리거이름
   BEFORE or AFTER  -----------------------> 트리거의 발생 시점, 보통 Before 를 많이 사용
   DELETE OR INSERT OR UPDATE ON 테이블명--> 3개를 선택해서 사용 (INSERT/UPDATE/DELETE) 
   REFERENCING NEW AS NEW OLD AS OLD-------> NEW , OLD 에 대한 alias 사용 가능, 
   FOR EACH ROW [statement]  --------------> 생략될경우 statement 실행
                             --------------> statement : 해당 이벤트에 한번만 실행됨
   WHEN (new.column = '~~')    ------------> 트리거의 조건을 생성
   
   DECLARE --- PL/SQL 구문  
     n number;
   BEGIN
   	--	NEW : 새로 추가되거나 변경된 후의 값에 트리거가 적용된다.(INSERT : 입력할 값, UPDATE : 수정할 값)
    --  OLD : 변경 전의 값에 트리거가 적용된다. (UPDATE : 수정 전 값, DELETE : 삭제할 값)
     IF INSERTING THEN      ~~ ;  ----> insert 이벤트 실행 
     ELSIF UPDATING THEN    ~~ :  ----> update 이벤트 실행
     ELSIF DELETING THEN    ~~ :  ----> delete 이벤트 실행
     END IF;
     
   END;

---

▶ Sample  ( https://livesql.oracle.com/apex/livesql/file/tutorial_D39T3OXOCOQ3WK9EWZ5JTJA.html )

1. Table 생성  

create table DEPARTMENTS (  
  deptno        number,  
  name          varchar2(50) not null,  
  location      varchar2(50),  
  constraint pk_departments primary key (deptno)  
);

---

2. TRIGGER 생성  - FOR EACH ROW

create or replace TRIGGER TG_DEPARTMENTS_BEFORE
   BEFORE DELETE OR INSERT OR UPDATE ON DEPARTMENTS
   REFERENCING NEW AS NEW  ------------------> 생략가능, defaul NEW , OLD
   FOR EACH ROW             --------------> 생략될경우 statement 실행                            
   --WHEN (length(new.name) = 3)    --------> name 의 자릿수를 체크
   DECLARE --- PL/SQL 구문  
     n number;
   BEGIN   

        IF INSERTING and :new.deptno is null then
            :new.deptno := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
             DBMS_OUTPUT.put_line('TG_DEPARTMENTS_BEFORE - INSERT');
        END IF;     

        IF UPDATING then           
             DBMS_OUTPUT.put_line('TG_DEPARTMENTS_BEFORE - UPDATE');
        END IF;    

        IF DELETING then           
             DBMS_OUTPUT.put_line('TG_DEPARTMENTS_BEFORE - DELETE');
        END IF;       

   END;

2.1. Data Insert 실행 -- 각 Insert low 당 1회식 실행 (아래 총 2번 실행)

insert into departments (name, location) values
   ('Finance','New York');

insert into departments (name, location) values
   ('Development','San Jose');

 

2.2. Data Update 실행 -- 각 Update low 당 1회식 실행 (아래 총 2번 실행)

UPDATE departments 
SET name='Lomario'
;

2.3. Data Delete 실행 -- 각 Delete low 당 1회식 실행 (아래 총 2번 실행)

DELETE from departments ;

---

3. TRIGGER 생성  - FOR EACH ROW 생략 - statement 로 실행

create or replace TRIGGER TG_DEPARTMENTS_BEFORE
   BEFORE DELETE OR INSERT OR UPDATE ON DEPARTMENTS
   REFERENCING NEW AS NEW  ------------------> 생략가능, defaul NEW , OLD
   -- FOR EACH ROW             --------------> 생략될경우 statement 실행                            
   -- WHEN (length(new.name) = 3)    --------> name 의 자릿수를 체크
   DECLARE --- PL/SQL 구문  
     n number;
   BEGIN   

        /*  데이타 low Level 이 아니기 때문에 아래 :new or :old 내용은 적용할 수 없음. */
        --IF INSERTING and :new.deptno is null then
        IF INSERTING then
            -- :new.deptno := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
             DBMS_OUTPUT.put_line('TG_DEPARTMENTS_BEFORE - INSERT');
        END IF;     

        IF UPDATING then           
             DBMS_OUTPUT.put_line('TG_DEPARTMENTS_BEFORE - UPDATE');
        END IF;    
        
        IF DELETING then           
             DBMS_OUTPUT.put_line('TG_DEPARTMENTS_BEFORE - DELETE');
        END IF;    
    

   END;

3.1. Data Insert 실행 -- 각 Insert event 당 1회식 실행 (아래 각 각 1회 실행)

insert into departments (deptno, name, location) values
   (to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'), 'Finance','New York');

insert into departments (deptno, name, location) values
   (to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'Development','San Jose');

3.2. Data Update 실행 -- 각 Update event 당 1회식 실행 (아래 1회 실행)

UPDATE departments 
SET name='Lomario'
;

3.3. Data Delete 실행 -- 각 Delete event 당 1회식 실행 (아래 1회 실행)

DELETE from departments ;