oracle 触发器管理（以及行级触发器中有两个伪变量 :new 与 :old 的使用方法）（三个谓词inserting、deleting、updating的使用方法）

1、概念

触发器定义与数据库操作有关的某个事件发生时数据库将要执行的操作

简单说：触发器是在特定事件出现的时候，自动执行的代码块

触发器存放在数据库内，<font color="red"也是命名的PL/SQL块>

一种特殊的存储过程，与存储过程的区别：触发器的执行是由事件触发的，而普通存储过程是由命令调用执行的。其执行过程是隐式的。另一个区别：触发器是没有参数的。

2、建立一个简单的触发器：

create or replace trigger emptrig after insert on emp
begin
	dbms.output.put_line('插入成功了哦，哈哈哈');
end;

关键字：trigger、after、insert、on、begin、end

当我们在emp插入记录成功后，就会执行所编写的触发器，输出内容。

验证：insert into emp(empno,ename) values(9999,‘aaaa’);
插入成功后：就会在脚本输出框中提示：插入成功了哦，哈哈哈
因为在emp表插入后after insert on emp，会执行触发器中的操作。

3、触发器的作用

1、安全性。
如：基于时间限制用户的操作
如：基于数据限制用户的操作（涨工资、股票）

2、审计
跟踪用户对数据库的操作（日志）

3、实现复杂的数据完整性规则（约束）

4、实现数据的备份和同步
（几乎所有的事件都会有备份，可以通过触发器来实现）

5、触发器会造成DML性能的下降
因为每一次插入的时候都会调用触发器，这就很麻烦。

4、触发条件

a、触发事件

触发事件（类型）

1、DML语句事件，执行insert、update、delete等语句时触发的事件

2、DDL语句事件，执行create、alter、drop语句时触发的事件

3、系统错误，当oracle数据库系统出现错误时触发的事件

4、数据库事件，startup、shutdown、logon、logoff、servererror

b、触发时间

Before在指定的事件发生之前执行触发器
After在指定的事件发生之后执行触发器

c、触发级别

行触发：对触发事件影响的每一行执行触发器
语句触发：对于触发事件只能触发一次，而且不能访问受触发器影响的每一行的值

d、触发限制

1．数据库及应用程序的维护困难
2. 占用服务器端太多的资源，对服务器造成很大的压力
3、不能做DDL。
4、触发器排错困难，而且数据容易造成不一致，后期维护不方便。

e、触发对象

触发对象表示是建立在表上的还是视图上的还是索引上的等等。

5、创建及使用触发器

创建：

Create or replace trigger <触发器名>
[before|after]
<触发事件> on <表名>
[for each row]
[when <条件表达式>]
<PL/SQL程序体>

for each row 表示对行设置触发器，每一行记录都会触发触发器，如果不写就是对一个表设置触发器。

6、触发器分类

1、DML触发器
  a、语句级触发器
  b、行级触发器

2、替代触发器（instead of）

3、事件触发器
  a、DDL事件
  b、数据库事件

7、应用案例：实施安全性检查

a、禁止在非工作时间插入新员工

实施安全性检查：

禁止在非工作时间插入新员工，这是基于时间限制用户的操作
分析：触发事件？触发时间？触发器级别？

答：insert DML语句事件 、插入之前触发、针对的是操作，所以是表级触发器

create or replace trigger trigemp2 
before insert
on emp
declare 
	xingqi varchar2(10);
begin
	xingqi := to_char(sysdate,'day');
	if xingqi in('星期日','星期六') then     --今天的时间是星期四
		raise_application_error(-20001,'周末不允许插入');    -- 20000~29999
	end if;
end;


--验证：
insert into emp(empno,ename) values(6666,'bbbb');

提示刚才触发器内容：插入成功了哦，哈哈哈
但是刚刚写的触发器没有触发，因为周四不在周末范围内，所以周四可以插入新员工。

更改上面的代码：

create or replace trigger trigemp2 
before insert
on emp
for each row
declare 
	xingqi varchar2(10);
begin
	xingqi := to_char(sysdate,'day');
	if xingqi in('星期四','星期六') then     --今天的时间是星期四
		raise_application_error(-20001,'周末不允许插入');    -- 20000~29999
	end if;
end;


--验证：
insert into emp(empno,ename) values(6666,'bbbb');

提示：周末不允许插入错误

这就发生了刚刚定义的组织的触发器，这是因为我们将周四设置不让插入新员工，因为今天是周四，所以周四插入就触发了触发器。

