触发器

数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句（insert、update、delete）

在指定的表上发出时，ORACLE自动地执行触发器中定义的语句序列。

第一个触发器

--第一个触发器：每当成功插入新员工后，自动打印“成功插入新员工”
CREATE TRIGGER sayNewEmp
AFTER INSERT
ON emp
DECLARE
BEGIN  
 dbms_output.put_line('成功插入新员工');
END;

INSERT INTO emp(empno,ename,sal,deptno) VALUES(1001,'tom',3000,10);

成功插入新员工

语句级触发器

在指定的操作语句操作之前或者之后执行一次，不管这条语句影响了多少行。针对的是表。

行级触发器

触发语句作用的每一条记录都被触发。在行级触发器中使用:old和:new伪记录变量，识别值的状态。针对的是行。

--创建触发器的语法
CREATE [OR REPLACE] TRIGGER 触发器名
{BEFORE|AFTER} 
{delete|INSERT|UPDATE[OF 列名]}
ON 表名
[FOR EACH ROW [WHEN (条件)]]
DECLARE
BEGIN  
 
END;

 

触发器的具体应用场景

复杂的安全性检查

--实施复杂的安全性检查：禁止在非工作时间插入新员工
/*
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

DECLARE
BEGIN 
 IF to_char(SYSDATE,'day') IN ('星期六','星期日') OR
    to_number(to_char(SYSDATE,'hh24')) NOT BETWEEN 9 AND 18 THEN
    --禁止insert新员工
    raise_application_error(-20001,'禁止在非工作时间插入新员工');
 END IF;
END;

数据的确认

--数据的确认：涨工资不能越涨越少
/*
:old和:new代表的是同一条记录
old表示操作该行之前，这一行的值
new表示之后的值
*/

CREATE OR REPLACE TRIGGER checkSalary
BEFORE UPDATE
ON emp
FOR EACH ROW

BEGIN 
 IF :new.sal < :old.sal THEN
    --禁止insert新员工
    raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水,涨后的薪水: '||:new.sal||' 涨前的薪水: '||:old.sal);
 END IF;
END;

update emp set sal=sal-1 where empno=7839;
update emp set sal=sal-1 where empno=7839
       *
ERROR at line 1:
ORA-20002: ▒Ǻ▒▒нˮ▒▒▒▒▒▒▒▒▒▒ǰ▒▒нˮ,▒Ǻ▒▒нˮ: 19391.31 ▒▒ǰ▒▒нˮ:
19392.31
ORA-06512: at "SCOTT.CHECKSALARY", line 4
ORA-04088: error during execution of trigger 'SCOTT.CHECKSALARY'

数据库审计

/*
给员工涨工资，当涨后的薪水超过6000的时候，审计该员工的信息
*/
--创建表，用于保存审计信息
CREATE TABLE audit_info
(
 information VARCHAR2(200)
);
CREATE OR REPLACE TRIGGER doAuditEmpSalary
AFTER UPDATE
ON emp
FOR EACH ROW

BEGIN  
 --当涨后的薪水大于6000，插入审计信息
 IF :new.sal > 6000 THEN
   INSERT INTO audit_info VALUES(:new.empno||' '||:new.ename||' '||:new.sal);
 END IF;
END;

UPDATE emp SET sal=sal+2000;
COMMIT;
select * from audit_info;

INFORMATION
--------------------------------------------------------------------------------
7369 SMITH 6251.74
7499 ALLEN 7153.63
7521 WARD 6589.96
7566 JONES 12044.9
7654 MARTIN 6589.96
7698 BLAKE 11743.59
7782 CLARK 11099.39
7788 SCOTT 9408.35
7839 KING 21392.31
7844 TURNER 6992.58
7876 ADAMS 6348.38

INFORMATION
--------------------------------------------------------------------------------
7900 JAMES 6106.81
7902 FORD 9408.35
7934 MILLER 6670.48

数据的备份和同步

CREATE TABLE empBack AS SELECT * FROM emp;
--数据的备份和同步，利用触发器实现数据的同步部分
/*
当给员工涨完工资后，自动备份新的工资到备份表中
*/
--创建表，用于保存审计信息

CREATE OR REPLACE TRIGGER syncSalary
AFTER UPDATE
ON emp
FOR EACH ROW

BEGIN  
 --当主表更新后，自动更新备份表
 UPDATE empBack SET sal=:new.sal WHERE empno=:new.empno;
END;

UPDATE emp SET sal=sal+10 WHERE empno=7839;
COMMIT;