15. 触发器的使用和案例

触发器

1. 触发器简介

1.1 什么是触发器

数据库触发器是一个与表相关联的、存储的PL/SQL程序。
每当一个特定的数据操作语句(insert，update,delete)在指定的表上发出时，Oracle自动的执行触发器中定义的语句序列。

1.2 触发器的应用场景

1.复杂的安全性检查
2.数据确认
3.实现审计功能
4.完成数据的备份和同步

1.3 触发器的语法

pl/sql语法

1.4 触发器的类型

语句级触发器：（针对是表）在指定的操作语句操作之前或者操作之后执行一次，不管这条语句执行了多少行。
行级触发器： （针对一行）触发语句作用的每一条记录都被触发，在行级触发器中使用:old和:new伪记录变量，识别值的状态。

2. 第一个触发器

需求：每当成功插入一条数据，自动打印“成功增加一位用户”

2.1 创建触发器

语法

CREATE [OR REPLACE] TRIGGER 触发器名称
-- 操作之前/之后
{BEFORE | AFTER}
--操作属性，更新可以指定列名
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名 
-- 如果存在这条语句，就是行级触发器，否则为语句触发器
[FOR EACH ROW[WHEN(条件)]]
PLSQL块

案例

-- TRIGGER关键字
CREATE OR REPLACE TRIGGER INSERTNEWITEM 
-- 插入之后操作，需要指明表
AFTER INSERT ON YSTEXT
--开头
DECLARE
BEGIN
  DBMS_OUTPUT.PUT('成功增加一位用户');
END;

运行

结果

3. 案例一：实施复杂的安全性检查-禁止在非工作时间插入新用户

触发器应用场景一：实施复杂的安全性检查

禁止在非工作时间插入新用户

条件

1.非工作时间
    1.周六日：to_char(sysdate,'day') in ('星期六','星期日')
    2.上班前/下班后：
2.语句级触发器，针对表

代码

create or replace TRIGGER ADDUSERINFO
BEFORE INSERT ON YSTEXT
BEGIN
    --设置条件
    IF to_char(SYSDATE,'day') in ('星期六','星期日') OR TO_NUMBER(to_char(SYSDATE, 'hh24')) not between 9 and 18
        THEN
        --禁止插入新员工 
        --这里使用抛出异常的方法，参数1为异常号，规定范围在-20000 到 -20999，第二个参数为错误信息
        RAISE_APPLICATION_ERROR(-20001, '禁止在非工作时间插入用户信息');
    END IF;
END;

结果

4. 案例二:数据的确认-涨工资，涨后的薪水不能低于涨前的薪水

触发器应用场景二：数据的确认

涨工资，涨后的薪水不能低于涨前的薪水

条件

1.行级触发器，针对每条数据
2. :old 和 :new 代表同一条记录
3. :old 表示操作该行之前，这一行的值
4. :new 表示操作该行之后，这一行的值

代码

create or replace TRIGGER CHECKMONEY 
BEFORE UPDATE ON YSTEXT
    FOR EACH ROW
BEGIN
    IF :new.money < :old.money
    THEN
        RAISE_APPLICATION_ERROR(-20002, '涨后的工资不能小于涨前的工资,涨后的薪水'||:new.money ||'涨前的薪水'||:old.money);
    END IF;
END;

结果

5. 案例三:数据库的审计-给员工涨工资，当工资超过6000时，保存数据到另一张表作为审计

数据库的审计

给员工涨工资，当工资超过6000时，保存数据到另一张表作为审计

条件

1.行级触发器

代码

--案例三数据库的审计
--给员工涨工资，当工资超过6000时，保存数据到另一张表作为审计
CREATE OR REPLACE TRIGGER AUDITMONEY 
BEFORE UPDATE ON YSTEXT
FOR EACH ROW
BEGIN
    --当涨后的薪水大于6000，插入信息到审计表
    IF :new.money > 6000 THEN
          INSERT INTO AUDIT_INFO("ID", "NAME", "MONEY") VALUES (:new.id,:new.name,:new.money);
        END IF;
END;

执行update

UPDATE YS.YSTEXT SET MONEY = MONEY-3000

结果：审计表插入一条数据

6. 案例四：数据的备份和同步-利用触发器实现数据同步

数据的备份和同步

利用触发器实现数据同步，当给员工涨完工资之后，自动备份新的工资到备份表中

条件

 1.行级触发器    

代码

CREATE OR REPLACE TRIGGER BACK_YSTEXT 
BEFORE UPDATE ON YSTEXT
FOR EACH ROW
BEGIN
  --当主表更新后，自动更新备份表
  UPDATE ystext_back SET money = :new.money where id = :new.id;
END;

源表起始数据

目标表起始数据

运行

源表结果数据

目标表结果数据