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 BEGIN 10 IF :NEW.fldleftmoney <> :OLD.fldleftmoney THEN 11 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.flag 21 from usergroupautoconfig a 22 where a.fldusergroupid = :NEW.fldusergroupid and a.flag=1) 23 loop 24 if :NEW.fldleftmoney > R.fldz then 25 if R.fldservicecid is not null then 26 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) then 29 if R.fldservicebid is not null then 30 e_code := PK_INTERFACE.user_pregroup(:NEW.FLDUSERNAME,R.fldservicebid,sysdate,9000,sort_id); 31 end if; 32 else 33 if R.fldserviceaid is not null then 34 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 then 40 PK_PUBLIC.error_log('trgregisterpregroup: ' || :NEW.flduserid || '-' || e_code); 41 end if; 42 EXCEPTION 43 WHEN OTHERS THEN 44 NULL; 45 END trgregisterpregroup;