oracle 触发器的种类，使用触发器实现复杂完整性的方法， instead of触发器更新视图的建立方法，特殊触发器（模式触发器、数据库启动关闭触发器、用户登录退出触发器）

前言

常用系统变量

  ORA_CLIENT_IP_ADDRESS 返回客户端的ip地址，只用在系统触发器中
  ORA_DATABASE_NAME 返回数据库名
  ORA_LOGIN_USER 返回登录用户名
  ORA_DICT_OBJ_NAME 返回DDL操作所对应的数据库对象名
  ORA_DICT_OBJ_TYPE返回DDL操作所对应的数据库对象类型
  ORA_SYSEVENT 返回系统事件

---

1.1 触发器定义

  触发器是当特定事件出现时自动执行的存储过程；特定事件可以是执行更新的DML语句和DDL语句；
  触发器不能被显式调用；

触发器的功能：
  自动生成数据；自定义复杂的安全权限；提供审计和日志记录；启用复杂的业务逻辑；

---

1.2 触发器语法

create [or replace] trigger 触发器名
	after|before|instead of
	[insert][[or]update [of 字段列表]] [[or] delete]
	on 表或视图名称
	[referencing {OLD [as] old  / NEW [as] new }]
	[for each row]  
	[when (条件)]
	PL/SQL块;

  for each row 对每一行执行PL/SQL块，即行级触发器，没有此参数则为表触发器；

  after触发器：表先保存更新，再激活触发器；
  before触发器：表先激活触发器，之后在保存更新；

  使用 :new 标识用户即将插入的一行记录 :old即将被删除的记录，在触发器中使用 :new 和 :old 必须有for each row。当执行 insert时， :new 存在 :old不存在；当执行 delete时， :new 不存在 :old存在；当执行 update时， :new 存在 :old存在；

如:

（1）scott用户下emp表 crud时进行提示：

set serverout on ;

create or replace trigger trig_emp
	before
	insert or update  or delete
	on scott.emp
	begin
		dbms_output.put_line('触发器trig_emp响应了');
	end;
	/

trig_emp 触发器是针对整个表，与影响的行数无关，需要添加参数for each row

create or replace trigger trig_emp
	before
	insert or update  or delete
	on scott.emp
	for each row
	begin
		dbms_output.put_line('触发器trig_emp响应了');
	end;
	/

（2）scott 用户emp表 不允许插入sal字段低于500的记录
必须使用for each row
使用 raise_application_error(异常编码,异常信息),中断插入操作

若触发器编译出错，可通过show errors查看：

create or replace trigger trig_emp
	before
	insert
	on scott.emp
	for each row
	begin
	    if :new.sal<500 then
	    raise_application_error(-20001,'sal错误，不能插入');
	    end if;
	end;
	/

（3）scott 用户emp表 插入的sal字段为负值时，转换为正值

create or replace trigger trig_emp
	before
	insert
	on scott.emp
	for each row
	begin
	    if :new.sal<0 then
	    :new.sal :=- :new.sal;
	    end if;
	end;
	/

（3）scott 用户emp表 删除的记录sal字段大于3000 时，不允许

create or replace trigger trig_emp
	before delete on scott.emp
	for each row
	begin
	    if :old.sal>3000 then
	   raise_application_error(-20001,'不允许删除');
	    end if;
	end;
	/

---

1.3 触发器分类

1.3.1 模式（DDL）触发器

如：
(1)schema内删除表时，向t2表记录表名和删除时间：

create table t1(id number);
create table t2(obj_name varchar2(30),obj_type varchar2(30),dtime timestamp);

create or replace trigger trig_drop
	after drop on schema
	begin 
	insert into t2 values(ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_TYPE,SYSDATE);
		dbms_output.put_line('用户:'||ORA_LOGIN_USER||'ip地址'||ORA_CLIENT_IP_ADDRESS
		||'数据库名'||ORA_DATABASE_NAME );	 
	end;
	/

1.3.2 数据库触发器

如：
(1)记录数据库的启动或关闭

create table event_table(event varchar2(30),time date);

在管理员用户sys下创建

create or replace trigger trig_startup
	after startup on database
	begin 
	insert into scott.event_table values(ORA_SYSEVENT,SYSDATE);
	end;
	/

create or replace trigger trig_shutdown
	before shutdown on database
	begin 
	insert into  scott.event_table values(ORA_SYSEVENT,SYSDATE);
	end;
	/

(2)记录用户登录或退出

create table log_table(username varchar2(30),logon_time date,logoff_time date,address varchar2(30) );

在管理员用户sys下创建

create or replace trigger trig_logon
	after logon on database
	begin 
	insert into scott.log_table(username,logon_time ,address )  values(ORA_LOGIN_USER,sysdate,ORA_CLIENT_IP_ADDRESS );
	end;
	/

create or replace trigger trig_logoff
	before logoff on database
	begin 
	insert into scott.log_table(username,logoff_time ,address )   	values(ORA_LOGIN_USER,sysdate,ORA_CLIENT_IP_ADDRESS );
	end;
	/

1.3.3 DML触发器

行级触发器

如：
（1）当用户进行增删改时，把情况输出

create or replace trigger trig_crud
	before insert or update or delete 
	on scott.emp
	for each row
	begin
	 if inserting then
	 	dbms_output.put_line('insert'||:new.EMPNO||'姓名'||:new.ENAME);
	 	elsif updating then
	 	dbms_output.put_line('update前'||:old.EMPNO||'update后'||:new.EMPNO);
	 	elsif deleting then
	 	dbms_output.put_line('delete'||:old.EMPNO||'姓名'||:old.ENAME);	 
	 end if;
	end;
	/

语句级触发器

instead of触发器

如：
(1) instead of用于更新视图，实际是更新基表

create or replace view view2 as
select e.empno,e.job,e.deptno edno,d.deptno ddno ,d.dname from emp e,dept d where e.deptno =d.deptno;

create or replace trigger trig_view
	instead of 
	update 
	on scott.view2
	for each row
	declare  a number(20);
	begin
		select  DEPTNO into a from emp where EMPNO=:old.EMPNO;
		update dept set DNAME=:new.dname where DEPTNO =a;
	end;
	/

1.4 触发器操作

启用或禁用触发器

alter trigger 触发器名 disable;

alter trigger 触发器名 enable;

删除触发器

drop trigger 触发器名