触发器的小例子

一个表插入数据或标志删除数据时，相应的插入或删除另一个表

create or replace trigger create_base_user3_count
after insert or update of state on base_user3
for each row
DECLARE
begin
  IF INSERTING THEN
       INSERT INTO BASE_USER3_COUNT(id,uuid,bh) VALUES(:NEW.id,:NEW.uuid,:NEW.bh);
  ELSE
       IF :NEW.STATE=1 THEN
          DELETE from BASE_USER3_COUNT WHERE id=:NEW.id;
       END IF;
  END IF;
end;

一个表插入数据或标志删除数据时，相应的插入或删除另一个表
create or replace trigger count_charge
after update of state on ARCHIVES_CHARGE
for each row
DECLARE
begin
IF :NEW.STATE!=:OLD.STATE THEN
  IF :NEW.state=0 THEN
       IF :NEW.archivesclass=1 THEN
          update BASE_USER3_COUNT set CHARGE_G=CHARGE_G+1 WHERE id in
           (select companyid from archives_company_charge where chargeId=:NEW.ID);
       END IF;
       IF :NEW.archivesclass=2 THEN
          update BASE_USER3_COUNT set CHARGE_B=CHARGE_B+1 WHERE id in
           (select companyid from archives_company_charge where chargeId=:NEW.ID);
       END IF;
       IF :NEW.archivesclass=3 THEN
          update BASE_USER3_COUNT set CHARGE_M=CHARGE_M+1 WHERE id in
           (select companyid from archives_company_charge where chargeId=:NEW.ID);
       END IF;
  END IF;
  IF :NEW.state=2 and :OLD.STATE=0 THEN
       IF :NEW.archivesclass=1 THEN
          update BASE_USER3_COUNT set CHARGE_G=CHARGE_G-1 WHERE id in
           (select companyid from archives_company_charge where chargeId=:NEW.ID);
       END IF;
       IF :NEW.archivesclass=2 THEN
          update BASE_USER3_COUNT set CHARGE_B=CHARGE_B-1 WHERE id in
           (select companyid from archives_company_charge where chargeId=:NEW.ID);
       END IF;
       IF :NEW.archivesclass=3 THEN
          update BASE_USER3_COUNT set CHARGE_M=CHARGE_M-1 WHERE id in
           (select companyid from archives_company_charge where chargeId=:NEW.ID);
       END IF;
  END IF;
END IF;
end;

一个表插入中某字段修改时，相应的修改另一个表

create or replace trigger count_company_charge
after DELETE on ARCHIVES_COMPANY_CHARGE
for each row
DECLARE
state1 NUMBER:='';
class varchar2(5 byte):='';
begin
  select c.archivesClass,c.state into class, state1 from archives_charge c where c.id=:old.chargeId;


     IF STATE1=0 THEN
       IF class=1 THEN
          update BASE_USER3_COUNT set CHARGE_G=CHARGE_G-1 WHERE id=:old.companyid;
       END IF;
       IF class=2 THEN
          update BASE_USER3_COUNT set CHARGE_B=CHARGE_B-1 WHERE id=:old.companyid;
       END IF;
       IF class=3 THEN
          update BASE_USER3_COUNT set CHARGE_M=CHARGE_M-1 WHERE id=:old.companyid;
       END IF;
     END IF;

end;

create or replace trigger count_media
after insert or update of state on ARCHIVES_MEDIA
for each row
DECLARE
begin
IF inserting and :new.state=0 THEN
       IF :NEW.archivesclass=1 THEN
          update BASE_USER3_COUNT set MEDIA_G=MEDIA_G+1 WHERE id in
           (select companyid from archives_company_media where mediaId=:NEW.ID);
       END IF;
       IF :NEW.archivesclass=2 THEN
          update BASE_USER3_COUNT set MEDIA_B=MEDIA_B+1 WHERE id in
           (select companyid from archives_company_media where mediaId=:NEW.ID);
       END IF;
       IF :NEW.archivesclass=3 THEN
          update BASE_USER3_COUNT set MEDIA_M=MEDIA_M+1 WHERE id in
           (select companyid from archives_company_media where mediaId=:NEW.ID);
       END IF;
