一、什么是DDL和數據庫事件觸發(fā)器？

DDL語句觸發(fā)DDL觸發(fā)器：CREATE，ALTER或DROP。
數據庫事件觸發(fā)器由數據庫中的非SQL事件觸發(fā)，例如：
?用戶連接到數據庫或與數據庫斷開連接。
?DBA啟動或關閉數據庫。

?用戶會話中引發(fā)了特定的異常。

（1）在DDL語句中創(chuàng)建觸發(fā)器語法

?ON DATABASE在數據庫中的所有模式上觸發(fā)DDL

?ON SCHEMA僅針對您自己的模式中的對象觸發(fā)DDL

CREATE [OR REPLACE] TRIGGER trigger_name Timing [ddl_event1 [OR ddl_event2 OR ...]] ON {DATABASE|SCHEMA} trigger_body

（2）DDL觸發(fā)器的示例

每次在模式中創(chuàng)建新的數據庫對象時，都希望寫入日志記錄：

CREATE OR REPLACE TRIGGER log_create_trigg AFTER CREATE ON SCHEMA BEGININSERT INTO log_tableVALUES (USER, SYSDATE); END;只要有任何（類型）的對象被創(chuàng)建，觸發(fā)器就會觸發(fā)。 您不能創(chuàng)建引用特定數據庫對象的DDL觸發(fā)器。

（3）DDL觸發(fā)器的第二個例子

防止從模式中刪除任何對象。

CREATE OR REPLACE TRIGGER prevent_drop_trigg BEFORE DROP ON SCHEMA BEGINRAISE_APPLICATION_ERROR (-20203, 'Attempted drop – failed'); END;

只要有任何（類型）的對象被刪除，觸發(fā)器就會觸發(fā)。 同樣，您不能創(chuàng)建引用特定數據庫對象的DDL觸發(fā)器。

（4）在數據庫事件語法上創(chuàng)建觸發(fā)器

?ON DATABASE觸發(fā)數據庫中所有會話的事件觸發(fā)器。
?ON SCHEMA僅為您自己的會話觸發(fā)觸發(fā)器。
CREATE [OR REPLACE] TRIGGER trigger_name timing [database_event1 [OR database_event2 OR ...]] ON {DATABASE|SCHEMA} trigger_body

二、LOGON、LOGOFF和SERVERERROR

示例1：LOGON和LOGOFF觸發(fā)器

CREATE OR REPLACE TRIGGER logon_trig AFTER LOGON ON SCHEMA BEGIN INSERT INTO log_trig_table(user_id,log_date,action)VALUES (USER, SYSDATE, 'Logging on'); END;

CREATE OR REPLACE TRIGGER logoff_trig BEFORE LOGOFF ON SCHEMA BEGIN INSERT INTO log_trig_table(user_id,log_date,action)VALUES (USER, SYSDATE, 'Logging off'); END;

示例2：SERVERERROR觸發(fā)器
想保留會話中發(fā)生的任何ORA-00942錯誤的日志：

CREATE OR REPLACE TRIGGER servererror_trig AFTER SERVERERROR ON SCHEMA BEGIN IF (IS_SERVERERROR (942)) THENINSERT INTO error_log_table ... END IF; END;

三、觸發(fā)器中的CALL語句

沒有結束;語句，并且在CALL語句結尾處沒有分號。

語法：

CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON table_name [REFERENCING OLD AS old | NEW AS new] [FOR EACH ROW] [WHEN condition] CALL procedure_name

例子：

CREATE OR REPLACE TRIGGER log_employee BEFORE INSERT ON EMPLOYEESCALL log_execution

四、突變表和行觸發(fā)器

????突變表是一個當前正在由DML語句修改的表。

????行觸發(fā)器不能從變異表中選擇，因為它會看到不一致的數據集（當觸發(fā)器嘗試讀取數據時，表中的數據將會改變）。 但是，如果需要，行觸發(fā)器可以從不同的表中進行選擇。

此限制不適用于DML語句觸發(fā)器，僅適用于DML行觸發(fā)器。

突變表：例子

