触发器

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

接触事件能够是之类任何一种:

1>
处理数据库表的DML语句(如INSEPAJEROT,UPDATE可能DELETE)。在触及事件产生在此之前照旧现在,触发器会履行。

2>
特定用户在一定形式下,只怕其余用户执行的DDL语句(如CREATE恐怕ALTE奇骏)。那种触发器平时被用来审计目标。它们得以记下各类情势修改,曾几何时实施,以及哪些用户执行的。

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或然AFTE逍客指明触发器什么日期实施,即在触发事件时有爆发在此之前,如故现在,trigger_event是本着数据库表的DML语句。table_name是与该触发器相关的数额库表的称呼。子句FOR
EACH
ROW指行触发器。FOLLOWS选项,内定触发器被触发的逐条。那个选项适用于在相同表上所定义的,并且会在同样时间点实施的触发器。ENABLE和DISABLE子句钦命触发器是在启用,照旧禁止使用状态下被制造的。暗许意况下是启用的。

剥夺或启用触发器,可用以下命令:ALTE凯雷德 T瑞鹰IGGELAND trigger_name
DISABLE/ENABLE;

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

触发器根据分化的分法,有例外的归类,首要有以下三种:

按触发的时刻可分为:BEFORE触发器和AFTE瑞鹰触发器

按触发的品种可分为:行触发器,语句触发器和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 T奥迪Q7IGGERAV4子句中,该触发器就是行触发器。

       上述范例均属于行触发器。

伍 、 语句触发器

     
 对于说话触发器而言,每执行3次触发语句,该触发器就会执行1次,也正是说,不管触发语句影响多少多少行,该触发器只会实行二次。

     
 当该触发器所执行的操作不借助于独立记录中的数据时,就相应选用语句触发器。例如,假若期待限制只可以在上班时间访问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操作(INSE奇骏T、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;

 AFTE途乐 LOGON ON DATABASE 也可替换为 BEFORE SHUTDOWN ON DATABASE,BEFORE
LOGON ON SCHEMA,ALTE奥迪Q3 LOGON ON SCHEMA等等。

⑧ 、 复合触发器

越多相关内容,可参照官方文书档案:http://docs.oracle.com/cd/E11882\_01/appdev.112/e25519/triggers.htm\#LNPLS020

         

       

 

      

      

 

      

 

相关文章