else
IF :NEW.STATE!=:OLD.STATE THEN
  IF :NEW.state=0 THEN
       IF :NEW.archivesclass=1 THEN
          update BASE_USER3_COUNT set MEDIA_G=MEDIA_G+1 WHERE id in
           (select companyid from archives_company_media where mediaId=:NEW.ID);
       END IF;
       IF :NEW.archivesclass=2 THEN
          update BASE_USER3_COUNT set MEDIA_B=MEDIA_B+1 WHERE id in
           (select companyid from archives_company_media where mediaId=:NEW.ID);
       END IF;
       IF :NEW.archivesclass=3 THEN
          update BASE_USER3_COUNT set MEDIA_M=MEDIA_M+1 WHERE id in
           (select companyid from archives_company_media where mediaId=:NEW.ID);
       END IF;
  END IF;
  IF :NEW.state=2 and :OLD.STATE=0 THEN
       IF :NEW.archivesclass=1 THEN
          update BASE_USER3_COUNT set MEDIA_G=MEDIA_G-1 WHERE id in
           (select companyid from archives_company_media where mediaId=:NEW.ID);
       END IF;
       IF :NEW.archivesclass=2 THEN
          update BASE_USER3_COUNT set MEDIA_B=MEDIA_B-1 WHERE id in
           (select companyid from archives_company_media where mediaId=:NEW.ID);
       END IF;
       IF :NEW.archivesclass=3 THEN
          update BASE_USER3_COUNT set MEDIA_M=MEDIA_M-1 WHERE id in
           (select companyid from archives_company_media where mediaId=:NEW.ID);
       END IF;
  END IF;
END IF;
END IF;
end;

create or replace trigger count_company_media
after INSERT OR DELETE on ARCHIVES_COMPANY_MEDIA
for each row
DECLARE
state1 NUMBER:='';
class varchar2(5 byte):='';
NUM NUMBER:='';
begin
   IF INSERTING THEN
      select COUNT(*) INTO NUM from archives_media c where c.id=:new.mediaId;
      IF NUM>0 THEN
         select c.archivesClass,c.state into class, state1 from archives_media c where c.id=:new.mediaId;
         IF STATE1=0 THEN
           IF class=1 THEN
              update BASE_USER3_COUNT set MEDIA_G=MEDIA_G+1 WHERE id=:new.companyid;
           END IF;
           IF class=2 THEN
              update BASE_USER3_COUNT set MEDIA_B=MEDIA_B+1 WHERE id=:new.companyid;
           END IF;
           IF class=3 THEN
              update BASE_USER3_COUNT set MEDIA_M=MEDIA_M+1 WHERE id=:new.companyid;
           END IF;
         END IF; 
      END IF;
   ELSE
     select c.archivesClass,c.state into class, state1 from archives_media c where c.id=:old.mediaId;
     IF STATE1=0 THEN
       IF class=1 THEN
          update BASE_USER3_COUNT set MEDIA_G=MEDIA_G-1 WHERE id=:old.companyid;
       END IF;
       IF class=2 THEN
          update BASE_USER3_COUNT set MEDIA_B=MEDIA_B-1 WHERE id=:old.companyid;
       END IF;
       IF class=3 THEN
          update BASE_USER3_COUNT set MEDIA_M=MEDIA_M-1 WHERE id=:old.companyid;
       END IF;
     END IF;
   END IF;
end;create or replace trigger count_COMMENT
after insert or update of state on ARCHIVES_COMMENT
for each row
DECLARE
begin
IF INSERTING THEN
       IF :NEW.TYPE=1 THEN
          update BASE_USER3_COUNT set COMMENT_G=COMMENT_G+1 WHERE id =:NEW.COMPANYID;
       END IF;
       IF :NEW.TYPE=2 THEN
          update BASE_USER3_COUNT set COMMENT_M=COMMENT_M+1 WHERE id =:NEW.COMPANYID;
       END IF;
       IF :NEW.TYPE=3 THEN
          update BASE_USER3_COUNT set COMMENT_B=COMMENT_B+1 WHERE id =:NEW.COMPANYID;
       END IF;
ELSE
       IF :NEW.STATE!=:OLD.STATE THEN
          IF :NEW.state=0 THEN
             IF :NEW.TYPE=1 THEN
                update BASE_USER3_COUNT set COMMENT_G=COMMENT_G+1 WHERE id =:NEW.COMPANYID;
             END IF;
             IF :NEW.TYPE=2 THEN
                update BASE_USER3_COUNT set COMMENT_M=COMMENT_M+1 WHERE id =:NEW.COMPANYID;
             END IF;
             IF :NEW.TYPE=3 THEN
                update BASE_USER3_COUNT set COMMENT_B=COMMENT_B+1 WHERE id =:NEW.COMPANYID;
             END IF;
          END IF;
          IF :NEW.state=1 THEN
             IF :NEW.TYPE=1 THEN
                update BASE_USER3_COUNT set COMMENT_G=COMMENT_G-1 WHERE id =:NEW.COMPANYID;
             END IF;
             IF :NEW.TYPE=2 THEN
                update BASE_USER3_COUNT set COMMENT_M=COMMENT_M-1 WHERE id =:NEW.COMPANYID;
             END IF;
             IF :NEW.TYPE=3 THEN
                update BASE_USER3_COUNT set COMMENT_B=COMMENT_B-1 WHERE id =:NEW.COMPANYID;
             END IF;
          END IF;
       END IF;
END IF;
end;

create or replace trigger count_company_quality
after update of state on ARCHIVES_company_QUALITY
for each row
DECLARE
begin
IF :NEW.STATE!=:OLD.STATE THEN
  IF :NEW.state=0 THEN
     IF :NEW.CHECKTYPE=1 THEN
       IF :NEW.result=1 THEN
          update BASE_USER3_COUNT set QUALITY_1_G=QUALITY_1_G+1 WHERE id =:NEW.COMPANYID;
       END IF;
       IF :NEW.result=2 THEN
          update BASE_USER3_COUNT set QUALITY_1_B=QUALITY_1_B+1 WHERE id =:NEW.COMPANYID;
       END IF;
     ELSE
       IF :NEW.result=1 THEN
          update BASE_USER3_COUNT set QUALITY_2_G=QUALITY_2_G+1 WHERE id =:NEW.COMPANYID;
       END IF;
       IF :NEW.result=2 THEN
          update BASE_USER3_COUNT set QUALITY_2_B=QUALITY_2_B+1 WHERE id =:NEW.COMPANYID;
       END IF;
    END IF;
  END IF;
  IF :NEW.state=2 and :OLD.STATE=0 THEN
     IF :NEW.CHECKTYPE=1 THEN
       IF :NEW.result=1 THEN
          update BASE_USER3_COUNT set QUALITY_1_G=QUALITY_1_G-1 WHERE id =:NEW.COMPANYID;
       END IF;
       IF :NEW.result=2 THEN
          update BASE_USER3_COUNT set QUALITY_1_B=QUALITY_1_B-1 WHERE id =:NEW.COMPANYID;
       END IF;
     ELSE
       IF :NEW.result=1 THEN
          update BASE_USER3_COUNT set QUALITY_2_G=QUALITY_2_G-1 WHERE id =:NEW.COMPANYID;
       END IF;
       IF :NEW.result=2 THEN
          update BASE_USER3_COUNT set QUALITY_2_B=QUALITY_2_B-1 WHERE id =:NEW.COMPANYID;
       END IF;
    END IF;
  END IF;
END IF;
end;

create or replace trigger count_lending
after update of state,archivesClass on ARCHIVES_LENDING
for each row
DECLARE
begin
  IF :NEW.state=0 and :OLD.STATE!=0 THEN
       IF :NEW.archivesclass=1 THEN
          update BASE_USER3_COUNT set LENDING_G=LENDING_G+1 WHERE id =:NEW.BORROWUNITID;
       END IF;
       IF :NEW.archivesclass=2 THEN
          update BASE_USER3_COUNT set LENDING_B=LENDING_B+1 WHERE id =:NEW.BORROWUNITID;
       END IF;
       IF :NEW.archivesclass=3 THEN
          update BASE_USER3_COUNT set LENDING_M=LENDING_M+1 WHERE id =:NEW.BORROWUNITID;
       END IF;
  END IF;
  IF :NEW.state=2 and :OLD.STATE=0 THEN
       IF :NEW.archivesclass=1 THEN
          update BASE_USER3_COUNT set LENDING_G=LENDING_G-1 WHERE id =:NEW.BORROWUNITID;
       END IF;
       IF :NEW.archivesclass=2 THEN
          update BASE_USER3_COUNT set LENDING_B=LENDING_B-1 WHERE id =:NEW.BORROWUNITID;
       END IF;
       IF :NEW.archivesclass=3 THEN
          update BASE_USER3_COUNT set LENDING_M=LENDING_M-1 WHERE id =:NEW.BORROWUNITID;
       END IF;
  END IF;
  IF :NEW.archivesClass!=:OLD.archivesClass and :NEW.STATE=0 and :old.state=0 THEN
       IF :NEW.archivesclass=1 THEN
          update BASE_USER3_COUNT set LENDING_G=LENDING_G+1 WHERE id =:NEW.BORROWUNITID;
       END IF;
       IF :NEW.archivesclass=2 THEN
          update BASE_USER3_COUNT set LENDING_B=LENDING_B+1 WHERE id =:NEW.BORROWUNITID;
       END IF;
       IF :NEW.archivesclass=3 THEN
          update BASE_USER3_COUNT set LENDING_M=LENDING_M+1 WHERE id =:NEW.BORROWUNITID;
       END IF;
       IF :OLD.archivesclass=1 THEN
          update BASE_USER3_COUNT set LENDING_G=LENDING_G-1 WHERE id =:NEW.BORROWUNITID;
       END IF;
       IF :OLD.archivesclass=2 THEN
          update BASE_USER3_COUNT set LENDING_B=LENDING_B-1 WHERE id =:NEW.BORROWUNITID;
       END IF;
       IF :OLD.archivesclass=3 THEN
          update BASE_USER3_COUNT set LENDING_M=LENDING_M-1 WHERE id =:NEW.BORROWUNITID;
       END IF;
  END IF;
