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;
