CREATE OR REPLACE TRIGGER trgregisterpregroup
BEFORE UPDATE
ON tblregisterusers
FOR EACH ROW
DECLARE
sort_id varchar2(2000);
e_code VARCHAR2(1000);
BEGIN
IF :NEW.fldleftmoney <> :OLD.fldleftmoney THEN
select to_char(systimestamp(3),YYYYMMDDHH24MISSFF) into sort_id from dual;
e_code := NULL;
for R in (select a.fldusergroupid,
a.fldx,
a.fldy,
a.fldz,
a.fldserviceaid,
a.fldservicebid,
a.fldservicecid,
a.flag
from usergroupautoconfig a
where a.fldusergroupid = :NEW.fldusergroupid and a.flag=1)
loop
if :NEW.fldleftmoney > R.fldz then
if R.fldservicecid is not null then
e_code := PK_INTERFACE.user_pregroup(:NEW.FLDUSERNAME,R.fldservicecid,sysdate,9000,sort_id);
end if;
elsif (:NEW.fldleftmoney <= R.fldz) and (:NEW.fldleftmoney > R.fldy) then
if R.fldservicebid is not null then
e_code := PK_INTERFACE.user_pregroup(:NEW.FLDUSERNAME,R.fldservicebid,sysdate,9000,sort_id);
end if;
else
if R.fldserviceaid is not null then
e_code := PK_INTERFACE.user_pregroup(:NEW.FLDUSERNAME,R.fldserviceaid,sysdate,9000,sort_id);
end if;
end if;
end loop;
end if;
if e_code <> pk_common.E00 then
PK_PUBLIC.error_log(trgregisterpregroup: || :NEW.flduserid || - || e_code);
end if;
EXCEPTION
WHEN OTHERS THEN
NULL;
END trgregisterpregroup;
来源: http://www.bubuko.com/infodetail-2489519.html