Oracle触发器

Oracle触发器

1.概念
触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。

触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。

2.触发器组成:
  2.1  触发事件：
  引起触发器被触发的事件。 例如：DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。

2.2 触发时间：
  即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。

2.3 触发操作：
  即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。 例如：PL/SQL 块。

2.4 触发对象：
  包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。

2.5 触发条件：
  由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。

2.6 触发频率：
  说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。

3.DML触发器
 3.1 语句级触发器
  是指当某触发事件发生时，该触发器只执行一次；

3.2 行级触发器
  3.2.1 概念：
   是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

3.2.2 行级触发器语法
   create  or replace trigger  触发器名字
   before|after  （动作update|insert|delete）
   on 表名
   for each row --行级触发器
   begin
       --备份操作（插入删除的数据到备份表）
   end

4.编写触发器时，需要注意以下几点：
 触发器不接受参数。
 l   一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。
 l  在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。
 l  触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。
 l  在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。
 l 触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。
 l  在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。
 l  在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能向表中的任何long和blob列。
 l  不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。

5. 触发器语法
    CREATE [OR REPLACE] TRIGGER trigger_name
    {BEFORE | AFTER }
    {INSERT | DELETE | UPDATE [OF column [, column …]]}
    [OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
    ON [schema.]table_name | [schema.]view_name
    [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
    [FOR EACH ROW ]
    [WHEN condition]
    PL/SQL_BLOCK | CALL procedure_name;

6.行级触发器
  案例1:
  当删除某一条记录时，将删除数据插入到某张备份表
  create or replace trigger tr_del_emp
     before delete  --触发的时机是删除前触发
     on emp    --指定操作的表
     for each row              --行级触发器
     when (old.deptno<>10)  --触发的条件
  begin
     --删除前将数据插入到备份表
     insert into del_emp(deptno,empno,ename,job,mgr,sal,comm,hiredate)
        values( :old.deptno, :old.empno, :old.ename, :old.job, :old.mgr, :old.sal, :old.comm,
         :old.hiredate);
  end;
   /
 
  --删除5001数据
  delete emp where empno=7902;
 
  --查询备份表
  select * from del_emp;
 
  --回滚
  rollback；
 
  --删除触发器
  drop trigger tr_del_emp;
 案例2:
  create or replace trigger upda_emp_row
    after insert or update or delete
    on emp
    for each row
  begin
    if updating then
      dbms_output.put_line('数据已经更新！');
    elsif deleting then
      dbms_output.put_line('数据已经删除！');
    elsif inserting then
      dbms_output.put_line('数据已经插入');
    end if;
  end;
  /
 
  --删除表中所有记录
  delete from emp;
 
  --回滚删除的数据
  rollback;
 
  --删除触发器
  drop trigger upda_emp_row;

7. 语句触发器
    --.语句触发器( 默认触发器)
    create or replace trigger upda_emp
      after insert or update or delete
      on emp
    begin
       if updating then
          dbms_output.put_line('数据已经更新');
      elsif deleting then
         dbms_output.put_line('数据已删除');
      elsif inserting then
        dbms_output.put_line('数据插入');
      end if;
    end;  
    /
    --删除
    delete from emp;
    
    --回滚
    rollback;
    
    --删除触发器
    drop trigger upda_emp;
    
    conn system/accp
    
    --创建用户
    create user user1 identified by user1；
    
    --授权
    grant connect,resource to user1;
    
    --切换用户user1
    conn user1
    
    --创建记录表
    create table event_ddl(event varchar2(20), username varchar2(10),owner varchar2(10),
       objname varchar2(20),objtype varchar2(10),time date);

8.模式触发器:DDL
 --创建用户
  create user user1 identified by user1;
 
  --给用户赋权
  grant connect ,resource to user1;
 
  --切换用户登录
  conn user1/user1;
 
  --创建记录表
   create table event_ddl(
    event varchar2(20),
    username varchar2(20),
    owner varchar2(10),
    objnamename varchar2(20),
    objtype varchar2(10),
    time date);
 
 --创建触发器记录user1模式的所有ddl的操作
 create or replace trigger tr_ddl
 after ddl on user1.schema
 begin
 --ora_sysevent是创建还是删除
 --ora_login_user 登录用户
 --ora_dict_obj_owner 操作用户
 --ora_dict_obj_name 操作对象名称=表名
 --ora_dict_obj_type操作类型是表还是其他
   insert into event_ddl values(ora_sysevent,ora_login_user,ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,sysdate);
 end;
 /
 
 --调用触发器
 create table temp(a int);
 drop table temp;
 
 --查看结果
 select * from event_ddl;
 drop table event_ddl purge;
 
 EVENT  USERNAME    OWNER  OBJNAMENAME   OBJTYPE   TIME
 ----- ----------- ------- ------------  -------   -----
 CREATE  USER1      USER1    TEMP       TABLE      13-12月-15
 
 DROP    USER1      USER1    TEMP       TABLE      13-12月-15
 
 CREATE  USER1      USER1    TEMP       TABLE      23-3月 -16
 
 DROP    USER1      USER1    TEMP       TABLE      23-3月 -16
 
 --删除触发器
 drop trigger tr_ddl;

9.数据库级触发器
 --题目：记录所有用户登录和退出数据库的用户名称、登录和退出时间
 --用管理员操作
 conn system
 输入密码：
 
 --创建记录表
 create table log_table(
   username varchar2(20), login_time date,
   logoff_time date,address varchar2(20)
   );
 
 --创建数据库级触发器
 create or replace trigger tr_login
 after logon on database
 begin
   insert into log_table(username,login_time,address)
     values(ora_login_user,sysdate,ora_client_ip_address);
 end;
 /
 
 --切换用户登录
 conn holly/sys
 conn scott/tiger
 conn system/sys
 --查询记录表
 select * from log_table;
 USERNAME             LOGIN_TIME     LOGOFF_TIME    ADDRESS
 -------------------- -------------- -------------- --------------------
 HOLLY                13-12月-15
 SCOTT                13-12月-15
 SYSTEM               13-12月-15
 SYSMAN               13-12月-15
 
 --删除表数据并且不进入回收站
 drop table log_table purge;
 
 --删除触发器
 drop trigger tr_login;

----------------------------------------------------------------------

----------------------------------------------------------------------

Oracle触发器(建立系统事件触发器)
系统时间触发器是指基于Oracle系统事件(LOGIN登录 STARTUP启动)所建立的触发器，通过使用系统事件触发器，提供了跟踪系统或数据库变化的机制。

1.常用事件属性函数

建立系统触发器要用到的：

ora_client_ip_address:用于返回客户端的IP地址
ora_database_name：用于返回当前数据库名
ora_des_encrypted_password:用于返回DES加密后的用户口令
ora_dict_obj_name:用于返回DDL操作所对应的数据库对象名
ora_dict_obj_name_list(name_list_ OUT ora_name_list_t):用于返回字事件中被修改的对象名列表
ora_dict_obj_owner:用于返回DDL操作所对应的对象的所有者名。
ora_dict_obj_ower_list(ower_list OUT ora_name_list_t):用于返回在事件中被修改对象的所有者列表
ora_dict_obj_type:用于返回DDL操作所对应的数据库对象的类型。
ora_grantee(user_list OUT ora_name_list_t):用于返回授权时事件授权者。
ora_instance_num:用于返回历程号。
ora_is_alter_column(column_name IN VARCHAR2):用于检测特定列是否被修改
ora_is_creating_nested_table:用于检测是否正在建立嵌套表
ora_is_drop_column(column_name IN VARCHAR2):用于检测特定列是否被删除
ora_is_servererror(error_number):用于检测是否返回了特定Oracle错误。
ora_login_user：用于返回登录用户名
ora_sysevent ：用于返回触发 触发器的系统时间名。

2.建立例程启动和关闭触发器：

为了跟踪例程启动和关闭事件，可以分别建立例程启动触发器和历程关闭触发器

为了记载历程启动和或关闭事件和时间，首先建立事件表event_table：

conn sys/oracle as sysdba

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

在建立了事件表event_table之后，就可以在触发器中引用该表了。

例程启动触发器和关闭触发器只有特权用户才能建立，例程启动触发器只能使用AFTER关键字，而例程关闭触发器只能使用BEFORE关键字

CREATE OR REPPLACE TRIGGER tr_startup
AFTER STARTUP ON DATABASE
BEGIN
INSERT INTO event_table VALUES(ora_sysevent,SYSDATE);
END;
/

CREATE OR REPPLACE TRIGGER tr_shutdown
BEFORE SHUTDOWN ON DATABASE
BEGIN
INSERT INTO event_table VALUES(ora_sysevent,SYSDATE);
END;
/

在建立了tr_startup触发器之后，当打开数据库之后会执行该触发器相应代码，在建立触发器tr_shutdown之后，在关闭例程之前，会执行触发器的相应代码,但SHUTDOWN ABORT(关闭数据库)不会触发该触发器。

3.建立登录和退出触发器

为了记载用户登录和退出事件，可以分别建立登录和退出触发器。为了记载登录用户和退出用户的名称。时间和IP地址，应该首先建立专门存档登录和退出的信息表LOG_TABLE

conn sys/oracle as sysdba

CREATE TABLE log_table(
username VARCHAR2(20),login_time DATE,
logoff_time DATE,address VARCHAR2(20)
);

在建立了LOG_TABLE表之后，就可以在触发器中引用该表了。
要用特权身份用户来建立登录和退出触发器，并且登录触发器只能使用AFTER关键字，而退出触发器用BEFORE

CREATE OR REPLACE TRIGGER tr_logon
AFTER LOGIN ON DATABASE
BEGIN
INSERT INTO log_table(username,logon_time,address)
VALUES(ora_login_user,SYSDATE,ora_client_ip_address);
END;
/

CREATE OR REPLACE TRIGGER tr_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
INSERT INTO log_table(username,logoff_time,address)
VALUES(ora_login_user,SYSTEM,ora_client_ip_address);
END;
/

在建立了触发器tr_logon之后，当用户登录到数据库之后，会执行其触发器代码；在建立了触发器tr__logoff之后，当用户断开数据库连接之前，会执行其触发器代码。

4.建立DDL触发器

为了记载系统所发生的DDL事件(CREATE,ALTER,DROP),可以建立DDL触发器，为了记载DDL时间信息，应该建立专门的表，以便存放DDL事件信息。

conn sys/oracle as sysdba

CREATE TABLE event_ddl(
event VARCHAR2(20),username VARCHAR2(10),
owner VARCHAR2(10),obbjname VARCHAR2(20),
objtype VARCHAR2(10),time DATE
);

在建立了表event_ddl之后，就可以在触发器中引用该表，为了记载DDL事件，应该建立DDL触发器，注意，当建立DDL触发器时，必须使用AFTER关键字。

CREATE OR REPLACE TRIGGER tr_ddl
AFTER DDL ON scott.schema
BEGIN

INSERT INTO event_ddl VALUES(
ora_sysevent,ora_login_user,ora_dict_obj_owner,ora_dict_obj_name,ora_obj_type,SYSTEM
);
END;
/

当建立了触发器tr_dll之后，如果在SCOTT方案对象上执行了DDL操作，则会将该新息记载到表event_ddl中。

create or replace trigger logon_trigger
after logon on database
begin
insert into log_table(username,logon_time,address) values(ora_login_user,sysdate,ora_client_ip_address);
end; /

create or replace trigger logoff_trigger
before logoff on database
begin
insert into log_table(username,logoff_time,address) values(ora_login_user,sysdate,ora_client_ip_address);
end; /