Oracle中创建触发器
1.说明
- 数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(insert,update,delete)在指定的表上发出时,Oracle自动地执行触发器中定义的语句序列。
- 触发器可用于
- 数据确认
- 实施复杂的安全性检查
- 做审计,跟踪表上所做的数据操作等
- 数据的备份和同步
2.类型
触发器的类型
- 语句级触发器
在指定的操作语句操作之前或之后执行一次,不管这条语句影响了多少行 。 - 行级触发器(FOR EACH ROW)
触发语句作用的每一条记录都被触发。在行级触发器中使用:old
和:new
伪记录变量,识别值的状态。
3.创建触发器语法
CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW [WHEN (条件)] ]
PLSQL块
在触发器中阻止SQL继续执行的方法:
抛一个错误,raise_application_error(-20000, '现在是非工作时间,不能插入数据');
错误号码应在-20000到-20999之间(包含,否则提示"错误号参数超出范围")。
4.触发语句与伪记录变量的值
触发语句 | :old | :new |
---|---|---|
insert | 所有字段都是空(null) | 将要插入的数据 |
update | 更新以前该行的值 | 更新后的值 |
delete | 删除以前该行的值 | 所有字段都是空(null) |
5.示例
例,在工作时间9:00~17:00之外,不能插入数据
create or replace trigger mytrigger
before insert
on emp
declare
-- 变量
begin
if not (to_number(to_char(sysdate, 'hh24')) between 9 and 16) then
raise_application_error(-20000, '现在是非工作时间,不能插入数据');
end if;
end;
insert into emp (empno, ename, sal) values (8001, 'test', 2300);
例,确认工资数据,更新工资一定不能比之前的少
create or replace trigger checkSal
before update
on emp
for each row
declare
-- 变量
begin
if :new.sal < :old.sal then
raise_application_error(-20001, '工资不能越涨越少');
end if;
end;
update emp set sal = sal - 1 where empno = 7369;
6.删除触发器
drop trigger mytrigger;
7.查询触发器、存储过程及函数
描述 | 字典表 | 示例 |
---|---|---|
存储过程、存储函数、触发器 | user_source | SELECT * FROM user_source; |
触发器 | user_triggers | SELECT * FROM user_triggers; |