觸發器是一種特殊的存儲過程,和存儲過程和存儲函數一樣是一個pl/sql程式塊,觸發器是不能接受參數,不能顯示調用,只是隨著事件觸發隱式運行的存儲過程程式塊。 ...
觸發器相關概念及語法
觸發器概述
什麼是觸發器
觸發器是一種特殊的存儲過程,和存儲過程和存儲函數一樣是一個pl/sql程式塊,觸發器是不能接受參數,不能顯示調用,只是隨著事件觸發隱式運行的存儲過程程式塊。
觸發器的應用場景
- 複雜的安全性檢查
- 數據確認
- 實現審計功能
- 完成數據的備份和同步
觸發器概念和第一個觸發器
概念
資料庫觸發器是一個與表相關聯的,存儲的PL/SQL程式,每當一個特定的資料庫操作語句(insert ,update,delete)在指定的表上發出時,Oracle自動地執行觸發器中定義得語句序列。
第一個觸發器
-- 第一個觸發器:每當成功插入新員工後,自動列印“成功插入新員工”
-- 觸發器單詞:trigger
create trigger saynewem -- 創建觸發器名稱
after insert -- 在插入操作以後
on emp -- 針對emp的表
declare -- 操作體
begin
dbms_output.put_line('成功插入新員工'); -- 觸發器操作的內容
end;
觸發器的應用場景
- 複雜的安全性的場景(涉及到許可權的問題);
- 數據的確認(涉及數據是否合理問題);
- 數據的審計(涉及到數據的增、刪、改的操作記錄);
- 數據的備份和同步(備份和同步重要);
觸發器的語法
create[or replace] trigger 觸發器名
{before|after}
{delete|insert|update[of 列名]}
on 表名
[for each row [when(條件)]] (有這條語句的話就是行級觸發器,否則就是語句級觸發器)
PLSQL 塊
觸發器的類型
語句級觸發器
在指定的操作語句操作之前或之後執行一次,不管這條語句影響了多少行。(針對的是表,觸發最多一次)
行級觸發器
觸發語句作用的每一條記錄都被觸發。在行級觸發器中使用 :old 和 :new 偽記錄變數,識別值的狀態。(針對的是行,觸發器觸發次數不一樣,有多少行滿足條件就觸發多少次)
觸發器應用
應用一——複雜的安全性檢查
-- 例如禁止在非工作時間插入數據
/**
1.周末: to_char(sysdate,'day') in ('星期六',‘星期日’)
2.上班前,下班後: to_number(to_char(sysdate,'hh24')) 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,'hh24')) not between 9 and 18 then
raise_application_error(-20001,'禁止在非工作時間插入新員工');
end if;
end;
/
應用二——數據確認
-- 漲工資不能越漲越少
/**
* :old 和 :new 代表同一條記錄
* :old 表示操作該行之前,這一行的值
* :new 表示操作該行值後,這一行的值
*/
create or replace trigger check_salary
before update
on emp
for each row
begin
if :new.sal<:odl.sal then
raise_application_error(-20002,'漲後薪水不能少於漲前薪水。 漲後薪水為:'||:new.sal ||'漲前的薪水:'||:old.sal);
end if;
end;
應用三——實現審計功能
-- 給員工漲工資,當漲後的薪水超過6000塊時候,審計該員工的信息
-- 創建表,用於保存審計信息
create table audit_info(
information varchar2(200)
);
create or replace trigger do_audit_emp_salary
after update
on emp
for each row
begin
if :new.sal>6000 then
insert into audit_info values(:new.empno||' '||:new.ename||' '||:new.sal);
end if;
end;
應用四——完成數據的備份和同步
-- 創建備份表
create table emp_back as select * from emp;
-- 利用觸發器實現數據的同步部分
-- 當給員工漲完工資後,自動備份新的工資到備份表中
create or replace trigger sync_salary
after update
on emp
for each row
begin
-- 當主表更新後,自動更新備份表
update emp_back set sal=:new.sal where empno=:new.empno;
end;
/
Reference: