Oracle中的觸發器TRIGGER

標籤：when   end   except   to_char   觸發器   ldl   conf   _id   for   

 1 CREATE OR REPLACE TRIGGER trgregisterpregroup 2   BEFORE UPDATE 3   ON tblregisterusers 4   FOR EACH ROW 5  6 DECLARE 7 sort_id varchar2(2000); 8 e_code  VARCHAR2(1000); 9 BEGIN10       IF :NEW.fldleftmoney <> :OLD.fldleftmoney THEN11         select to_char(systimestamp(3),‘YYYYMMDDHH24MISSFF‘) into sort_id from dual;12         e_code := NULL;13         for R in (select a.fldusergroupid,14                        a.fldx,15                        a.fldy,16                        a.fldz,17                        a.fldserviceaid,18                        a.fldservicebid,19                        a.fldservicecid,20                        a.flag21                   from usergroupautoconfig a22                  where a.fldusergroupid = :NEW.fldusergroupid and a.flag=1)23           loop24             if :NEW.fldleftmoney > R.fldz then25               if R.fldservicecid is not null then26                  e_code := PK_INTERFACE.user_pregroup(:NEW.FLDUSERNAME,R.fldservicecid,sysdate,9000,sort_id);27               end if;28             elsif (:NEW.fldleftmoney <= R.fldz) and (:NEW.fldleftmoney > R.fldy) then29               if R.fldservicebid is not null then30                  e_code := PK_INTERFACE.user_pregroup(:NEW.FLDUSERNAME,R.fldservicebid,sysdate,9000,sort_id);31               end if;32             else33               if R.fldserviceaid is not null then34                  e_code := PK_INTERFACE.user_pregroup(:NEW.FLDUSERNAME,R.fldserviceaid,sysdate,9000,sort_id);35               end if;36             end if;37           end loop;38        end if;39     if e_code <> pk_common.E00 then40           PK_PUBLIC.error_log(‘trgregisterpregroup: ‘ || :NEW.flduserid || ‘-‘ || e_code);41         end if;42      EXCEPTION43       WHEN OTHERS THEN44         NULL;45 END trgregisterpregroup;

 

Oracle中的觸發器TRIGGER