《trigger篇》
原文链接：https://blog.csdn.net/m0_37253968/article/details/120326922
概述
触发器，当 '触发条件' 成立时，其语句就会 '自动执行'。
触发器有什么用？ 保护数据的安全，监视对数据的各种操作，如'日志记录': 对重要表的 '修改' 进行记录。
触发器
创建语句
create [or replace] trigger 触发器名
触发时间 {before | after} -- view 中是 instead of
触发事件 {insert | update | delete} -- dml、ddl、database
on 触发对象 -- table、view、schema、database
触发频率 {for each row} -- 行级触发器。默认：语句级触发器
[follows 其它触发器名] -- 多个触发器执行的 前后顺序
[when 触发条件]
begin
pl/sql 语句;
end;
关键词说明：
1. 触发器名：一般格式 tr_*
2. 触发时间：在 '触发事件' 发生之前（before）还是之后（after）
3. 触发事件：根据不同的 '触发事件'，可以分为不同的 '类型'
4. 触发对象：table、view、schema、database
5. 触发频率：'语句级触发器'（默认）指触发一次，'行级触发器' 每一行触发一次
6. 触发条件：仅当 '触发条件' 为 True 时，才执行 pl/sql 语句
基础数据准备：
create table scott.student_info (
sno number(10),
name varchar2(30),
sex varchar2(2)
);
insert into scott.student_info(sno, name, sex) values(1, '张三', '女');
insert into scott.student_info(sno, name, sex) values(2, '李四', '男');
insert into scott.student_info(sno, name, sex) values(3, '王五', '女');
commit;
for each row
多次执行
提示：若去掉 for each row，则仅触发 1 次
/*
功能：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;
PL/SQL 输出窗口：3 条记录，故共触发 3 次
更新成功！
更新成功！
更新成功！
follows
前提：触发器间的执行是否需要指定 '先后顺序'？
若不需要，则无需 follows 关键字
若需要
(1) before 和 after 能否区分，若能，则无需 follows 关键字
(2) 最后，才用 follows 区分
请注意：测试前，先删除所有触发器，避免影响测试结果
select 'DROP TRIGGER ' || t.owner || '.' || t.trigger_name || ';' 删除
from all_triggers t
where t.owner = 'SCOTT'
and t.table_name = 'STUDENT_INFO';
触发器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;
/
触发器2：（顺序：先触发器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;
/
测试语句：
insert into scott.student_info(sno, name, sex) values(5, '赵六', '女');
PL/SQL 输出窗口：
插入操作 1
插入操作 2
when
when：增加触发条件
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
表示含义
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
:new、:old 值分布情况：
insert：有:new，无:old
update：有:new，有:old
delete：无:old，有:old
0
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;
测试结果：PL/SQL 输出窗口
old.sno = 1
old.name = 张三
old.sex = 女
------
new.sno = 1
new.name = name
new.sex = 2
触发器分类
DML 触发器
同上述案例，触发事件：insert、update、delete
单列触发器
--1. 上述案例中，均是记录 '所有列' 的变化，若只想关注其中 '几列' 的变化，该如何呢？
--2. 此时就用到 'of 列名' 子句，多个列用逗号 ',' 隔开即可
create or replace trigger scott.tr_au_student_info
after update of sno, name on scott.student_info
for each row
begin
if :new.sno <> :old.sno or :new.name <> :old.name then
raise_application_error(-20001,
'禁止操作！修改 sno = ' || :new.sno || ', name = ' ||
:new.name);
end if;
end;
测试语句：
update scott.student_info t
set t.name = '哈哈'
where t.sno = 1;
测试结果：
弹框 - 错误提醒
DDL 触发器
触发事件：create、alter、drop
0
-- 功能：非 DBA 管理员禁止操作, 如：wangyou
-- 限定符合下列 类型 和 域账户 的人，才能操作 create、alter、drop
create or replace trigger scott.tr_dba_control
before create or alter or drop on database
declare
v_user_name varchar2(50); -- 用户名
begin
select sys_context('USERENV', 'OS_USER') into v_user_name from dual;
if dbms_standard.dictionary_obj_type in
('TABLE', 'SYNONYMS', 'USER', 'TABLESPACE') and
v_user_name not in ('wangyou') then
raise_application_error(-20000,
v_user_name || '用户无 DDL-' || ora_sysevent ||
' 权限，请联系数据架构设计处处理！');
end if;
end;
Database 触发器
触发事件
startup：'数据库打开'时，相反的 = shutdown
logon ：当用户连接到数据库并 '建立会话' 时，相反的 = logoff
servererror：发生服务器错误时
0
create table scott.database_login_info (
client_ip varchar2(30),
login_user varchar2(30),
database_name varchar2(30),
database_event varchar2(30),
create_user varchar2(50),
create_data date
);
0
create or replace trigger scott.tr_al_database_login_info
after logon on database
declare
v_option_user varchar2(50) := sys_context('USERENV', 'OS_USER'); -- 电脑域账户
begin
insert into scott.database_login_info
(client_ip,
login_user,
database_name,
database_event,
create_user,
create_data)
values
(dbms_standard.client_ip_address,
dbms_standard.login_user,
dbms_standard.database_name,
dbms_standard.sysevent,
v_option_user,
sysdate);
end;
instead of 替换触发器
1. 只适用于视图（多个简单的基表相连），不能直接作用于表上（间接）
2. 很少使用，个人感觉，不如 dml 触发器来得直观
3. 必须包含 for each row 选项
0
create or replace trigger <触发器名称>
instead of insert or update or delete on <视图名>
for each row -- 必填，且唯一
begin
pl/sql 语句;
end;