b、创建一个触发器，只允许每个月的10号才能入职和离职。

create or replace trigger triemp
before insert or delete
on emp
declare
	v_day  varchar2(2);
begin
	v_day:=to_char(sysdate,'dd');   --时间用 'hh24' 表示
	if v_day <> '10' then
		raise_application_error(-20002,'不是10号，不能入职和离职');
	end if;
end;

8、应用案例：数据完整性检查

a、限制雇员工资的调整，只允许提高，不能降低。

数据完整性检查：
例： 限制雇员工资的调整，只允许提高，不能降低。
分析：

触发事件？	触发时间？	触发级别？	触发对象
update事件	更改之前(before)	行级（不是针对于操作，而是针对的员工）	emp表

create or replace trigger upsal before update on emp for each row
begin
	if :old.sal>:new.sal then
		dbms_output.put_line('涨后工资：' || :old.sal || '        涨前工资' || :new.sal);
		raise_application_error(-20003,'涨后工资要大于涨前的工资');
	end if;
end;

验证：

update emp set sal=200 where empno=7369;

此时就提示错误，触发触发器啦。

b、行级触发器中有两个伪变量 :new 与 :old 的使用方法

行级触发器中有两个伪变量，用于识别值的状态
:old 操作之前，是记录变量，使用形式：:old.字段名
:new 操作之后，是记录变量，使用形式：:new.字段名
只在DML触发表中字段时才有效，只能在触发器内部使用

9、应用案例：数据库的审计：

一般做审计的操作我们都会先建一个表。

a、当员工工资超过6000元时，查看这个员工的信息。

分析：

触发事件？	触发时间？	触发级别？	触发对象
update事件	更改之后	行级（不是针对于操作，而是针对的员工）	emp表

操作：
建表存储该员工的信息
Create table shji_info(info varchar(200));

例： 创建触发器，当更新员工工资时触发，如果员工资超过了6000，则向表中插入一条记录，记录该员工的编号，姓名和工资

方案1:

create table info(xinxi varchar2(100));

create or replace trigger shenji after update on emp for each row
begin
	if :new.sal>6000 then
		insert into info values(:new.name || '       ' || :new.sal || '      ' :new.comm);
	end if;
end;

验证：

update emp set sal=6500 where empno=7369;

select * from info;

上面我们更新工资的时候，使得工资大于6000，所以会触发触发器，这个时候使用查询语句，我们可以看到info表中已经有了记录。

方案2：

create table info(xinxi varchar2(100));

create or replace trigger shenji after update on emp for each row
when (new.sal>6000)       --只有当更新的工资大于6000触发器才会执行，只有行级触发器才能在这个地方加条件，不加条件的话，只要进行update都会执行触发器，只是触发不触发触发器的问题了。
begin
	if :new.sal>6000 then
		insert into info values(:new.name || '       ' || :new.sal || '      ' :new.comm);
	end if;
end;

10、应用案例：数据的备份和同步

a、当emp表中的员工涨工资后，在触发器中更新备份表中员工的工资

员工涨工资后，会同步备份到备份表中

分析：

触发事件？	触发时间？	触发级别？	触发对象
update事件	更改之后（after）	行级（不是针对于操作，而是针对的员工）	emp表

操作：
建立一个备份表emp_copy
Create table emp_copy as select * from emp;

当emp表中的员工涨工资后，在触发器中更新备份表中员工的工资。

方案1：

create or replace trigger copysal
after update on emp
for each row
begin
    update emp_back set sal=:new.sal where empno=:new.empno;
end;

方案2：

create table copy as select * from emp;

create or replace trigger trigcopy after update on emp for each row
begin
	--inserting updating deleting 三个谓词，分别代表插入、更新、删除操作
	if updating('sal') then
		update copy set sal=:new.sal where empno=:new.empno;
	end if;
end;

验证：

update emp set ename='SMITH' where empno=7369;   --不触发触发器，因为只有修改工资的时候才会触发触发器

update emp set sal=6800 where empno=7369;

select * from copy;

此时我们可以看到copy表同步更新。

11、触发器的执行顺序

当一个表中有多个触发器时，通常按如下顺序执行:
before表级触发器
before行级触发器
更新操作
after行级触发器
ater表级触发器

我们可以发现，对于befor触发器，表级触发器的执行顺序要比行级触发器的执行顺序要早。
对于after触发器，行级触发器的执行顺序要比表级触发器的执行顺序要早。

