【趙強老師】利用數(shù)據(jù)庫觸發(fā)實現(xiàn)復雜的安全性檢查

一、什么是觸發(fā)器

數(shù)據(jù)庫觸發(fā)器是一個與表相關(guān)聯(lián)的，存儲的PL/SQL 語句。每當一個特定的數(shù)據(jù)操作語句（insert update delete）在指定的表上發(fā)出時，Oracle自動執(zhí)行觸發(fā)器中定義的語句序列。

創(chuàng)新互聯(lián)公司：于2013年開始為各行業(yè)開拓出企業(yè)自己的“網(wǎng)站建設(shè)”服務(wù)，為上1000家公司企業(yè)提供了專業(yè)的成都網(wǎng)站設(shè)計、網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計和網(wǎng)站推廣服務(wù)， 按需定制設(shè)計由設(shè)計師親自精心設(shè)計，設(shè)計的效果完全按照客戶的要求，并適當?shù)奶岢龊侠淼慕ㄗh，擁有的視覺效果，策劃師分析客戶的同行競爭對手，根據(jù)客戶的實際情況給出合理的網(wǎng)站構(gòu)架，制作客戶同行業(yè)具有領(lǐng)先地位的。

觸發(fā)器的應(yīng)用場景如下：

• 復雜的安全性檢查
• 數(shù)據(jù)的確認
• 數(shù)據(jù)庫審計
• 數(shù)據(jù)的備份和審計

二、創(chuàng)建Oracle觸發(fā)器的語法

CREATE [OR REPLACE] TRIGGER trigger_name  
{BEFORE | AFTER }  
{INSERT | DELETE | UPDATE [OF column [, column …]]}  
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]  
ON [schema.]table_name | [schema.]view_name  
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]  
[FOR EACH ROW ]  
[WHEN condition]  
PL/SQL_BLOCK | CALL procedure_name;

其中：

• BEFORE 和AFTER指出觸發(fā)器的觸發(fā)時序分別為前觸發(fā)和后觸發(fā)方式，前觸發(fā)是在執(zhí)行觸發(fā)事件之前觸發(fā)當前所創(chuàng)建的觸發(fā)器，后觸發(fā)是在執(zhí)行觸發(fā)事件之后觸發(fā)當前所創(chuàng)建的觸發(fā)器。
• FOR EACH ROW選項說明觸發(fā)器為行觸發(fā)器。
• 行觸發(fā)器和語句觸發(fā)器的區(qū)別表現(xiàn)在：行觸發(fā)器要求當一個DML語句操走影響數(shù)據(jù)庫中的多行數(shù)據(jù)時，對于其中的每個數(shù)據(jù)行，只要它們符合觸發(fā)約束條件，均激活一次觸發(fā)器；而語句觸發(fā)器將整個語句操作作為觸發(fā)事件，當它符合約束條件時，激活一次觸發(fā)器。
• 當省略FOR EACH ROW 選項時，BEFORE 和AFTER 觸發(fā)器為語句觸發(fā)器，而INSTEAD OF 觸發(fā)器則只能為行觸發(fā)器
• REFERENCING 子句說明相關(guān)名稱，在行觸發(fā)器的PL/SQL塊和WHEN 子句中可以使用相關(guān)名稱參照當前的新、舊列值，默認的相關(guān)名稱分別為OLD和NEW。觸發(fā)器的PL/SQL塊中應(yīng)用相關(guān)名稱時，必須在它們之前加冒號(:)，但在WHEN子句中則不能加冒號。
• WHEN 子句說明觸發(fā)約束條件。Condition 為一個邏輯表達時，其中必須包含相關(guān)名稱，而不能包含查詢語句，也不能調(diào)用PL/SQL 函數(shù)。WHEN 子句指定的觸發(fā)約束條件只能用在BEFORE 和AFTER 行觸發(fā)器中，不能用在INSTEAD OF 行觸發(fā)器和其它類型的觸發(fā)器中。
• 當一個基表被修改( INSERT, UPDATE, DELETE)時要執(zhí)行的存儲過程，執(zhí)行時根據(jù)其所依附的基表改動而自動觸發(fā)，因此與應(yīng)用程序無關(guān)，用數(shù)據(jù)庫觸發(fā)器可以保證數(shù)據(jù)的一致性和完整性。

三、Oracle觸發(fā)器的類型

• 行級觸發(fā)器：對DML語句影響的每個行執(zhí)行一次，例如，一條update語句更新了100條數(shù)據(jù)，如果我們針對update定義了行級觸發(fā)器，那么行級觸發(fā)器將會被觸發(fā)100次 。
• 語句級觸發(fā)器：對每個DML語句執(zhí)行一次，例如，一條update語句更新了200條數(shù)據(jù)，如果我們針對update定義了語句級觸發(fā)器，那么語句級觸發(fā)器將會被觸發(fā)1次 。

四、利用數(shù)據(jù)庫觸發(fā)實現(xiàn)復雜的安全性檢查

/*
實施復雜的安全性檢查
禁止在非工作時間插入新員工
周末：to_char(sysdate,'day') in ('星期六','星期日')
上班前和下班后:to_number(to_char(sysdate,'hh34')) not between 9 and 18 
*/
create or replace trigger securityemp
before insert
on emp
begin
  if to_char(sysdate,'day') in ('星期六','星期日') or
     to_number(to_char(sysdate,'hh34')) not between 9 and 18 then
     --禁止insert操作 拋出異常
     raise_application_error(-20001,'禁止在非工作時間插入新員工');
  end if;
end;
/

• 在上面的例子中，我們禁止在非工作時間插入新員工，從而實現(xiàn)執(zhí)行insert語句時候的安全性檢查。