文章目录
触发器
CREAETE TRIGGER trigger_name #创建触发器名称
BEFORE/AFTER DELETE [UDPATE,INSERT,SHUTDOWN······] #触发时机【触发事件】
ON object_name #触发器操作对象【表或数据库】
Trigger_SQL_PL/SQL_body #触发器主题代码逻辑
1. 触发器的创建
EX:
创建一个触发器用于实现 SCOTT模式下对表EMP的操作记录,当用户有删除操作时,在标准输出装置中打印一条信息
SQL> list
1 create or replace trigger trigger_emp_trigger
2 before delete
3 on emp
4 for each row
5 begin
6 dbms_output.put_line('deleting....');
7* end delete_emp_trigger;
SQL> /
触发器已创建
SQL> select object_name,object_type,created,status
2 from user_objects
3 where object_type='TRIGGER';
#STATUS为VALID表示该触发器有效
OBJECT_NAME OBJECT_TYPE CREATED STATUS
-------------------- ------------------- -------------- -------
TRIGGER_EMP_TRIGGER TRIGGER 11-3月 -20 VALID
SQL> delete from emp where ename='FORD';
deleting....
已删除 1 行。
2. 触发器的分类
2.1 基于DML操作的触发器
语法格式:
CREATE [ OR REPLACE] TRIGGER trigger_name [BEFORE|AFTER] #触发时机
[INSERT|UPDATE|DELETE] ON table_name [FOR EACH ROW [WHEN cond]] #更具体的操作
Trigger_SQL_Statements;
2.2 基于DDL操作的触发器
例如 CREATE|AFTER|DROP
语法格式:
CREATE [ OR REPLACE] TRIGGER trigger_name [BEFORE|AFTER]
[CREATE|AFTER|DROP] ON database_name [WHEN cond]] #更具体的操作
Trigger_SQL_Statements;
2.3 基于数据库级操作的触发器
例如 START|SHUTDOWN|LOGON|LOGOFF
语法格式:
CREATE [ OR REPLACE] TRIGGER trigger_name [BEFORE|AFTER]
[START|SHUTDOWN|LOGON|LOGOFF] ON database_name [WHEN cond]] #更具体的操作
Trigger_SQL_Statements;
3. 触发器的权限
- 用户创建触发器的权限为
CREATE TRIGGER
,DBA用户通过GRANT CREATE TRIGGER TO user_name
赋予当前用户 - 当前用户创建其他用户的触发器,需要
CREATE ANY TRIGGER
权限 - 创建的触发器作用于数据库,例如
START
\SHUTDOWN
,需要ADMINISTER DATABASE TRIGGER
系统权限
SYS用户下查看用户SCOTT的系统权限
SQL> select *
2 from dba_sys_privs
3 where grantee ='SCOTT';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO
授权用户SCOTT可在自己的模式下创建触发器
SQL> GRANT create trigger to SCOTT;
授权成功。
授权用户SCOTT在任何模式下创建触发器
SQL> GRANT create ANY trigger to SCOTT;
授权成功。
通过数据字典DBA_SYS_PRIVES查看用户SCOTT的系统权限
SQL> col grantee for a15
SQL> col privilege for a20
SQL> col admin_option for a15
SQL> select *
2 from dba_sys_privs
3 where grantee = 'SCOTT'
4 and privilege like 'CREATE%'
5 /
GRANTEE PRIVILEGE ADMIN_OPTION
--------------- -------------------- ---------------
SCOTT CREATE TRIGGER NO
SCOTT CREATE ANY TRIGGER NO
Tips:
ADMIN_OPTION
为NO即说明对应的权力不能再赋予其他用户
4. 触发器的新值和旧值
Oracle提供两个临时表来访问每行中的新值和旧值,即:new
和:old
EX:
创建一个触发器,用于在更新SCOTT的EMP表中记录后,将更新的行的sal值打印到标准输出装置
create or replace trigger update_emp_trigger
after update on emp
for each row
begin
dbms_output.put_line('old value is '||:old.sal);
dbms_output.put_line('new value is '||:new.sal);
end update_emp_trigger;
查看触发器信息并更新EMP表
SQL> select object_name,object_type,created,status
2 from user_objects
3 where object_type = 'TRIGGER'
4 and status = 'VALID'; #'VALID'表示可用
OBJECT_NAME OBJECT_TYPE CREATED STATUS
-------------------- ------------------- -------------- -------
TRIGGER_EMP_TRIGGER TRIGGER 11-3月 -20 VALID
UPDATE_EMP_TRIGGER TRIGGER 11-3月 -20 VALID
SQL> set serveroutput on;
SQL> update emp
2 set sal = sal+200-200
3 where job='CLERK';
old value is 800 # :old.sal
new value is 800 # :new.sal
old value is 1100
new value is 1100
old value is 950
new value is 950
old value is 1300
new value is 1300
已更新4行。
5. 审核触发器的创建
EX:
创建一个表记录,当用户插入和更新数据时,能够记录该用户的名称和更改时间
以下触发器创建包含IF条件语句示例
SQL> create table user_modify_table
2 (username varchar2(20),
3 modifytime date,
4 content varchar2(20));
表已创建。
SQL> desc user_modify_table;
名称 是否为空? 类型
---------------------------------------------
USNAME VARCHAR2(20) MODIFYTIME
DATE CONTENT VARCHAR2(20)
#已在e:user_change_empdata.sql写好触发器创建语句,直接读取脚本
SQL> GET e:user_change_empdata.sql
1 CREATE OR REPLACE TRIGGER user_change_empdata
2 BEFORE update or insert ON emp
3 FOR EACH ROW
4 BEGIN
5 IF inserting THEN #不同触发事件用IF条件语句来判断
6 INSERT INTO user_modify_table
7 VALUES (user,sysdate,'inserting');
8 END IF;
9 IF updating THEN
10 INSERT INTO user_modify_table
11 VALUES (user,sysdate,'updating');
12 END IF;
13* END user_change_empdata;
#运行脚本
SQL> @e:user_change_empdata.sql
14 /
触发器已创建
#查看创建的触发器
SQL> select object_name,object_id,object_type,created,status
2 from user_objects
3 where object_type = 'TRIGGER'
4 and object_name like 'USER%';
OBJECT_NAME OBJECT_ID OBJECT_TYPE CREATED STATUS
-------------------- ---------- ------------------- -------------- -------
USER_CHANGE_EMPDATA 73522 TRIGGER 11-3月 -20 VALID
SQL> insert into emp (empno,ename,sal,job)
2 values (7879,'tom',10000,'MANAGER');
已创建 1 行。
SQL> update emp
2 set sal = sal + 200
3 where job='CLERK'; #这里是之前的触发器没有禁用导致的,可以用'ALTER trigger update_emp_trigger disable'禁用该触发器
old value is 800
new value is 1000
old value is 1100
new value is 1300
old value is 950
new value is 1150
old value is 1300
new value is 1500
已更新4行。
SQL> select * from USER_MODIFY_TABLE;
USERNAME MODIFYTIME CONTENT
-------------------- -------------- --------------------
SCOTT 11-3月 -20 inserting
SCOTT 11-3月 -20 updating
SCOTT 11-3月 -20 updating
SCOTT 11-3月 -20 updating
SCOTT 11-3月 -20 updating
6. 删除触发器的创建
EX:
创建一个记录删除数据的表,表结构与EMP表的结构对应,用删除触发器记录删除操作时的内容
SQL> create table backup_delete_emp_table
2 (backempno number(4),
3 backname varchar2(10),
4 backjob varchar2(9),
5 backmgr number(4),
6 backhiredate date,
7 backsal number(7,2),
8 backcomm number(7,2),
9 backdeptno number(2));
表已创建。
SQL> get @e:backup_emp_trigger
1 create or replace trigger backup_emp_trigger
2 before delete on emp_test
3 for each row
4 begin
5 insert into backup_delete_emp_table
6 values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,
7 :old.sal,:old.comm,:old.deptno);
8* end back_emp_trigger;
SQL> /
触发器已创建
SQL> delete from emp_test
2 where ename = 'TOM';
已删除0行。
SQL> delete from emp_test
2 where ename = 'SCOTT';
deleting....
已删除 1 行。
SQL> SELECT * FROM backup_delete_emp_table;
#ename = 'TOM'没有执行成功,所以表格backup_delete_emp_table没有记录
BACKEMPNO BACKNAME BACKJOB BACKMGR BACKHIREDATE BACKSAL BACKCOMM BACKDEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7. 触发器的条件语句
- WHEN子句
- IF 子句
7.1 WHEN子句
设置基于行级的触发条件
语法结构:
CREAETE OR REPLACE TRIGGER when_emp_trigger
BEFOR UPDATE OR INSERT ON emp
FOR EACH ROW
WHEN (condition) #WHEN子句后无分号;
BEGIN
Trigger_body;
END when_emp_trigger;
EX:
对于删除表EMP中的一行记录时,先判断JOB是否为MANAGER 或者 PRESIDENT,是则备份。
SQL> create or replace trigger when_backup_emp_trigger
2 before delete on emp
3 for each row
4 when (old.job in 'MANAGER,PRESIDENT')
#WHEN子句中的new或old都不使用:old或:new形式,且字句后没有分号
5 BEGIN
6 INSERT INTO backup_delete_emp_table
7 VALUES (:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,
8 :old.sal,:old.comm,:old.deptno);
9 END when_backup_emp_trigger;
10
11 /
7.2 IF子句
用条件判断来实现一个触发器相应多个触发事件
语句格式:
IF [INSERT | UPDATE| DELETE] THEN
trigger_body;
END IF;
点击跳转到IF条件语句示例
8. 触发器的管理
8.1 查看触发器
USER_TRIGGERS数据字典可查看当前用户的触发器信息
EX:
查看触发器BACKUP_EMP_TRIGGER的信息
SQL> set line 120
SQL> col status for a15
SQL> col triggering_event for a15
SQL> col description for a25
SQL> select trigger_name,triggering_event,description,status
2 from user_triggers
3 where trigger_name = 'BACKUP_EMP_TRIGGER'
4 /
TRIGGER_NAME TRIGGERING_EVEN DESCRIPTION STATUS
------------------------------ --------------- ------------------------- ---------------
BACKUP_EMP_TRIGGER DELETE backup_emp_trigger ENABLED
before delete on emp_test
for each row
8.2 重新编译触发器
参考《管理触发器》
当使用ALTER TABLE命令修改表的结构(例如增加列,删除列)时,会使得其触发器转变为INVALID状态。
在这种情况下,为了使得触发器继续生效,需要重新编译触发器。
手动编译触发器如下:
ALTER TRIGGER trigger_name COMPILE;
8.3 屏蔽和激活触发器
禁止触发器是指是使触发器临时失效。当触发器处于ENABLED状态时,如果在表上执行DML操作,则就会触发相应的触发器。
如果基于INSERT操作建立了触发器,当使用SQL*Loader装载大批量数据时会触发触发器。
为了加快数据装载速度,应该在装载数据之前禁止触发器。
EX:
SQL> alter trigger backup_emp_trigger disable;
触发器已更改
SQL> get @e:trigger_status.sql
1 select trigger_name,triggering_event,description,status,trigger_body
2 from user_triggers
3* where trigger_name = 'BACKUP_EMP_TRIGGER'
SQL> /
TRIGGER_NAME TRIGGERING_EVEN DESCRIPTION STATUS TRIGGER_BODY
------------------------------ --------------- ------------------------- --------------- -------------------------
BACKUP_EMP_TRIGGER DELETE backup_emp_trigger DISABLED begin
before delete on emp_test insert into backup_delete
_emp_table
for each row values(:old.empno,:old.en
ame,:old.job,
SQL> alter trigger backup_emp_trigger enable;
触发器已更改
SQL> get @e:trigger_status.sql
1 select trigger_name,triggering_event,description,status,trigger_body
2 from user_triggers
3* where trigger_name = 'BACKUP_EMP_TRIGGER'
SQL> /
TRIGGER_NAME TRIGGERING_EVEN DESCRIPTION STATUS TRIGGER_BODY
------------------------------ --------------- ------------------------- --------------- -------------------------
BACKUP_EMP_TRIGGER DELETE backup_emp_trigger ENABLED begin
before delete on emp_test insert into backup_delete
_emp_table
for each row values(:old.empno,:old.en
ame,:old.job,
8.3 删除触发器
DROP TRIGGER trigger_name
练习
有一个员工表和部门表,如下所示:
create table t_emp(
empid number(2) primary key,
empname varchar2(20),
deptid number(2)
);
create table t_dept(
deptid number(2) primary key,
deptname varchar2(20)
);
insert into t_emp values(1,'jason01',10);
insert into t_emp values(2,'jason02',20);
insert into t_emp values(3,'jason03',30);
insert into t_emp values(4,'jason04',40);
insert into t_dept values(10,'软件部');
insert into t_dept values(20,'销售部');
insert into t_dept values(30,'工程部');
insert into t_dept values(40,'财务部');
- 创建一个触发器,在删除一个部门记录后,删除该部门下所有员工的记录
create or replace trigger del_dept
after delete on t_dept for each row
begin
delete from t_emp where deptid = :old.deptid;
end;
- 创建一个触发器,在增加一个部门记录后,在员工表中增加一条在这个部门下的员工
create or replace trigger add_dept
after insert on t_dept for each row
begin
insert into t_emp values (5,'jason06',:old.deptid);
end;
- 创建触发器,在更新一个部门编号后,更新该部门下所有员工的编号
create or replace trigger upd_dept
after update on t_dept for each row
begin
update t_emp set deptid=:new.deptid where deptid=:old.deptid;
end;
- 创建触发器,如果删除的员工的empid为1时,那么不允许删除,而其他员工可以删除
create or replace trigger del_emp
before delete on t_emp for each row
begin
if :old.empid =1 then raise_application_error(-2000,'不允许删除该记录');
end if;
end;