触发器

mei触发器可以简单的分为：

dml 触发器：主要是针对于对表中记录的update，insert ， delete 操作

·表级触发器

    ·行级触发器：对于每一条记录都器作用

 

系统触发器：主要是真的oracle系统作用，如用户登录，注销的操作等

 

ddl 触发器：主要是真的在create table , drop table , create sequence , create procedure,,drop               procedure ...数据定义语言的操作

 

 

对于触发器，和储存过程很像，但是触发器不能有传入，传出参数 ，不交给用户调用，而是由oracle本身来执行

 

dml 触发器

 

1, 建立dml 触发器

 

create  or replace  trigger  tri_name

{before |after } 

{insert | delete| update [of  column1,column2...]}on {[schema.]tablename}

[ for eachrow]

begin

... 执行代码;

end;

 

 

? 先新建一张测试表 my_emp, 如果my_emp里面新增一条记录叫打印出一条‘添加成功’

 

create or replace trigger myTri1

 afterinsert on  hwt.my_emp

begin

   dbms_output.put_line('添加成功');

end;

 

当像my_emp中添加一条数据的时候，就会打印出一条信息‘添加成功’，但是如果我一次像表中添加四条数据的话，还是只打印出一条信息，因为这是表级的触发器

create or replace trigger myTri2

after updateon hwt.my_emp

for each row --行级触发器，对于每一行都要进行触发操作

begin

    dbms_output.put_line('添加了一条记录');

end;

这样的话，一次性插入4条记录就会提示4条信息

 

? 在休息日（星期六，星期日）不能操作my_emp表

create or replace trigger myTri3

before deleteon hwt.my_emp

begin

     if(to_char(sysdate,'dy')in ('星期六','星期天')) then

     raise_application_error(-20112,'休息日不能修改员工');--抛出一个错误，使得在这里终止，raise_application_error(错误的号，错误信息);错误的号是（-20000~ -20999）

     end if;

end;

 

2, 谓词 update -> updating , delete -> deleting ,insert -> inserting

可以通过谓词来对操作的类型进行使用

如：

create or replace trigger myTri4

before updateor insert on hwt.my_emp

begin

    case

    when updating then --如果是更新操作

    dbms_output.put_line('更新数据');

    when  insertingthen -- 如果是增加操作

    dbms_output.put_line('添加数据');

    end case;

end;

 

3，:new 和 :old 来取出新数据和旧数据

值得注意的是 :new:old 的数据必须要在foreach row 中才能使用

 

? 更新my_emp表中的员工的工资时候，工资不能比以前的工资低，但是也不能超过原来工资的20%，在插入新数据的时候，员工的工资不能低于1000；

create or replace trigger myTri5
before insert or update on hwt.my_emp
for each row --值得注意的是，:new :old 必须要在行级触发器中使用
begin
     case
          when updating then
               if (:new.sal < :old.sal or :new.sal > :old.sal*1.2) then
                 raise_application_error(-20001,'更新的工资不能比原来的工资低或者超过20%');
               else
                  dbms_output.put_line('更新成功');
               end if;
          when inserting then
               if :new.sal < 1000 then
                 raise_application_error(-20002,'工资不能低于1000');
               else
                   dbms_output.put_line('添加成功');
               end if;
     endcase;
end;

 

？当对my_emp进行删除操作的时候，自动备份到另一张表中（my_emp_bak）

create or replace trigger myTri6
before delete on my_emp
for each row -- 只要使用了:old 或者 :new 就要是行级触发器
begin
     insertinto my_emp_bak
     values(:old.ename,:old.sal);
end;

 

 

系统触发器

系统触发器主要是针对数据的logon/ logoff 或者 startup /shutdown 等操作，要注意的是对于系统触发器，一般是有sys用户来建立的，常用的系统属性有：

ora_login_user      返回登陆名

ora_client_ip_address         返回客户端的IP

ora_sysevent             返回触发器触发的系统事件

ora_database_name             返回数据的名字

ora_des_encrypeted_password     返回加密的密码

 

语法如下：

create [or replace] trigger trigger_name

{ before/ after}{logon/logoff/startup/shutdown}  on database

begin

执行代码;

end;

 

？在用户登陆或者在用户注销的时候加入到login_info

--先建立login_info表(username,login_time, logoff_time, address)

create or replacetrigger login_tri
after logon on database
begin
   insertintologin_info(username,login_time,address)
    values(ora_login_user,sysdate,ora_client_ip_address);
end;

 

 

ddl 触发器

 

ddl 是数据定义语言的触发器，主要是针对于create ... drop ... 等等操作，对于ddl触发器也一般是由sys用户创建的

 

基本语法：

create [or  replace]  trigger  mytri_name

after ddl on  [方案名].schema

begin

... 操作代码;

end;