end;

create or replace trigger count_PROPERTY
after update of state on ARCHIVES_PROPERTY
for each row
DECLARE
begin
IF :NEW.STATE!=:OLD.STATE THEN
  IF :NEW.state=0 THEN
       update BASE_USER3_COUNT set PROPERTY=PROPERTY+1 WHERE id =:NEW.companyid;
  END IF;
  IF :NEW.state=2 and :OLD.STATE=0 THEN
       update BASE_USER3_COUNT set PROPERTY=PROPERTY-1 WHERE id =:NEW.companyid;
  END IF;
END IF;
end;

create or replace trigger count_QUALIFICATION
after update of state on ARCHIVES_QUALIFICATION
for each row
DECLARE
begin
IF :NEW.STATE!=:OLD.STATE THEN
  IF :NEW.state=0 THEN
       update BASE_USER3_COUNT set QUALIFICATION=QUALIFICATION+1 WHERE id =:NEW.companyid;
  END IF;
  IF :NEW.state=2 and :OLD.STATE=0 THEN
       update BASE_USER3_COUNT set QUALIFICATION=QUALIFICATION-1 WHERE id =:NEW.companyid;
  END IF;
END IF;
end;

create or replace trigger count_verdict
after update of state on ARCHIVES_VERDICT
for each row
DECLARE
begin
IF :NEW.STATE!=:OLD.STATE THEN
  IF :NEW.state=0 THEN
       IF :NEW.verdictResult=0 THEN
          update BASE_USER3_COUNT set VERDICT_G=VERDICT_G+1 WHERE id in
           (select nameid from archives_verdict_company where type=0 and verdictId=:NEW.ID);
       END IF;
       IF :NEW.verdictResult=1 THEN
          update BASE_USER3_COUNT set VERDICT_G=VERDICT_G+1 WHERE id in
           (select nameid from archives_verdict_company where type=1 and verdictId=:NEW.ID);
       END IF;
  END IF;
  IF :NEW.state=2 and :OLD.STATE=0 THEN
       IF :NEW.verdictResult=0 THEN
          update BASE_USER3_COUNT set VERDICT_G=VERDICT_G-1 WHERE id in
           (select nameid from archives_verdict_company where type=0 and verdictId=:NEW.ID);
       END IF;
       IF :NEW.verdictResult=1 THEN
          update BASE_USER3_COUNT set VERDICT_G=VERDICT_G-1 WHERE id in
           (select nameid from archives_verdict_company where type=1 and verdictId=:NEW.ID);
       END IF;
  END IF;
END IF;
end;

create or replace trigger base_user
before insert on base_user3
for each row
DECLARE
xh varchar2(20);
begin
   select seq_user3bh.nextval into xh from dual;
   while (xh like '%1111%' or xh like '%2222%' or xh like '%3333%' or xh like '%4444%' or xh like '%5555%'
       or xh like '%6666%' or xh like '%7777%' or xh like '%8888%' or xh like '%9999%' or xh like '%0000%') LOOP
         select seq_user3bh.nextval into xh from dual;
      end LOOP;
  select seq_user3bh.CURRVAL into :new.bh from dual;
end;