Oracle触发器

数据库触发器是储存于数据库的命名PL/SQL语句块,当接触事件时有发生常它们会包含执行。

点事件可以是之类任何一样栽:

1>
处理多少库表的DML语句(如INSERT,UPDATE或者DELETE)。在接触事件时有发生之前还是以后,触发器会实行。

2>
特定用户以一定模式下,或者其他用户执行的DDL语句(如CREATE或者ALTER)。这种触发器经常给用于审计目的。它们可以记录各种模式修改,何时实施,以及谁用户执行的。

3> 系统事件,如数据库启动或关闭

4>
用户事件,如登陆和撤回。即好定义一个触发器,在用户登陆数据库时记下用户称和登陆事件。

缔造触发器的通用语法如下所示:

CREATE [OR REPLACE] TRIGGER Trigger_name
{BEFORE |AFTER} Triggering_event ON table_name
[FOR EACH ROW]
[FOLLOWS another_trigger]
[ENABLE/DISABLE]
[WHEN condition]
DECLARE
     declaration statements
BEGIN
     executable statements
EXCEPTION 
     exception-handling statements
END;

其中,trigger_name是触发器的称。BEFORE或者AFTER指明触发器何时实施,即于接触事件产生前,还是之后,trigger_event是本着数据库表的DML语句。table_name是同拖欠触发器相关的数据库表的称谓。子句FOR
EACH
ROW指行触发器。FOLLOWS选项,指定触发器被点的次第。这个选项适用于当相同表上所定义之,并且会以同一时间点执行的触发器。ENABLE和DISABLE子句指定触发器是于启用,还是禁用状态下受创造的。默认情况下是启用的。

剥夺或启用触发器,可用以下命令:ALTER TRIGGER trigger_name
DISABLE/ENABLE;

剥夺某个表上的有所触发器,可用如下命令:ALTER TABLE table_name DISABLE
ALL TRIGGERS.

触发器按照不同的分法,有不同之归类,主要发生以下简单种:

以点的时日而分为:BEFORE触发器和AFTER触发器

论点的类型可分为:行触发器,语句触发器和INSTEAD OF触发器

下,我们成现实的实例来演示不同之触发器。

一、 BEFORE触发器

范例一:

create or replace trigger student_i
before insert on student
for each row
BEGIN
  :NEW.student.id := student_seq.nextval;
  :NEW.created_by := USER;
  :NEW.created_date := SYSDATE;
END;

注意:触发器包含伪记录:NEW,使得你可拜正让插student表的数据行。为看伪记录:NEW的独成员,需要采用点符号:

        通过PL/SQL表达式访问序列是Oracle 11g的新特点。在Oracle
11g之前,只能通过以下措施获得:

        select student_seq.nextval into v_student_id from dual;

        :NEW.student.id := v_student_id

范例二:

create or replace trigger emp_update
before update or delete or insert on emp
for each row
begin
   if updating or deleting or inserting then
      raise_application_error(-20001,'The table emp can not be modified');
   end if;
end;

该触发器的代码有来三只布尔函数-updating,deleting,inserting,如果对这发明执行update操作,则函数updating的价为TRUE;如果对是发明执行delete操作,则函数deleting的价值吗TRUE。insert操作亦然。

范例三:

create or replace trigger emp_update
before update of sal on emp
for each row
begin
   if updating then
       raise_application_error(-20001,'Salary can not be modofied');
   end if;
end;

可见,对某列也可采取触发器

二、 AFTER触发器

范例四:

create or replace trigger dept_delete
after delete on dept
for each row
begin
   if deleting then
      delete from emp where deptno = :old.deptno;
   end if;
end;

dept和emp表存在外键约束,即dept表的主键deptno是emp表的外键,当我们去dept表的实施记录时,如果要删除的deptno在emp表中留存对应之笔录,则会报ORA-02292:
integrity constraint (SCOTT.FK_DEPTNO) violated – child record
found。构造触发器如达到,当我们抹dept的推行记录时,会自动删除deptno在emp表中对应的履行记录。

其三、 自治工作

     
 trigger语句子块被无克含有事务处理语句,如commit和rollback。倘若要用事务处理语句,则必须用到自治工作。

季、 行触发器

     
 行触发器指的是触发器被硌的次数等同于触发语句所影响的数据行数量。当语句子FOR
EACH ROW出现于CREATE TRIGGER子句被,该触发器就是行触发器。

       上述范例均属行触发器。

五、 语句触发器

     
 对于讲话触发器而言,每执行同一破触发语句,该触发器就会见履同样糟,也就是说,不管触发语句影响小多少实行,该触发器只见面实行同样潮。

     
 当该触发器所实施之操作不因让独立记录被之数码时,就应用语句触发器。例如,如果愿意限制只能于上班时间访问emp表,就相应使语句触发器。

