Oracle中的DML触发器

什么是DML触发器

DML触发器是指基于DML操作所建立的触发器.

(DML操作:SELECT、UPDATE、INSERT、DELETE,用来对数据库里的数据进行操作)

DML触发器的作用

数据库触发器的常见用途包括：

1. 记录并审核用户对表中数据的修改操作，实现审计功能。
2. 实现比CHECK约束更加复杂的完整性约束，比如禁止非业务时间的数据操作。
3. 实现某种业务逻辑，比如在增加或删除员工时，自动更新部门中的人数。
4. 使用触发器生成序列号的值，为字段提供默认的数据。
5. 实现数据的同步复制。

DML触发器的缺点

虽然触发器可以帮助我们实现许多功能，但是它也存在一些缺点：

1. 触发器增加了数据库结构的复杂度和系统维护的难度。
2. 触发器由数据库服务器运行，需要占用更多的数据库资源。
3. 触发器也是一种存储过程，所以不同数据库之间的可移植性比较差。
4. 触发器不能接收参数，只能基于当前的触发对象进行操作。

总之，合理利用触发器可以为我们的业务实现带来便利，但是不要过度依赖触发器，避免造成数据库的性能下降和维护困难。

DML触发器类型

1.语句触发器

在指定的操作语句之前或之后执行一次,不管这条语句影响了多少行.

2.行触发器

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

:old 表示操作之前该行的值

:new 表示操作之后该行的值

创建DML触发器语法:

语句触发器:

delete from empnew where empno=7788;

create [or replace] trigger trigger_name
{before|after}
{delete|insert|update|[of列名]}
on 表名


PLSQL块

行触发器:

delete from empnew where empno=7788;

create [or replace] trigger trigger_name
{before|after}
{delete|insert|update|[of列名]}
on 表名

[for each row [when(条件)]]

PLSQL块

示例

1: 实现数据审计

功能: 审计员工信息表数据的变化,审计删除时间,以及被删除的雇员名.

--创建审计表
create table delete_emp_audit(
  name varchar2(10), --员工姓名
  delete_time date   --删除时间
);

--创建触发器
create or replace trigger del_emp_tigger
after delete 
on emp
for each row
begin
  insert into delete_emp_audit values(:old.ename,sysdate);
end;

--测试
delete from emp where empno =7499;

首先创建一张审计表,然后创建触发器.最后删除数据触发已创建的触发器.

查询审计表,发现里面新增了一条删除的员工姓名和删除时间.

实例2是一个行级触发器.

2:实现数据的安全保护

功能: 禁止在休息日(周六、周日)改变emp表数据

create or replace trigger emp_trigger1
before insert or update or delete
on emp
begin
  if to_char(sysdate,'day') in ('星期六','星期日') then
    raise_application_error(-20006,'不能在休息日改变员工信息!');
  end if;
end;

当执行删除操作时,会自定执行触发器

实例1属于语句触发器.

3:实现数据完整性

功能: 要求员工涨后的工资不能低于原来的工资,并且所涨的工资不能超过原工资的50%

create or replace trigger tr_check_sal
before update of sal --of 指定更新sal列时触发 触发器
on emp
for each row
when (new.sal < old.sal or new.sal > old.sal * 1.5) --此处new和old不加冒号(:),因为when语句不在qlsql块内部
begin
  raise_application_error(-20028,'工资只升不降,并且升幅不能超过50%');
end;

当修改员工工资时会触发这个触发器

实例3也是行级触发器

4.实现主键自增（常用）

在设计数据库的时候，Oracle中没有类似SQL Server中系统自动分配ID作为主键的功能，这时Oracle可以通过“序列”和“触发器”来实现ID自动增加的功能。

创建一个序列

create sequence seq_uid
increment by 1
start with 1
nomaxvalue
nocycle
cache 10;

其中：

"seq_uid"表示自定义的序列名称；

"start with 1"表示序列值从1开始；

"increment by 1"表示序列每次增加的值为1。

创建触发器Trigger

create trigger tri_uid 
 before insert on t_user
 for each row when (new.user_id is null)

begin
  select seq_uid.nextval into:new.user_id from dual;
end;

其中：

"tri_uid"表示自定义的触发器名称；

"seq_uid"表示要使用的序列名称；

"user_id"表示要实现自增的列；

"t_user"表示要实现自增的列所在的数据表。

表中原有数据：

向表中插入一条新记录：

user_id为10的记录，insert语句并没有为user_id字段赋值，但是记录中是有值的，利用触发器在数据插入之前将序列的值自动赋给新的记录。

5:实现参照完整性

功能: 级联更新dept表的主键列以及emp表的外键列

create or replace trigger upd_cascade_trigger
after update of deptno
on dept
for each row
begin
  update emp set deptno = :new.deptno where deptno = :old.deptno;
end;

执行修改dept表中deptno列,会触发定义的触发器upd_cascade_trigger,在触发器中会把emp表对应的deptno字段进行更新操作.

--测试
update dept set deptno =50 where deptno =10;

查询一下更新后的员工信息

 

以上就是对DML触发器用法的总结。