关于触发器

 DML触发器

语句触发器：当执行DML语句时被隐含执行的触发器
语法： create (or replace) trigger t_update_A
       {BEFORE| AFTER } EVENT1  [OR EVENT2 OR EVENT3]
       ON TABLE_NAME
       pL/SQL block;

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

行触发器-----当执行DML语句时，每作用一行被触发一次的触发器，当使用DML语句触发器时，
              不能记录列数据的变化，为了审计表数据的变化，就需要使用行触发器

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

限制行触发器：-----在特定条件下执行行触发器，需要使用When子句对触发条件加以限制，
  （下面以审计岗位salesman的雇员工资变化为例）
eg:  create or replace trigger  tr_sal_change
      after update or 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;

 

 

eg:

     

CREATE TRIGGER after_IQC_Inspection 
 after insert on iqcm.iqc_inspection
 for each row
declare
 v_specid iqc_spec.id%type;
 v_subclassno iqc_spec.subclassno%type;
 
 --??????
 v_plant iqc_inspec_set.plant%type;
 v_grade iqc_inspec_set.geadeno%type;
 v_methodno iqc_inspec_set.methodno%type;
 v_countaql iqc_inspec_set.countaql%type;
 v_methodno_jl  iqc_inspec_set.measure_methodno%type;
begin

     --?????????
    
     declare cursor acc_cursor is
             select y.plant,y.geadeno,y.methodno,y.measure_methodno,y.countaql  
             from iqc_inspec_set y
             where y.plant=trim(:new.plant) and y.p_no=trim(:new.p_no);          
    begin
         open acc_cursor;
              fetch acc_cursor into v_plant,v_grade,v_methodno,v_methodno_jl,v_countaql;
         close acc_cursor;
    end;
                       
     --??????????
     declare cursor mycursor1 is
     select a.id,a.subclassno from iqc_spec a where exists
     (select b.specid from iqc_inspec_execute_spec b
             where b.specid=a.id and b.executeid=trim(:new.executeid));
     begin
     open mycursor1;
         
     fetch mycursor1 into v_specid,v_subclassno;
     while mycursor1%found  
         
     loop
    
         --???????? AQL,???? 
         if (v_subclassno<3) then
            insert into iqc_lot_info (stutasid,subclassno,spec_id,grade_no,method_no,acc_aql,plant)
            values(:new.id,v_subclassno,v_specid,v_grade,v_methodno,v_countaql,v_plant);
         else
             insert into iqc_lot_info (stutasid,subclassno,spec_id,method_no,plant)
             values(:new.id,v_subclassno,v_specid,v_methodno_jl,v_plant);
         end if;
         
         fetch mycursor1 into v_specid,v_subclassno;
         
     end loop;
     close mycursor1;
     end;
    
     --?????????
     insert into iqc_lot_info (stutasid,subclassno,spec_id,plant)
     values(:new.id,0,'F590039595F758E1E030850A6382459B',v_plant);
 
     --?????????????????
     insert into iqc_check (stutasid,subclassno,spec_id)
     values(:new.id,0,'F590039595F758E1E030850A6382459B');
    

     --dbms_output.put_line(v_plant||v_grade||v_mothod||v_countaql);
    
end;