create or REPLACE TRIGGER SYNHBLAREA before insert or update
ON “op_hbl”
FOR EACH ROW
DECLARE
CURSOR curfuction(orgId VARCHAR2) is select * from (select SYS_OFFICE_ID,OFFICE_FULL_NAME from “sys_office” s START WITH s.SYS_OFFICE_ID = orgId CONNECT BY PRIOR s.PARENT_OFFICE_ID = s.SYS_OFFICE_ID) where SYS_OFFICE_ID in (‘6482276542714318848’,‘6482276480240160768’,‘6482276522355167232’,‘6482276580383363072’,‘6482276632556310528’);
emp_record curfuction%rowtype;
PRAGMA AUTONOMOUS_TRANSACTION;
begin
open curfuction(:new.ORG_ID);
loop
fetch curfuction into emp_record;
exit when curfuction%notfound;
if :old.HBL_AREA_NAME is null then
begin
:new.HBL_AREA_NAME := emp_record.OFFICE_FULL_NAME;
end;
end if;
end loop;
close curfuction;
END;