Oracle触发器
触发器分类:DML触发器、DDL触发器、Databse触发器、instead of 替代触发器(参考2有详解)
作用: 保护数据的安全,监视对数据的各种操作,如'日志记录': 对重要表的 '修改' 进行记录
例如:当员工信息插入后,自动输出“插入成功”的信息。
create or replace trigger empTrigger after insert on emp for each row declare -- 这里存放本地变量 begin dbms_output.put_line('插入成功!'); end empTrigger;
1、语法:
create [or replace] trigger 触发器名(tr_) 触发时间 {before | after} -- view 中是 instead of 触发事件 {insert | update | delete} -- dml、ddl、database on 触发对象 -- table、view、schema、database 触发频率 {for each row} -- 行级触发器。默认:语句级触发器,指触发一次 [follows 其它触发器名] -- 多个触发器执行的 前后顺序 [when 触发条件] --仅当 '触发条件' 为 True 时,才执行 pl/sql 语句 begin pl/sql 语句; end;
/* 功能:after insert or update or delete 时,执行语句 命名:tr_aiud_student_info */ create or replace trigger scott.tr_aiud_student_info after insert or update or delete
on scott.student_info for each row begin case when inserting then dbms_output.put_line('插入成功!'); when updating then dbms_output.put_line('更新成功!'); when deleting then dbms_output.put_line('删除成功!'); else dbms_output.put_line('无操作!'); end case; end; --测试 update scott.student_info t set t.sex = '1' where t.sno <= 3;
2、一些关键字的使用:
follows:
前提:触发器的执行是否需要指定 '先后顺序'?
1. 若不需要,则无需 follows 关键字
2. 若需要
(1) before 和 after 能否区分,若能,则无需 follows 关键字
(2) 最后,才用 follows 区分
--触发器1 create or replace trigger scott.tr_ai_student_info_1 after insert on scott.student_info for each row begin if inserting then dbms_output.put_line('插入操作 1'); end if; end; --(顺序:先触发器1,再触发器2) create or replace trigger scott.tr_ai_student_info_2 after insert on scott.student_info for each ROW FOLLOWS scott.tr_ai_student_info_1 begin if inserting then dbms_output.put_line('插入操作 2'); end if; end;
when:
1. when:增加触发条件
2. when 中的 new、old 是不带 : 的哦(不是 :new、:old)
create or replace trigger scott.tr_ad_student_info after delete on scott.student_info for each row when (old.sno = 1) --sno = 1 的记录禁止被删除! begin if deleting then raise_application_error(-20001, '此条记录禁止删除,sno = ' || :old.sno); end if; end; / --测试 delete from scott.student_info t where t.sno = 1;
inserting、updating、deleting:
1. 前提条件:无 2. 表示含义 inserting = insert 操作 updating = update 操作 deleting = delete 操作
now、old:
1. 前提条件:for each row 2. 表示含义 :new = 触发后的值 :old = 触发前的值 3. 说明 (1) new、old 均为 '默认值', 常用, 一般无需更改 referencing new as new old as old (2) 若想要更改,如:new => new_new,old => old_old referencing new as new_new old as old_old
create or replace trigger scott.tr_au_student_info after update on scott.student_info for each row begin -- 旧值 dbms_output.put_line('old.sno = ' || :old.sno); dbms_output.put_line('old.name = ' || :old.name); dbms_output.put_line('old.sex = ' || :old.sex); dbms_output.put_line('------'); -- 新值 dbms_output.put_line('new.sno = ' || :new.sno); dbms_output.put_line('new.name = ' || :new.name); dbms_output.put_line('new.sex = ' || :new.sex); end; / --测试 update scott.student_info t set t.name = 'name', t.sex = '2' where t.sno = 1; --输出窗口 old.sno = 1 old.name = 张三 old.sex = 女 ------ new.sno = 1 new.name = name new.sex = 2
3、DML触发器基本要点
(1)触发时机:指定触发器的触发时间。如果指定为BEFORE,则表示在执行DML操作之前触发,以便防止某些错误操作发生或实现某些业务规则;如果指定为AFTER,则表示在执行DML操作之后触发,以便记录该操作或做某些事后处理。
(2)触发事件:引起触发器被触发的事件,即DML操作(INSERT、UPDATE、DELETE)。既可以是单个触发事件,也可以是多个触发事件的组合(只能使用OR逻辑组合,不能使用AND逻辑组合)。
(3)条件谓词:当在触发器中包含多个触发事件(INSERT、UPDATE、DELETE)的组合时,为了分别针对不同的事件进行不同的处理,需要使用ORACLE提供的如下条件谓词。
(a).INSERTING:当触发事件是INSERT时,取值为TRUE,否则为FALSE。
(b).UPDATING [(column_1,column_2,…,column_x)]:当触发事件是UPDATE时,如果修改了column_x列,则取值为TRUE,否则为FALSE。其中column_x是可选的。
(c).DELETING:当触发事件是DELETE时,则取值为TRUE,否则为FALSE。
(4)解发对象:指定触发器是创建在哪个表、视图上。
(5)触发类型:是语句级还是行级触发器。
(6)触发条件:由WHEN子句指定一个逻辑表达式,只允许在行级触发器上指定触发条件,指定UPDATING后面的列的列表。
查询触发器
show triggers;
删除触发器
drop trigger 触发器名;
4、触发器实例:
1)禁止在非工作时间插入数据。
create or replace trigger addEmpInfoCheck before insert on emp_info declare begin if to_char(sysdate, 'day') in ('星期六', '星期日') or to_number(to_char(sysdate, 'hh24')) not between 9 and 18 then --禁止insert raise_application_error(-20001,'非工作时间禁止插入数据!'); end if; end addEmpInfoCheck;
raise_application_error用于在plsql使用程序中自定义不正确消息。该异常只在数据库端的子程序(流程、函数、包、触发器)中运用,而无法在匿名块和客户端的子程序中运用。语法为raise_application_error(error_number,message[,[truefalse]])。其中error_number用于定义不正确号,该不正确号必须在-20000到-20999之间的负整数;message用于指定不正确消息,并且该消息的长度无法超过2048字节。
2)涨薪后的工资应该大于涨薪前的工资。
create or replace trigger checkSalary before update on salary_info for each row declare --没有变量声明的话,declare可以省略 begin if :new.sal < :old.sal then raise_application_error(-20002,'涨后的薪水:'|| :new.sal ||'小于涨前的薪水:'||:old.sal); end if; end checkSalary;
(3)创建基于值的触发器
create table xzw_test(info varchar2(256)); create or replace trigger addData after update on xzw_test for each row declare begin if :new.sal > 6000 then insert into xzw_test values(:new.sal ||'-'|| :new.username ||'-'|| :new.job); end if; end addData;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