Oracle中的触发器TRIGGER

 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;