触发器关联自己的表

CREATE OR REPLACE TRIGGER tr_insert_project_tmp
  BEFORE UPDATE ON project
  FOR EACH ROW
  WHEN (NEW.status = 'FINISH_REPAY_PLAN')
declare
  --id                project_tmp.id%type;
  bank_sn           project_tmp.bank_sn%type;
  lend_date         project_tmp.lend_date%type;
  project_no        project_tmp.project_no%type;
  contract_no       project_tmp.contract_no%type;
  name              project_tmp.name%type;
  identity_card_no  project_tmp.identity_card_no%type;
  product_type      project_tmp.product_type%type;
  apply_credits     project_tmp.apply_credits%type;
  fee_rate          project_tmp.fee_rate%type;
  service_fee       project_tmp.service_fee%type;
  monthly_other_fee project_tmp.monthly_other_fee%type;
  loan_duration     project_tmp.loan_duration%type;
  repay_day         project_tmp.repay_day%type;
  first_repay_date  project_tmp.first_repay_date%type;
  account_bank      project_tmp.account_bank%type;
  account_name      project_tmp.account_name%type;
  account_no        project_tmp.account_no%type;
  project_use       project_tmp.project_use%type;
  status            project_tmp.status%type;
  mobile_phone      project_tmp.mobile_phone%type;
  PRAGMA AUTONOMOUS_TRANSACTION; --因为要关联自己的表，所以要加上事务
BEGIN


  --查询所需字段
  select pl.bank_sn,
         pl.lend_date,
         :NEW.project_no,
         :NEW.contract_no,
         b.name,
         b.identity_card_no,
         :NEW.apply_credits,
         :NEW.fee_rate,
         :NEW.service_fee,
         :NEW.monthly_other_fee,
         :NEW.loan_duration,
         to_number(substr(replace(rpl.lend_date, '-', ''), 7, 2)) repay_day,
         pr.first_repay_date,
         b.account_bank,
         b.account_name,
         b.account_no,
         b.mobile_phone,
         :NEW.project_use,
         :NEW.status,
         :NEW.product_type
    into bank_sn,
         lend_date,
         project_no,
         contract_no,
         name,
         identity_card_no,
         apply_credits,
         fee_rate,
         service_fee,
         monthly_other_fee,
         loan_duration,
         repay_day,
         first_repay_date,
         account_bank,
         account_name,
         account_no,
         mobile_phone,
         project_use,
         status,
         product_type
    from project p,
         project_lend pl,
         (select project_id,
                 ltrim(sys_connect_by_path(name, '|'), '|') name,
                 ltrim(sys_connect_by_path(identity_card_no, '|'), '|') identity_card_no,
                 ltrim(sys_connect_by_path(account_bank, '|'), '|') account_bank,
                 ltrim(sys_connect_by_path(account_name, '|'), '|') account_name,
                 ltrim(sys_connect_by_path(account_no, '|'), '|') account_no,
                 ltrim(sys_connect_by_path(mobile_phone, '|'), '|') mobile_phone
            from (select project_id,
                         name,
                         identity_card_no,
                         account_bank,
                         account_name,
                         account_no,
                         mobile_phone,
                         row_number() over(partition by project_id order by name) rn
                    from borrower
                   where delete_indc = 'N'
                     and type = 'PROJECT')
           where connect_by_isleaf = 1
           start with rn = 1
          connect by rn = prior rn + 1
                 and project_id = prior project_id) b,
         (select project_id,
                 sum(lend_amount) lend_amount,
                 min(lend_date) lend_date
            from PROJECT_LEND
           group by project_id) rpl,
         (select project_id,
                 min(repay_date) first_repay_date,
                 max(repay_date) last_repay_date
            from project_repay
           group by project_id) pr
   where p.id = pl.project_id
     and p.id = b.project_id(+)
     and p.id = rpl.project_id(+)
     and p.id = pr.project_id(+)
     and p.id = :NEW.id;


  --插入临时表
  insert into project_tmp
    (id,
     bank_sn,
     lend_date,
     project_no,
     contract_no,
     name,
     identity_card_no,
     product_type,
     apply_credits,
     fee_rate,
     service_fee,
     monthly_other_fee,
     loan_duration,
     repay_day,
     first_repay_date,
     account_bank,
     account_name,
     account_no,
     project_use,
     status,
     mobile_phone)
  values
    (seq_project_tmp.nextval,
     bank_sn,
     lend_date,
     project_no,
     contract_no,
     name,
     identity_card_no,
     product_type,
     apply_credits,
     fee_rate,
     service_fee,
     monthly_other_fee,
     loan_duration,
     repay_day,
     first_repay_date,
     account_bank,
     account_name,
     account_no,
     project_use,
     status,
     mobile_phone);
  COMMIT;
END;