欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracle 触发器

程序员文章站 2022-05-07 14:17:14
...

---before 触发器  在 dml 操作之后-----------------

   create or replace trigger tr_sec_emp
   before
     insert into update or delete on emp
   begin
     if to_char(Sysdate,’Dy') in ('星期六',’星期日') then
        raise_application_error(-2000,’不能在休息的日修改员工的信息');
    end if;
   end;

--使用 条件谓词
   inserting   updating   deleting   
  

   create or replace trigger tri_sce_emp
   before
       insert or update or delete on emp
     begin
       if to_char(sysdate,’Dy') in ('星期六','星期日') then
         case
                when  updating then
                      raise_application_error(-2000,'不能在休息日更员工的信息');
                when  deleting then
                      raise_application_error(-2001,'不能在休息日删除员工的信息');
                when  inserting then
                      raise_appllication_error(-2003,'不能在休息日插入员工的信息');  
         end  case;
      end if;
    end ;
      
-- if updating('sal') then
     ----执行操作
  end if;


--使用  after 触发器   在DMl中 之后
   
create table audit_table  创建审计表 
(
	id number primary key,
	tb_name varchar2(20) not null,
        ins number not null,
        upd number not null,
        del number not null,
        starttime date,
        endtime date

);

create or replace trigger tri_sce_emp
after 
  insert or update or delete on emp 
  
  declare
     v_temp number;
    begin
      select count(*) into v_temp from audit_table where tb_name='emp';
     
     if v_temp=0 then
          insert into audit_table values(seq_audit.Nextval,'emp',0,0,0,sysdate,null);
    end if;
     case
        when inserting then
          update audit_table set ins=ins+1,endtime=sysdate where tb_name='emp';
        when updating then
          update audit_table set upd=upd+1,endtime=sysdate where tb_name='tmp';
       when deleting then
         update audit_table set del=del+1,endtime=sysdate  where tb_name='emp';
    end case;
  end;


---行触发器(before) :new  中的数据是可以更新的额   :old 中的数据是只读的 
   create or replace trigger tir_emp_sal
     before update of sal on emp
     for each row
   begin
     if :new.sal<:old.sal then
        raise_application(-20000,'新工资不能小于原有工资');
     end if
  end;
    

--after 行级触发器(为了审计DML操作)

create table audit_sal_change
(
	id number primary key,
 	ename varchar2(20) not null,
        oldsal number(7,2) not null,
        newsal number(7,2) not null,
        auditTime Date
);

  create or replace trigger tri_sal_change
  after update of sal on emp
  for each row
  begin
     insert into audit_sal_change values(seq_audit_sal_change.nextVal,:old.ename,:old.sal,:new.sal,sysdate);
  end;


--限制行触发器
   create or replace trigger tri_sal_change
   after update of sal on emp
   for each row
   when (old.job='manager')
   begin
      insert into audit_sal_changes values(seq_audit_sal_chagne.NextVal,:old.ename,:old.sal,:new.sal,sysdate);
   end;


--为复杂视图创建触发器  Insread Of
  create or replace VIEW view_dept_emp as select a.deptno,a.dname,b.empno,b.ename from dept a,emp b whre a.deptno=b.deptno;

  create or replace trigger tr_inserted_of_dept_emp 
  Instead Of  insert on view_dept_emp
  for each row
  declare
     v_temp number;
  begin
     select count(*) into v_temp form dept where deptno=:new.deptno;
   if v_temp=0 then
     insert into dept(deptno ,dname) values (:new.deptno,:new.dname);
  end if;
  
  select count(*)  into v_temp from emp where empno=:new.empno;
  if v_temp=0 then
     insert int emp(empno,ename) values(:new.empno,:new.ename);
  end if;
end;

---系统事件触发器
   
   --创建例程启动和关闭的表
    create table event_table
   (
       event varchar2(20),
        event_time date 
     )

   --例程启动  只能是 after   关闭是 before
     
   create or replace trigger tri_startup
   after startup on database
   begin
      insert into event_table values(ora_sysevent,SySDate); 
   end;

   create or replace trigger tri_shutdown
   before SHUTDOWN on database
   begin
      insert into event_table values(ora_sysevent,sysdate);
   end;


--登录  after和退出触发器 before
   --创建表
    crreate  table log_table
   (
      username varchar2(20),
       logon_time date,
       logoff_time date,
       Ip varchar2(20)
     );
    
    --登录
      create or replace trigger tr_logon
      after logon on database
      when (ora_login_user not in ('sys','sysyman'))
      begin
          insert into log_table (username,logon_time,ip) vlues (ora_login_user,sysydate,ora_client_ip_address);
      end;

   -- 退出
      create or replace trigger lon_off
      before  logoff  on database when (ora_login_user not in ('sys','sysMan'))
      begin
         insert int log_table (userName,log_off_time,ip) values (ora_login_user,sysdate,ora_client_ip_address);
      end;

 
---DDL 触发器
    
  --创建存储DDL事件的表
    craete table evetn_ddl
    (
        event varchar2(20),
       userName varchar2(20),
       owner varcchar2(20),
      objtype varcharr2(20),
      oibname  varchar2(30),
      ddl_time date
    );
    
    create or replace trigger tri_ddl
    after ddl on scott.schema
    begin
       insert int event_ddl values(ora_sysyevent,ora_login_user,ora_dict_object_ower,ora_dict_bj_name,ora_dict_pnj_type,sysdate);
    end;



---管理触发器
    --显示触发器的信息
      select trigger_name,status from user_triggers where table_name=‘emp’;
   --禁止 启用触发器
     
  alter trigger tri_sal_change disable;
  

  alter trigger tr_sal_change enable; --启用触发器

  alter  table emp enable all triggers;


--重新编译触发器

  --当使用 alter table 修改表的结构时候, 会是触发器无效
    alter trigger tri_sal_change compile;

 --删除触发器
    drop trigger tr_sal_change;
相关标签: Oracle trigger