触发器详解
目录：
1 概述
2 触发器管理
2.1 创建触发器
2.1.1 for each row
2.1.2 follows
2.1.3 when
2.2 查询触发器
2.3 删除触发器
2.4 常用属性
2.4.1 inserting、updating、deleting
2.4.2 now、old
3 触发器分类
3.1 DML 触发器
3.1.1 单列触发：of 列名
3.2 DDL 触发器
3.3 Databse 触发器
3.4 instead of 替换触发器
--------------------------------------------------------
正文：
1概述
1.1. 触发器是什么？
当 '触发条件' 成立时，其语句就会 '自动执行'
1.2. 触发器有什么用？
保护数据的安全，监视对数据的各种操作，如
'日志记录': 对重要表的 '修改' 进行记录
1.3. 触发器和存储过程的区别？
主要区别：'调用运行方式不同'
(1) 存储过程: '用户'、'应用程序'、'触发器' 来调用
(2) 触发器: '自动执行'（满足 '触发条件'），与其它无关
2 触发器管理
2.1 创建触发器
CREATE [OR REPLACE] TRIGGER 触发器名 --一般格式 tr_*
{BEFORE | AFTER} --触发时间 view中是instead of
{insert | update | delete} --触发事件 dml，ddl，datebase
[of 列名] --特定列触发
on 触发对象 --table,view,schema,datebase
{for each row} --触发频率 默认为：语句级触发
[follows 其他触发器名] --多个触发器执行的前后顺序
[when 触发条件]
[declare] --可选
变量申明;
begin
pl/sql 语句;
end;
关键字说明：
1. 触发器名：一般格式 tr_*
2. 触发时间：在 '触发事件' 发生之前（before）还是之后（after）
Before 触发器可以防止修改发生, after触发器则为事后校验; 如果是做权限检查,
用BEFORE触发器更为高效，因为可以减少昂贵的回滚处理。
BEFORE 行触发器可以修改:NEW的值，AFTER行触发器不可以。
BEFORE 行触发器看到的不是最终的值，而且如果有多个BEFORE行触发器，
它们的触发是无序的、随机的，在一个触发器中看到的数据可能随后被另一个改动，
所以不能用于数据校验，应该改用AFTER行触发器。
如果你需要修改:NEW的值就用BEFORE行触发器，如果你需要校验:NEW的值就用AFTER行触发器。此外AFTER触发器会产生较少的REDO。
3. 触发事件：根据不同的 '触发事件'，可以分为不同的 '类型' 多个触发事件用 or 连接
4. 触发对象：table、view、schema、database
5. 触发频率：'语句级触发器'（默认）指触发一次，'行级触发器' 每一行触发一次
6. 触发条件：仅当 '触发条件' 为 True 时，才执行 pl/sql 语句
7. 条件判断：inserting,updating,deleting 对应相应的事件，返回值为bool值
基础数据准备：
create table scott.student_info (
sno number(10),
name varchar2(30),
sex varchar2(4)
);
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;
select * from student_info;
2.1.1 for each row 行级触发器：每一行数据变化都执行一次触发器
语句级触发器：即不加 for each row 执行一条事件语句执行一次，不管有多少条数据变化
测试代码：--创建一个触发器
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;
insert into scott.student_info(sno, name, sex) values(4, '麻子', '女');
update scott.student_info t set t.sex='1' where t.sno>4; --不会执行触发器
2.1.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;
触发器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, '赵六', '女');
1--插入操作 1
2--插入操作 2
2.1.3 when 增加触发条件 --when中的 new,old是不带:的（不是 :new,:old）
create or replace trigger scott.tr_ad_student_info
before 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;
测试结果：弹框 - 错误提醒
2.2 查询触发器
权限范围，由大到小：dba_* > all_* > user_*
select * from dba_triggers;
select * from all_triggers;
select * from user_triggers;
2.3 删除触发器
drop trigger 触发器名;
2.4 常用属性
2.4.1 inserting、updating、deleting
1. 前提条件：无
2. 表示含义
inserting = insert 操作
updating = update 操作
deleting = delete 操作
2.4.2 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 update delete
:new √ √ ×
: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;
3.触发器分类
3.1 DML 触发器
同上述案例，触发事件：insert、update、delete
3.1.1 单列触发：of 列名
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; --报错
3.2 instead of 替换触发器
1. 只适用于视图（多个简单的基表相连），不能直接作用于表上（间接）
2. 很少使用，个人感觉，不如 dml 触发器来得直观
3. 必须包含 for each row 选项
create or replace trigger <触发器名称>
instead of insert or update or delete on <视图名>
for each row -- 必填，且唯一
begin
pl/sql 语句;
end;
3.3 DDL 触发器
触发事件：create、alter、drop
-- **********************************************************************
-- 功能：非 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;
3.4 Databse 触发器
触发事件
startup：'数据库打开'时，相反的 = shutdown
logon ：当用户连接到数据库并 '建立会话' 时，相反的 = logoff
servererror：发生服务器错误时
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
);
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;
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
