文章目录

1 概述2 触发器管理2.1 创建触发器2.1.1 for each row2.1.2 follows2.1.3 when2.2 查询触发器2.3 删除触发器2.4 常用属性2.4.1 inserting、updating、deleting2.4.2 now、old3 触发器分类3.1 DML 触发器3.1.1 单列触发：of 列名3.2 DDL 触发器3.3 Databse 触发器3.4 instead of 替换触发器

1 概述

1. 触发器是什么？当 '触发条件' 成立时，其语句就会 '自动执行'2. 触发器有什么用？保护数据的安全，监视对数据的各种操作，如'日志记录': 对重要表的 '修改' 进行记录3. 触发器和存储过程的区别？主要区别：'调用运行方式不同'(1) 存储过程: '用户'、'应用程序'、'触发器' 来调用(2) 触发器: '自动执行'（满足 '触发条件'），与其它无关

2 触发器管理

2.1 创建触发器

create [or replace] trigger 触发器名触发时间 {before | after} -- view 中是 instead of触发事件 {insert | update | delete} -- dml、ddl、databaseon 触发对象 -- table、view、schema、database 触发频率 {for each row} -- 行级触发器。默认：语句级触发器[follows 其它触发器名] -- 多个触发器执行的 前后顺序[when 触发条件]beginpl/sql 语句;end;

关键字说明：

1. 触发器名：一般格式 tr_*2. 触发时间：在 '触发事件' 发生之前（before）还是之后（after）3. 触发事件：根据不同的 '触发事件'，可以分为不同的 '类型'4. 触发对象：table、view、schema、database5. 触发频率：'语句级触发器'（默认）指触发一次，'行级触发器' 每一行触发一次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;

特别提醒：在演示某触发器效果时，建议先删除其它触发器，避免影响测试结果

2.1.1 for each row

/*功能：after insert or update or delete 时，执行语句命名：tr_aiud_student_info*/create or replace trigger scott.tr_aiud_student_infoafter insert or update or delete on scott.student_infofor each rowbegincasewhen inserting thendbms_output.put_line('插入成功！');when updating thendbms_output.put_line('更新成功！');when deleting thendbms_output.put_line('删除成功！');elsedbms_output.put_line('无操作！');end case;end;/

测试语句：行级触发器（每行触发一次）

update scott.student_info tset t.sex = '1'where t.sno <= 3;

PL/SQL 输出窗口：3 条记录，故共触发 3 次

更新成功！更新成功！更新成功！

提示：若去掉for each row，再执行上述操作，则仅触发1次

2.1.2 follows

前提：触发器的执行是否需要指定 '先后顺序'？1. 若不需要，则无需 follows 关键字2. 若需要(1) before 和 after 能否区分，若能，则无需 follows 关键字(2) 最后，才用 follows 区分

请注意：测试前，先删除所有触发器，避免影响测试结果

select 'DROP TRIGGER ' || t.owner || '.' || t.trigger_name || ';' 删除from all_triggers twhere t.owner = 'SCOTT'and t.table_name = 'STUDENT_INFO';

触发器1：

create or replace trigger scott.tr_ai_student_info_1after insert on scott.student_infofor each rowbeginif inserting thendbms_output.put_line('插入操作 1');end if;end;/

触发器2：（顺序：先触发器1，再触发器2）

create or replace trigger scott.tr_ai_student_info_2after insert on scott.student_infofor each ROWFOLLOWS scott.tr_ai_student_info_1beginif inserting thendbms_output.put_line('插入操作 2');end if;end;/

测试语句：

insert into scott.student_info(sno, name, sex) values(5, '赵六', '女');

PL/SQL 输出窗口：

插入操作 1插入操作 2

2.1.3 when

1. when：增加触发条件2. when 中的 new、old 是不带 : 的哦（不是 :new、:old）

create or replace trigger scott.tr_ad_student_infoafter delete on scott.student_infofor each rowwhen (old.sno = 1) -- sno = 1 的记录禁止被删除！beginif deleting thenraise_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 触发器名;select 'DROP TRIGGER ' || t.owner || '.' || t.trigger_name || ';' 删除from all_triggers twhere t.owner = 'SCOTT'and t.table_name = 'STUDENT_INFO';

2.4 常用属性

2.4.1 inserting、updating、deleting

1. 前提条件：无2. 表示含义inserting = insert 操作updating = update 操作deleting = delete 操作

效果同 2.1.1 for each row

2.4.2 now、old

1. 前提条件：for each row2. 表示含义: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 值分布情况：

create or replace trigger scott.tr_au_student_infoafter update on scott.student_infofor each rowbegin-- 旧值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 tset t.name = 'name',t.sex = '2'where t.sno = 1;

测试结果：PL/SQL 输出窗口

old.sno = 1old.name = 张三old.sex = 女------new.sno = 1new.name = namenew.sex = 2

3 触发器分类

3.1 DML 触发器

同上述案例，触发事件：insert、update、delete

3.1.1 单列触发：of 列名

1. 上述案例中，均是记录 '所有列' 的变化，若只想关注其中 '几列' 的变化，该如何呢？2. 此时就用到 'of 列名' 子句，多个列用逗号 ',' 隔开即可

create or replace trigger scott.tr_au_student_infoafter update of sno, name on scott.student_infofor each rowbeginif :new.sno <> :old.sno or :new.name <> :old.name thenraise_application_error(-20001,'禁止操作！修改 sno = ' || :new.sno || ', name = ' ||:new.name);end if;end;

测试语句：

update scott.student_info tset t.name = '哈哈'where t.sno = 1;

测试结果：弹框 - 错误提醒

3.2 DDL 触发器

触发事件：create、alter、drop

-- **********************************************************************-- 功能：非 DBA 管理员禁止操作, 如：wangyou--限定符合下列 类型 和 域账户 的人，才能操作 create、alter、drop-- **********************************************************************create or replace trigger scott.tr_dba_controlbefore create or alter or drop on databasedeclarev_user_name varchar2(50); -- 用户名beginselect sys_context('USERENV', 'OS_USER') into v_user_name from dual;if dbms_standard.dictionary_obj_type in('TABLE', 'SYNONYMS', 'USER', 'TABLESPACE') andv_user_name not in ('wangyou') thenraise_application_error(-20000,v_user_name || '用户无 DDL-' || ora_sysevent ||' 权限，请联系数据架构设计处处理！');end if;end;

3.3 Databse 触发器

触发事件startup：'数据库打开'时，相反的 = shutdownlogon ：当用户连接到数据库并 '建立会话' 时，相反的 = logoffservererror：发生服务器错误时

create table scott.database_login_info (client_ipvarchar2(30),login_uservarchar2(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_infoafter logon on databasedeclarev_option_user varchar2(50) := sys_context('USERENV', 'OS_USER'); -- 电脑域账户begininsert 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;

3.4 instead of 替换触发器

1. 只适用于视图（多个简单的基表相连），不能直接作用于表上（间接）2. 很少使用，个人感觉，不如 dml 触发器来得直观3. 必须包含 for each row 选项

create or replace trigger <触发器名称>instead of insert or update or delete on <视图名>for each row -- 必填，且唯一beginpl/sql 语句;end;

如果觉得《Oracle 触发器详解（trigger）》对你有帮助，请点赞、收藏，并留下你的观点哦！