12、课堂练习：

a、在非工作时间，对emp表进行的所有DML操作都不允许。（三个谓词inserting、deleting、updating的使用方法）

三个谓词：inserting、deleting、updating
用于判断在DML操作中触发的具体事件

如：如果事件是insert，则inserting的值为true，否则为false.
当此时只有insert操作的时候，inserting的值为true，deleting、updating的值都为false。

create or replace trigger emptime2
before insert or delete or update on emp
declare
	v_riqi varchar2(10);
	v_hh number(2);
begin
    v_riqi:=to_char(sysdate,'day');
    v_hh:=to_number(to_char(sysdate,'hh24'));
    if(v_riqi in('星期六','星期日') or (v_hh>17 or v_hh<8)) THEN
    if inserting then
        raise_application_error(-20013,'非工作时间禁止插入');
    elsif deleting then
        raise_application_error(-20014,'非工作时间禁止删除');
    elsif updating then
        raise_application_error(-20015,'非工作时间禁止更新');
    else 
        raise_application_error(-20016,'非工作时间禁止任何操作');
    end if;
  end if;
end;

b、例：向dept表中添加数据时，部门编号是主键，要求必须有值且不重复，可以使用序列实现这个要求。创建一个触发器，要求使用触发器自动为该主键赋值，从而不需要手动方式向dept表的主键列添加数据。

create sequence myseq start with 1 ;

create or replace trigger mttrig after insert on emp
for each row
declare
	eno emp.empno%type := myseq.nextval;
	vnum number;
begin
	loop
	select count(*) into vnum from emp where empno=eno;
	exit when vnum=0;
	eno := myseq.nextval;
	end loop;
	insert into emp(empno) values(eno) where ename=:new.ename;
end;


--验证：
insert into emp(ename,job) values('张三','会计');

换个思路：

CREATE SEQUENCE MYDEPT INCREMENT BY 10 START WITH 50;
create or replace trigger t_dept before insert on dept  for each row
declare
begin
  if :new.deptno is null then 
 select MYDEPT.nextval into :new.deptno from dual;
  end if;
end;


测试：
insert into dept(dname,loc) values('aaa','北京');
select * from dept;

13、替代触发器

作用：解决多表视图的更新问题

当执行视图的更新时，将会触发替代触发器
具体含义：执行一个替代操作来代替触发事件的操作。即Oracle只运行触发器操作而不再运行触发语句。
替代触发器只能基于视图创建，并且主要用于不可修改的视图上。

单表视图没必要建立替代触发器

过程就是自己做的操作没有执行，执行替代触发器来完成自己需要的操作。

所有的替代触发器都是建立在视图上的，所有的替代触发器一定是行级触发器

语法：
create or replace trigger 触发器名
instead of DML on 视图名
for each row
PL/SQL块

a、应用案例：当向视图中插入新记录时，被认定为非法。试用触发器完成插入操作

1、先建一个复杂视图

2、验证向视图中插入记录

3、建立替代触发器

视图的本质就是一条查询语句

在system用户下：
grant create view to c##scott;

因为scott用户没有创建视图的权限，所以如果想在scott用户下建立视图的话，首先要在system管理员用户下为scott用户授权

方案一：

--1、创建复杂视图
create or replace view ed_view as select empno,ename,sal,e.deptno,dname from emp e join dept d on e.deptno=d.deptno;

--2、验证：插入记录
insert into ed_view values(1111,'aaa',1230,50,'黄山');
--提示：无法通过联接视图修改多个基表

--3、那么就需要创建一个替代触发器啦
create or replace trigger mytrig instead of insert on ed_view for each row
declare
	ecount number;
	dcount number;
begin
	select count(*) into ecount from emp where empno=:new.empno;
	select count(*) into dcount from dept where deptno=:new.deptno;
	--先判断dept表，因为emp表中的deptno依赖于dept表
	if dcount=0 then
		insert into dept(deptno,dname) values(:new.deptno,:new.dname);
	end if;
	if ecount=0 then
		insert into emp(empno,ename,sal,deptno) values(:new.empno,:new.ename,:new.sal,:new.deptno);
	end if;
end;

--验证：
insert into ed_view values(1111,'aaa',1230,50,'黄山');
--提示：1行已插入

实际上insert into ed_view values(1111,‘aaa’,1230,50,‘黄山’);这条语句并没有执行，是通过替代触发器执行的。

方案二：

create or replace trigger view_insert_tigger
  instead of insert on v_emp20  
  for each row