CREATE OR REPLACE TRIGGER check_salaryBEFORE INSERT OR UPDATE OF salary, job_id ON employeesFOR EACH ROW DECLAREv_minsalary employees.salary%TYPE;v_maxsalary employees.salary%TYPE; BEGINSELECT MIN(salary), MAX(salary)INTO v_minsalary, v_maxsalaryFROM employeesWHERE job_id = :NEW.job_id;IF :NEW.salary < v_minsalary OR:NEW.salary > v_maxsalary THENRAISE_APPLICATION_ERROR(-20505,'Out of range');END IF; END; UPDATE employees SET salary = 3400 WHERE last_name = 'Davies';出錯：
ORA-04091: table USVA_TEST_SQL01_T01_EMPLOYEES is mutating, trigger/function may not see it ORA-06512: at “USVA_TEST_SQL01_T01.CHECK_SALARY”, line 5 ORA-04088: error during execution of trigger ‘USVA_TEST_SQL01_T01.CHECK_SALARY’ 3. WHERE last_name – ‘Davies’;

五、觸發(fā)器的更多可能用途

????不應該創(chuàng)建觸發(fā)器來執(zhí)行某些可以通過其他方式輕松完成的操作，例如通過檢查約束或適當的對象權限。 但是有時你必須創(chuàng)建一個觸發(fā)器，因為沒有其他方法可以做需要的事情。
????以下示例只顯示了必須創(chuàng)建觸發(fā)器的三種情況。 還有更多！

（1）第一個例子

數據庫安全性（誰可以做什么）通常由系統和對象權限控制。 例如，用戶SCOTT需要更新EMPLOYEES行：

GRANT UPDATE ON employees TO scott;

但是，SCOTT被允許這樣做時，單憑權限無法控制。 為此，我們需要一個觸發(fā)器：

CREATE OR REPLACE TRIGGER weekdays_empBEFORE UPDATE ON employees BEGIN IF (TO_CHAR (SYSDATE, 'DY') IN ('SAT','SUN')) THENRAISE_APPLICATION_ERROR(-20506,'You may only change data during normal business hours.'); END IF; END;

（2）第二個例子

數據庫完整性（允許DML）通常由約束條件控制。 例如，每個員工的工資必須至少為500美元：

ALTER TABLE employees ADD CONSTRAINT ck_salary CHECK (salary >= 500);

如果一條業(yè)務規(guī)則指出員工的薪水可以提高但不降低，這個限制并不能阻止員工的薪水從700美元降低到600美元。 為此，我們需要一個行觸發(fā)器。此代碼顯示在下一張幻燈片中。

現在我們不再需要約束了。

CREATE OR REPLACE TRIGGER check_salaryBEFORE UPDATE OF salary ON employeesFOR EACH ROWWHEN (NEW.salary < OLD.salaryOR NEW.salary < 500) BEGINRAISE_APPLICATION_ERROR (-20508,'Do not decrease salary.'); END;

（3）第三個例子

您需要創(chuàng)建一個顯示部門總工資單的報表。 你可以聲明和使用這個游標：

... CURSOR tot_sals ISSELECT SUM(salary)FROM employeesWHERE department_id = p_dept_id; ...但是，如果在一個大型組織中，該部門有10,000名員工呢？ 從EMPLOYEES表中抽取10,000行可能太慢。 下面展示了一個更快的方法來做到這一點。

首先，我們在DEPARTMENTS表中添加一個新列以存儲每個部門的總工資單：

ALTER TABLE DEPARTMENTS ADD (total_salary NUMBER(12,2));

接下來，只填寫當前總工資單的這一欄：

UPDATE departments dSET total_salary = (SELECT SUM(salary) FROM employeesWHERE department_id = d.department_id);

現在，我們必須在更改工資時保持這一新列。 這是通過使用DML行觸發(fā)器完成的。

CREATE OR REPLACE PROCEDURE increment_salary(p_id IN NUMBER, p_new_sal IN NUMBER) IS BEGINUPDATE departmentsSET total_salary = total_salary + NVL(p_new_sal,0)WHERE department_id = p_id; END increment_salary; CREATE OR REPLACE TRIGGER compute_salary AFTER INSERT OR UPDATE OF salary OR DELETE ON employees FOR EACH ROW BEGIN IF DELETING THEN increment_salary(:OLD.department_id,(:OLD.salary * -1)); ELSIF UPDATING THEN increment_salary(:NEW.department_id,(:NEW.salary - :OLD.salary)); ELSE increment_salary(:NEW.department_id,:NEW.salary); END IF; END;

總結

以上是生活随笔為你收集整理的Oracle入门（十四.22）之创建DDL和数据库事件触发器的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。