十一、Oracle 触发器

• 一、Oracle 操作集
• 二、Oracle 数据类型
• 三、Oracle 函数
• 四、Oracle 视图
• 五、Oracle 序列
• 六、Oracle 同义词
• 七、Oracle 索引
• 八、Oracle PL/SQL
• 九、Oracle 存储函数
• 十、Oracle 存储过程
• 十一、Oracle 触发器
• 十二、Oracle 查询

十一、触发器

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

• 触发器可用于：

1. 数据确认。
2. 实施复杂的安全性检查。
3. 做审计，跟踪表上所做的数据操作等。
4. 数据的备份和同步。

• 触发器分类：

1. 前置触发器（BEFORE）。
2. 后置触发器（AFTER）。

1. 创建触发器

CREATE [OR REPLACE] TRIGGER '触发器名'
BEFORE | AFTER -- 触发时间。
[DELETE] [[OR]INSERT] [[OR]UPDATE [OF '列名']] -- 触发事件。
ON '表名'
[FOR EACH ROW] [WHEN('条件')]
declare
	-- ...声明变量
begin
    'PLSQL块'
End;

-- `FOR EACH ROW`：指定对于受触发事件影响的每一行，都要激活触发器的动作。

• FOR EACH ROW：
  是标注此触发器是 行级触发器、语句级触发器。
• 在触发器中 触发语句 与 伪记录变量 的值。

• 在触发器创建中，每个表每个事件每次只允许一个触发器。
• 一个表最多支持 6 个触发器。

CREATE TRIGGER `test`.`customers_insert_trigger`
AFTER
INSERT
ON `test`.`customers` 
FOR EACH ROW SET @str = 'one customers added!' 

CREATE TRIGGER `test`.`customers_insert_trigger`
AFTER
INSERT
ON `test`.`customers`
FOR EACH ROW SET @str = NEW.cus_id;

-- NEW：虚拟表，用来访问新插入的行（在`INSERT、UPDATE`触发器，不区分大小写）。
-- OLD：虚拟表，用来访问被删除的行（在`DELETE、UPDATE`触发器，不区分大小写）。

CREATE TRIGGER `test`.`customers_update_trigger`
BEFORE
UPDATE 
ON `test`.`customers` 
FOR EACH ROW
SET NEW.cus_address = OLD.cus_contact; 

2. 删除触发器

DROP TRIGGER [IF EXISTS] '触发器名';

DROP TRIGGER IF EXISTS `test`.`customers_insert_trigger`;

二、案例

1. 前置触发器

• 需求：当用户输入 本月累计表数 后，自动计算出 本月使用数。

create or replace trigger 'tri_account_update_num1'
before

update of 'num1'
on 't_account'

for each row
declare
begin
	:new.usenum: = :new.num1 - :new.num0;
end; 

-- num0=95076、num1=99324、usenum=4248

UPDATE T_ACCOUNT
SET num1 = 100000
WHERE ID = 66;
-- num0=95076、num1=100000、usenum=4924

2. 后置触发器

• 需求：当用户修改了 业主信息表的数据 时，记录 修改前与修改后 的值。

-- 创建业主修改日志表，用于记录业主更改前后的名称。
create table `t_owners_log`
(
    updatetime date,
    ownerid number,
    oldname varchar2(30),
    newname varchar2(30)
);

-- 创建后置触发器，自动记录业主更改前后日志
create trigger 'tri_owners_log'
after

update of 'name'
on 't_owners' 

for each row
declare
begin
	insert into 't_owners_log'
	values (sysdate, :old.id, :old.name, :new.name);
end;

• 测试。

-- 更新数据
update t_owners set name = 'wy' where id = 11;
commit;

-- 查询日志表
select * from `t_owners_log`;
-- 2022-04-26 16:48:02 11 王岩 wy