declare
    v_empCount       NUMBER;
    v_deptCount      NUMBER;
begin
    --判断要增加的员工是否存在
    SELECT COUNT(empno) INTO v_empCount FROM emp WHERE empno=:NEW.empno;
        --判断要部门是否存在
    SELECT COUNT(deptno) INTO v_deptCount FROM dept WHERE deptno=:new.deptno;
  --如果员工不存在
  IF v_empCount=0 THEN
      INSERT INTO emp(empno,ename,job,sal,deptno)
      VALUES(:new.empno,:new.ename,:new.job,:new.sal,:new.deptno);
    END IF;
    --如果部门不存在
    IF v_deptCount=0 THEN
      INSERT INTO dept(deptno,dname,loc)VALUES(:new.deptno,:new.dname,:new.loc);
    END IF;
end ;

14、DDL(create、drop、alter…)触发器

是系统级触发器

触发的事件主要包括两种:用户触发事件、系统触发事件(数据库级别触发事件)

创建DDL触发器需要用户具有DBA 权限

a、应用案例：创建一个基于create命令的DDL触发器

create or replace trigger ddl_trig
after create on schema  --schema 是针对用户的操作，这是用户级别的触发器，如果是数据库级别的这个地方就写database
begin
     dbms_output.put_line('scott:对象创建成功');
end;


--验证：
create table t(tid number);
--创建成功会提示scott:对象创建成功，因为触发了触发器

b、应用案例：记录用户所有的DDL操作

1、建立日志表
2、当用户执行DDL操作时，向表中插入记录

---

日志表：

create table ddl_log(
	lname varchar2(30),
	obj_type varchar2(20),
	eventname archar2(20),
	ddl_date date);

触发器：

create or replace trigger trigddl after ddl on schema
begin
	insert into ddl_log values(ora_dict_obj_name,ora_dict_obj_type,ora_sysevent,sysdate);
end;

ddl：表示的是create、drop、alter这种命令，只要任意一种命令执行，就会触发触发器。
on schema ：表示的是在所有用户级别的所有操作上。

创建表：

create table tt(tid number);

执行操作（数据定义操作：DDL操作）：

alter table tt add tname varchar2(10);
drop table tt;

验证：

select * from ddl_log;

---

c、事件属性函数

事件属性函数：

1、ora_dict_obj_name：返回所对应对象的名称，类型为varchar2(30)
或者使用sys.dictionary_obj_name属性

2、ora_dict_obj_type：返回DDL操作所对应对象的类型，返回类型为varchar2(20)

3、ora_sysevent：返回触发器的系统事件名

d、应用案例：创建系统事件触发器，监视用户的登录退出情况

系统事件必须是有DBA权限的

1、先创建一个数据表，记录用户的登录和退出事件
create table event(eventuser varchar2(20),action varchar2(20),logtime date default sysdate);

2、需要创建两个触发器，一个是对登录的触发，一个是对退出的触发。（因为它们的触发时间不一样）

数据库级别的触发器⬇️：

--登录触发器
create or REPLACE trigger login_trig
after logon on database
begin
  insert into event(eventuser, action) values(user,'log on');
end;


--退出触发器
create or REPLACE trigger login_trig
after logon off database
begin
  insert into event(eventuser, action) values(user,'log off');
end;

15、在触发器中调用过程或函数

可以在触发体中直接调用过程或函数

调用形式：

1、使用匿名块
过程名后不用加括号

2、使用call命令
使用该命令时不需要写begin/end
过程名后不用加括号
程序末尾也不能加分号

示例：

create or replace trigger pro_trig
after insert on dept
call insdept

create or replace trigger pro_trig
after insert on dept
begin
   insdept;
end;

--过程定义
create or replace procedure insdept
is
begin
  dbms_output.put_line('插入成功');
end;

--测试
Set  serveroutput  on
insert into dept values(60,'keyanchu','weihai');

16、触发器管理

a、通过数据字典查看

select object_name, object_type, status from user_objects where object_name='PRO_TRIG';

select line,text from user_source where name='INSDEPT';

select trigger_name,status,table_name from user_triggers;

b、启用和禁用触发器

触发器默认是启用状态

启用或禁用某个触发器

 alter trigger trigger_name disable | enable

启用或禁用某个对象上的所有触发器

alter table table_name disable | enable all triggers

c、重新编译触发器

alter trigger trigger_name compile

d、删除触发器

drop trigger trigger_name