范例五:

create or replace trigger emp_record
before insert or update or delete on emp
declare
   v_day varchar2(10);
begin
   v_day := rtrim(to_char(sysdate,'DAY'));
   if v_day in ('SATURDAY','SUNDAY') then 
      raise_application_error(-20000,'The table can not be modified during off hours');
   end if;
end;

EMP表只能当工作日修改。

六、 替代触发器

     
 到目前为止,我们所提到的触发器都是基于表的,其实,Oracle还提供了其他一样种植触发器,这种触发器是当数据库视图上缔造的,即替代触发器。替代触发器会代替基于视图的DML操作(INSERT、UPDATE、DELETE),而一直作用为底层的数库表。我们来探视下面的尝试:

      1> 创建视图

       SQL> create or replace view v_test as select
e.empno,e.ename,d.deptno,d.dname from emp e,dept d where
e.deptno=d.deptno;

      2> 查询视图

       SQL> select * from v_test;

       EMPNO ENAME DEPTNO DNAME
       ———- ———- ———- ————–
       7782 CLARK 10 ACCOUNTING
       7839 KING 10 ACCOUNTING
       …

       3> 插入数据

        SQL> insert into v_test values(1234,’VICTOR’,50,’IT’);

        insert into v_test values(1234,’VICTOR’,40,’IT’)
        *
        ERROR at line 1:
        ORA-01776: cannot modify more than one base table through a join
view

       4> 建立代表触发器

CREATE OR REPLACE TRIGGER t_v_test
INSTEAD OF INSERT ON v_test
DECLARE
   duplicate_info EXCEPTION;
   PRAGMA EXCEPTION_INIT(duplicate_info,-00001);
BEGIN
   INSERT INTO  dept(deptno,dname)
       VALUES(:new.deptno,:new.dname);
   INSERT INTO  emp(empno,ename,deptno)
       VALUES(:new.deptno,:new.ename,:new.deptno);
EXCEPTION
   WHEN duplicate_info THEN 
       RAISE_APPLICATION_ERROR(-20001,'Duplicate empno or deptno');
END;

       5> 重新插数据 

        SQL> insert into v_test values(1234,’VICTOR’,50,’IT’);

        1 row created.

         测试OK!

七、 系统触发器

       下面试举几例

       1> 限制用户从指定IP登陆

CREATE OR REPLACE TRIGGER TRIGGER_RESTRICT_LOGON
AFTER LOGON ON DATABASE
DECLARE
   RESTRICTED_USER VARCHAR2(32) := 'SCOTT';
   ALLOWED_IP      VARCHAR2(16) := '192.168.1.1';
   LOGON_USER      VARCHAR2(32);
   CLIENT_IP       VARCHAR2(16);
BEGIN
   LOGON_USER := SYS_CONTEXT('USERENV','SESSION_USER');         -->> SYS_CONTEXT是一个蛮有用的函数
   CLIENT_IP  := NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'), 'NULL');
   IF LOGON_USER = RESTRICTED_USER AND CLIENT_IP <> ALLOWED_IP THEN
        RAISE_APPLICATION_ERROR(-20001, RESTRICTED_USER || ' is not allowed to connect from ' || CLIENT_IP);
   END IF;
END;

       2> 通过用户级别触发器修改日期格式

CREATE OR REPLACE TRIGGER emp_format
AFTER LOGON ON SCHEMA
DECLARE
   sqlstr VARCHAR2(60) :='alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';   注意:连续两个单引号表示转义
BEGIN
   execute immediate sqlstr;
END;

        3> 记录用户之登陆信息

SQL> create table log_table(sid number,serial# number,username varchar2(20),action varchar2(8),log_time varchar2(19));   -->> 首先创建表用于记录用户的登陆信息

CREATE OR REPLACE TRIGGER logon_db
AFTER LOGON ON DATABASE
DECLARE
  v_sid v$mystat.sid%type;
  v_serial# v$session.serial#%type;
  v_username v$session.username%type;
BEGIN
  SELECT sid INTO v_sid FROM v$mystat WHERE rownum=1;
  SELECT serial#,username
  INTO   v_serial#,v_username
  FROM v$session WHERE sid=v_sid;
  INSERT INTO log_table
  VALUES (v_sid,v_serial#,v_username,'logon',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
END logon_db;

 AFTER LOGON ON DATABASE 吗只是替换为 BEFORE SHUTDOWN ON DATABASE,BEFORE
LOGON ON SCHEMA,ALTER LOGON ON SCHEMA等等。

八、 复合触发器

再度多系内容,可参照官方文档:http://docs.oracle.com/cd/E11882\_01/appdev.112/e25519/triggers.htm\#LNPLS020

         

       

 

      

      

 

      

 

相关文章