触发器

oracle中存储的层次结构如下：
 1)数据库由一个或多个表空间组成。
 2)表空间由一个或多个数据文件文件组成，一个表空间包含段。
 3)段(表，索引等)由一个或多个盘区组成。段存在于表空间中,但在表空间中可以有许多数据文件中的数据。
 4)盘区是在磁盘上连续快的组，一个盘区在一个表空间中，而且，总是在表空间中的单一文件中。
 5)快是在数据库中最小的分配单元。快是数据库使用的最小的I/O单元。
 
 
select a.name,b.value from v$statname a,v$mystat b
 where a.STATISTIC# = b.STATISTIC#
 and a.name like '%ga%'
 
 NAME                         VALUE
 session uga memory          1216836
 session uga memory max      1216836
 session pga memory          1957460
 session pga memory max      1957460
 calls to kcmgas              20

 

 

触发器:
create or replace trigger tr_sec_emp
  before insert or update or delete on emp
declare   
begin
  if to_char(sysdate,'DY','nls_date_language = AMERICAN') IN ('SAT','SUN') then
    raise_application_error(-20001,'不能在休息日,更改雇员信息!');
  end if;
end tr_sec_emp;

create or replace trigger tr_trigger_emp
  before insert or update or delete on emp
declare
begin
  if to_char(sysdate,'DY','nls_date_language=AMERICAN') in ('SAT','SUN') then
    case when inserting then
      raise_application_error(-20001,'在休日不能增加雇员信息');
    when updating then
      raise_application_error(-20002,'在休日不能更新雇员信息'); 
    when deleting then
      raise_application_error(-20002,'在休日不能删除雇员信息');
     end case;
   end if;      
end tr_trigger_emp;

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

create or replace trigger tr_emp_sal
  before update of sal on emp
  for each row
declare 
begin
  if :new.sal < :old.sal then
     raise_application_error(-20010,'工资只涨不降');
   end if;    
end tr_emp_sal;

create or replace trigger tr_sal_chang
  after update of sal  on  emp
  for each row
declare
  v_temp int;
begin
  select count(*) into v_temp from audit_emp_change where name = :old.ename;
  if v_temp = 0 then
     insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate);
  else
     update audit_emp_change set oldsal = :old.sal,newsal = :new.sal,time = sysdate where name = :old.ename;
  end if;  
end tr_sal_chang;

限制行触发器
create or replace trigger tr_sal_change
  after update of sal on emp
  for each row
  when (old.job = 'SALESMAN')
declare
  v_temp int; 
begin
  select count(*) into v_temp from audit_emp_change where name = :old.ename;
  if v_temp = 0 then
     insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate);
  else
     update audit_emp_change set oldsal = :old.sal,newsal = :new.sal,time=sysdate where name = :old.ename;
  end if;       
end tr_sal_change;

对视图的触发器
create or replace trigger tr_instead_of_dept_emp instead of insert on dept_emp  
  for each row
declare
 v_temp int;
begin
  select count(*) into v_temp from 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 into emp(empno,ename,deptno) values(:new.empno,:new.ename,:new.deptno);
  end if;   
end tr_instead_of_dept_emp;

系统触发器
create or replace trigger tr_startup
  after startup on database
declare 
begin
  insert into event_table values(ora_sysevent,sysdate);
end tr_startup;

create or replace trigger tr_logoff
  before logoff on database 
declare

begin
  insert into log_table(username,logon_time,address) values(ora_login_user,sysdate,ora_client_ip_address);
end tr_logoff;

create or replace trigger tr_logon
  after logon  on database 
declare

begin
  insert into log_table(username,logon_time,address) values(ora_login_user,sysdate,ora_client_ip_address); 
end tr_logon;

create or replace trigger tr_ddl
  after DDL on bms.schema 
declare

begin
  insert into event_ddl values(ora_sysevent,ora_login_user,ora_dict_obj_owner,
  ora_dict_obj_name,ora_dict_obj_type,sysdate); 
end tr_ddl;

 

二、当一个A表，插入数据，删除数据，更新数据，数据要保留原来数据到B表，插入的话，也同样插入到B表

create or replace trigger a_trigger
  before delete or update or insert on  a
  for each row
declare
begin
  if updating then
     insert into b values(:old.id,sysdate);
  elsif deleting then
      insert into b values(:old.id,sysdate);   
  elsif inserting then      
      insert into b values(:new.id,sysdate);
  end if;
end a_trigger;

 

create or replace trigger tt
  after insert or delete or update on t1
  for each row
declare
 
begin
  if inserting then
    insert into t2 values(:new.code,:new.p_code,:new.captiion);
  elsif updating then    
    update t2 set t1_pcode = :new.code where t1_pcode = :old.code;
    update t2 set t1_code = :new.code where t1_code = :old.code;
  elsif deleting then
    delete from t2 where t1_code in(select t1_code from t2 start with t1_code = :old.code
           connect by prior t1_code = t1_pcode);
   end if;       
  exception when others then
    raise_application_error(-20020,'触发器错误!');
commit;
end tt;

 

自治事务

create or replace trigger insert_tri
  after insert on zyj
  for each row
declare
pragma autonomous_transaction;
begin 
  if(:new.xmlb = '措施') then
       update zyj set zyzl='优质' where jh = :new.jh and rq = (select max(rq) from zyj where jh = :new.jh);
       commit;   
  end if;
end insert_tri;

 

解决变异表问题

--采取变量赋值的方法可以阻止变异表的事情，下面是例子
[code=SQL]
SQL> create table examscore(学号 varchar2(4) not null,
  2  姓名 varchar2(20),
  3  课程代码 varchar2(4) not null,
  4  课程名称 varchar2(30),
  5  期中成绩 number(3),
  6  期末成绩 number(3),
  7  总成绩 number(3)
  8  )
  9  /

表已创建。

SQL> insert into examscore values('001','王军','058','计算机',60,80,140)
  2  /

已创建 1 行。

SQL> commit;

提交完成。

SQL> create or replace trigger SumScoreTri
  2  before insert or update on examscore for each row
  3  declare
  4  v_sum examscore.总成绩%type;
  5  begin
  6  v_sum:=:new.期中成绩+:new.期末成绩;
  7  if v_sum<>:new.总成绩 then
  8  :new.总成绩:=v_sum;
  9  end if;
10  end;
11  /

触发器已创建

SQL> update examscore set 期末成绩=60 where 姓名='王军'
  2  /

已更新 1 行。

SQL> commit;

提交完成。

SQL> select * from examscore
  2  /

学号 姓名                 课程 课程名称                         期中成绩   期末成绩     总成绩
---- -------------------- ---- ------------------------------ ---------- ---------- ----------
001  王军                 058  计算机                                 60         60        120

带条件的触发器

create or replace trigger trg_i_u
before insert or update on table_name
when (:old.a is null or :new.a=:old.a)
is
....
....

一、

表test中字段id，name，tablename
其中字段tanblename中存储的是需要插入的表名，建立以下触发器

create or replace trigger tri_i_test 
before insert on test
for each row
declare  
sql_str varchar2 ( 100 );
begin
     if :new.ID > 1 then
        sql_str: = ' insert into ' || :new.tablename || ' (col1,col2)
         values ( ' || :new.id || ' , ''' || :new.tablename || ''' ) ' ;
        execute immediate sql_str;
     end if ;
end ;