트리거 생성(CREATE TRIGGER)

트리거 생성(CREATE TRIGGER)

--기본 트리거 생성
CREATE OR REPLACE TRIGGER Audit_employee_reset
AFTER INSERT OR DELETE OR UPDATE OF COLUMN1, COLUMN2 ON TABLE_NM
BEGIN
   auditpackage.set_reason(NULL);
END;

CREATE OR REPLACE TRIGGER TRIGGER_TEST
  BEFORE DELETE OR INSERT OR UPDATE ON TABLE_NM
  FOR EACH ROW
WHEN (new.COLUMN1> 0)
DECLARE
    sal_diff number;
BEGIN

    INSERT INTO TEST1 VALUES(:old.COLUMN1||'-'||:NEW.COLUMN1);
    
    dbms_output.put('Old salary: ' || :old.COLUMN1);
    dbms_output.put('  New salary: ' || :new.COLUMN1);
    dbms_output.put_line('  Difference ' || :new.COLUMN1);
END;
/

CREATE OR REPLACE TRIGGER Dept_emplist_tr
   INSTEAD OF INSERT ON NESTED TABLE Emplist OF Dept_view
   REFERENCING NEW AS Employee
      PARENT AS Department
   FOR EACH ROW
BEGIN
-- The insert on the nested table is translated to an insert on the base table:
   INSERT INTO Emp_tab VALUES (
      :Employee.Empno, :Employee.Empname,:Employee.Salary, :Department.Deptno);
END;

--사용자 로그인 하면 실행하는 트리거? 별게 다 있네
CREATE OR REPLACE TRIGGER On_Logon  
  AFTER LOGON  
  ON The_user.Schema  
BEGIN  
  Do_Something;  
END;  

    IF INSERTING THEN    --등록이면
        ...
    ELSIF DELETING THEN  --삭제면
        ...    
    ELSE
    
    --업데이트된컬럼명이 COLUMN1 라면
    IF UPDATING('COLUMN1') THEN
        ...
    --그냥 업데이트 라면
    ELSIF UPDATING THEN
        ...        
    END IF;

--트리거 사용
ALTER TRIGGER TRIGGER_TEST ENABLE;
--트리거 사용 정지
ALTER TRIGGER TRIGGER_TEST DISABLE;

--트리거 Recompiling
ALTER TRIGGER TRIGGER_TEST COMPILE;

--트리거 이름으로 조회
SELECT Trigger_type, Triggering_event, Table_name
   FROM USER_TRIGGERS
   WHERE Trigger_name = 'TRIGGER_NAME';
   
SELECT Trigger_body
   FROM USER_TRIGGERS
   WHERE Trigger_name = 'TRIGGER_NAME';   

참고 : http://ooz.co.kr/33
원본 : https://479lgs.blog.me/220373814609