資料庫開發---常用物件-觸發器

 

---觸發器

觸發器在資料庫裡以獨立的物件儲存

觸發器是由一個事件來啟動執行

觸發器常用來完成由資料庫的完整性約束難以完成的複雜業務規則的約束，或用來監視對資料庫的各種操作，實現審計的功能。

---觸發器分類

1. DML觸發器

2.替代觸發器

3.系統觸發器

---另外一種分法:

語句級(STATEMENT)觸發器

行級(ROW)觸發器

---另外一種分法:

before

after

------行級觸發器專用

:NEW 修飾符訪問操作完成後列的值

:OLD 修飾符訪問操作完成前列的值

 

:old.deptno 刪除之前的資料

:new.deptno 刪除之後的資料

--1. 替代(INSTEAD OF)觸發器----只能應用在行級上面，無論有沒有 for each row

只能用在檢視上

CREATE OR REPLACE VIEW v_emp AS

SELECT deptno, count(*) total_emp, sum(sal) total_sal

FROM emp

GROUP BY deptno;---簡單檢視和原表中的資料是一對一的

 

 

select * from  v_emp;

 

rollback

delete from v_emp where deptno=10;-----複雜檢視不能做變更，使用替代觸發器

 

CREATE OR REPLACE TRIGGER tr_vemp_del

  INSTEAD OF DELETE ON v_emp

  FOR EACH ROW

BEGIN

  DELETE FROM emp WHERE deptno = :old.deptno;

END tr_vemp_del;

/

DELETE FROM v_emp WHERE deptno=10;

--2.DML觸發器

---語句級觸發器

CREATE OR REPLACE TRIGGER tr_dept_time

  BEFORE INSERT OR DELETE OR UPDATE ON dept

BEGIN

  IF (TO_CHAR(sysdate, 'DAY') IN ('星期六', '星期日')) OR

     (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00') THEN

    RAISE_APPLICATION_ERROR(-20001, '非上班時間，不能修改dept表');

  END IF;

END;

/

 

 delete from dept where deptno=10;

 --------------------------------------------------------------

 ----行級觸發器

CREATE OR REPLACE TRIGGER tr_emp_sal_comm

  BEFORE UPDATE OF sal, comm OR DELETE ON scott.emp

  FOR EACH ROW

  WHEN (old.deptno = 10)

BEGIN

  CASE

    WHEN UPDATING('sal') THEN

      IF :NEW.sal < :old.sal THEN

        RAISE_APPLICATION_ERROR(-20001, '部門10的人員的工資不能降');

      END IF;

    WHEN UPDATING('comm') THEN

      IF :NEW.comm < :old.comm THEN

        RAISE_APPLICATION_ERROR(-20002, '部門10的人員的獎金不能降');

      END IF;

    WHEN DELETING THEN

      RAISE_APPLICATION_ERROR(-20003, '不能刪除部門10的人員記錄');

  END CASE;

END;

select * from emp;

UPDATE emp SET sal = 2000 WHERE empno = 7839;

UPDATE emp SET sal = 2    WHERE empno = 7788;

 

---INSTEAD OF觸發器需要注意以下幾點：

1.只能被建立在檢視上，並且該檢視沒有指定WITH CHECK OPTION選項。

2.不能指定BEFORE 或 AFTER選項。

3.FOR EACH ROW子句是可選的，即INSTEAD OF觸發器只能在行級上觸發、或只能是行級觸發器，沒有必要指定。---為了可讀性更好，可以加上

-----3. 系統事件觸發器

CREATE TABLE ddl_event

(crt_date timestamp PRIMARY KEY,

 event_name VARCHAR2(20),

 user_name VARCHAR2(10),

 obj_type VARCHAR2(20),

 obj_name VARCHAR2(20));

 

CREATE OR REPLACE TRIGGER tr_ddl

  AFTER DDL ON SCHEMA

BEGIN

  INSERT INTO ddl_event

  VALUES

    (systimestamp,

     ora_sysevent,

     ora_login_user,

     ora_dict_obj_type,

     ora_dict_obj_name);

END tr_ddl;

/

 create table tt(id number);

 

 select * from ddl_event;

 SELECT * FROM log_event;

 CREATE TABLE log_event

(user_name VARCHAR2(10),

 address VARCHAR2(20),

 logon_date timestamp,

 logoff_date timestamp);

CREATE OR REPLACE TRIGGER tr_logon

  AFTER LOGON ON DATABASE

BEGIN

  INSERT INTO log_event

    (user_name, address, logon_date)

  VALUES

    (ora_login_user, ora_client_ip_address, systimestamp);

END tr_logon;

/

CREATE OR REPLACE TRIGGER tr_logon

  AFTER LOGON ON DATABASE

BEGIN

  INSERT INTO log_event

    (user_name, address, logon_date)

  VALUES

    (ora_login_user, ora_client_ip_address, systimestamp);

END tr_logon;

 

 

CREATE OR REPLACE TRIGGER tr_nologon

  BEFORE LOGOFF ON DATABASE

BEGIN

  INSERT INTO log_event

    (user_name, address, logon_date)

  VALUES

    (ora_login_user, ora_client_ip_address, systimestamp);

END tr_nologon;

 

 

 

重新編譯觸發器

ALTER TRIGGER [schema.] trigger_name COMPILE;

DROP TRIGGER trigger_name;

禁用或啟用觸發器:

ALTER TIGGER trigger_name [DISABLE | ENABLE ];

DISABLE  失效

ENABLE 生效

ALTER TABLE [schema.]table_name {ENABLE|DISABLE} ALL TRIGGERS;

 

ALTER TABLE emp DISABLE ALL TRIGGERS;

 

 

SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT,

 TABLE_OWNER, BASE_OBJECT_TYPE, REFERENCING_NAMES,

 STATUS, ACTION_TYPE

FROM user_triggers;