oracle触发器学习(一)

触发器定义大小最大为32KB，如果太大的话，可以通过调用函数和过程，支持12种方式

after、before{insert、update、delete（for each row）}, 不支持commit,rollback事务处理和ddl语句

when 中用new,old不带“：”,在begin end块中用：new ：old,new和old代表新的一行和以前的一行

如需要某个字段的值用：new.column ：old.column

启用和禁用触发器

alter trigger biu_emp_manager disable;
alter trigger biu_emp_manager enable;

 

/**********************************************#通过触发器实现inserting,updating,deleting操作时#比如在写日志加上操作类型***********************************************/create or replace trigger biud_employee  before insert or update or delete on empdeclare  v_type varchar2(100);  v_name emp.ename%type = 'jack';begin  if inserting then    v_type := 'insert';  elsif updating then    v_type := 'update';  elsif deleting then    v_insert := 'delete';  end if;  insert into log    (date, name, type)  values    (to_char(sysdate, 'yyyymmdd'), v_name, v_type);end;//**********************************************#通过触发器referecing为实现为old和new起别名#isert只有new,delete只有old,update即有old也有new***********************************************/create or replace trigger biufr_emp  before insert or update of sal on emp  referencing old as old_value new as new_value  for each row  when new_value.sal < 800;begin  :new_value.comm := 0;end;//********************************************# 通过触发器实现主键自增********************************************/create or replace trigger biur_emp_pk  before insert on empty  for each rowbegin  select seq_emp.nextval into :new.empid from dual;end;/********************************************# 员工和其经理对应的一个视图********************************************/create or replace view v_emp_manager asselect b.empno,       b.ename,       b.job,       b.hiredate,       b.sal,       b.mgr      mgr_no,       a.ename    mgr_ename,       a.job      mgr_job,       a.hiredate mgr_hiredate,       a.sal      mgr_sal  from scott.emp a,       (select empno, ename, job, mgr, hiredate, sal          from scott.emp         start with mgr is null        connect by prior empno = mgr) b where a.empno = b.mgr;/**********************************************# 触发器，现在通过mgr_no更新mgr_ename*********************************************/create or replace trigger biu_emp_manager  instead of update on v_emp_managerbegin    update scott.emp       set ename    = :new.mgr_ename,           job      = :new.mgr_job,           hiredate = :new.mgr_hiredate,           sal      = :new.mgr_sal     where empno = :old.mgr_no ;end;--触发器中:old.mgr_no会用上次的mgr_no的值去更新mgr字段，无法实现用empno直接更新mgr_ename和其它mgr字段update v_emp_manager set mgr_ename=lower('JONES'), mgr_job=lower('ANALYST') where empno=7566;--直接用mgr_no的值去更新mgr_ename和其它mgr字段update v_emp_manager set mgr_ename=lower('KING') where mgr_no=7839;

/*****************************************#类别：系统事件触发器#内容：数据库启动、关闭、服务器错误等#example:数据库启动****************************************/create or replace trigger ad_startup  after startup on databasebegin  dbms_output.put_line('database started');end;/**********************************************************************#类别:用户事件触发器#内容：用户登录、注销#craete/alter/drop/analyze/audit/grant/revoke/rename/truncate/logoff#example:对schema进行drop操作后录日志***********************************************************************/create or replace trigger bdrop_emp_copy  before drop on schemabegin  insert into drop_objects    (object_name, object_type, oper_time)  values    (ora_dict_obj_name, ora_dict_obj_type, sysdate);end;--创建记录日志表create table drop_tables(  object_name varchar2(100),  object_type varchar2(100),  oper_time date); --定义一个object的typecreate or replace type obj_emp as object (  empno number(9),  empname varchar2(32),  hiredate date);--定义一个object的嵌套表create type typ_emp is table of obj_emp;drop type typ_emp;drop type obj_emp;create table emp_copy as select * from scott.emp;drop table emp_copy;select * from drop_objects