触发器
概述:数据库触发器是一个与表相关联的、存储的PL/SQL程序。触发时机--每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时(事件启动),Oracle自动地执行触发器中定义的语句序列。
触发器 作用(应用场景)(1)数据确认
(2)实施复杂的 安全性检查
(3)做 日志记录,跟踪表上所做的数据操作等
(4)数据的备份和同步;
语法:
CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名 -- 对哪个表进行操作
[FOR EACH ROW [WHEN(条件) ] ]
declare
-- 定义语句
begin
-- PLSQL 块
end ;
触发器的类型(常见)
语句级触发器 :在指定的操作语句操作之前或之后执行一次,不管这条语句影响了多少行 。
行级触发器(for each row) :触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量, 识别值的状态。
需求1:当我往emp表中插入一条数据后,打印语一句话
创建触发器(测试窗口)
-- 说明:创建一个简单的触发器,绑定哪个表的哪些操作
create OR REPLACE trigger mytri after insert on mytable
DECLARE
-- 可以省略
BEGIN
--注意:如果没输出 记得打开屏幕输出开关set serveroutput on;
--监听:插入的操作(给予提示)
dbms_output.put_line('你插入了一个员工');
end mytri; --表示触发器创建完毕(一定要带上表名)
执行(sql窗口)
--(1)创建一个表
CREATE TABLE mytable(nb NUMBER,st VARCHAR(20));
-- DROP TABLE mytable;
--说明:在触发器创建以后,再插入数据
insert into mytable values(1000,'abc3'); --当我插入数据后,就会触发,触发器
SELECT *FROM mytable;
需求2:公司禁止在非工工作时间,往数据库里面插入数据
分析:首先我们得判断出什么是非工作时间,举例如下:
to_char(sysdate,'day') in ('星期六','星期日'); --这个是非工作时间
to_number(to_char(sysdate,'hh24')) not between 9 and 17; --不在9到17点之间都是非上班时间,not between and 不在什么之间
说明:按照某种格式转换成字符串。
然后下面我们写触发器,因为我们不用监听每插入一行数据就触发一次,只要对表进行操作,就触发那我们就用语句级触发器。
触发器
create or replace TRIGGER mytesttri before insert on mytable
declare
begin
-- 判断如果是非工作时间对表操作,就抛出异常,给出提示
if to_char(sysdate,'day') in ('星期六','星期日') OR
-- 周天24h的格式
to_number(to_char(sysdate,'hh24')) not between 9 and 17
then
-- 禁止对表操做,我们可以调用一个系统提供的函数,进行提示
-- 参1 固定的错误码 参数2 提示信息
Raise_application_error(-20001,'非工作时间禁止操作');
end if;
end;
执行
--创建一个表
CREATE TABLE mytable(nb NUMBER,st VARCHAR(20));
DROP TABLE mytable;
--准备插入数据
insert into mytable values(1000,'abc3'); --当我插入数据后,就会触发,触发器
SELECT *FROM mytable;
结果:数据没有插入进去。
第 1 行出现错误:
ORA-20000: 非工作时间禁止操作
ORA-06512: 在 "SCOTT.MYTRIGGER", line 3
ORA-04088: 触发器 'SCOTT.MYTRIGGER' 执行过程中出错
需求3:给员工涨后的工资
分析:涨工资不能小于涨前的工资,,也就是说在update工资的时候只能加不能减;同时因为涨工资是对每一个人都要操作,所以也就是每一行记录都有影响,所以我们就要用行级触发器(FOR EACH ROW), 每操作一行就触发一次。
触发器
create or replace trigger mytrigger2 BEFORE update on emp for each row
begin
--if 涨后的薪水 < 涨前的薪水 then
-- :new表示影响后的新数据,:old表示影响之前的旧数据
if :new.sal < :old.sal then
-- 注意错误码不要跟上面一个触发器例子的错误码重复了
raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水.涨前:'||:old.sal||' 涨后:'||:new.sal);
end if;
end;
测试
-- 测试一下 我们减一下工资,就会触发 我们写的触发器
update emp set sal=sal-1; --但我更新工资时 就会报错
未完待续。。。