java - 插入触发器导致 EntityNotFoundException

Question

我需要一些帮助来解决这个问题。我浪费了一天的时间寻找答案却什么也没找到。

我有一个实体和模型类。实体是简单的数据，模型是实体的 Controller 。在数据库上，我有一个触发器，当发生 AFTER INSERT 时，会触发触发器来更新另一个表的信息。发生的情况是，在 EntityManager 持久并刷新之后，调用刷新并导致“EntityNotFoundException”。如果我取出扳机上的插件，那么一切就都顺利了。代码如下:

让球滚动的方法

    public FTL createPaymentByAccountNumberLoanNumber(String accountNumber, Integer loanNumber, Payment payment) {

        EntityManager em = getEntityManager();

        Loan loan = checkLastLoan(accountNumber, loanNumber);

        BigDecimal totalAmount =
                payment.getAppliedToBalance()
                .add(payment.getAppliedToXXX())
                .add(payment.getAppliedToYYY())
                .add(payment.getAppliedToZZZ());

        FT ft = new FT ();            
        ft.setTransactionAmount(totalAmount);
        em.persist(ft);

        FTL lastFtl = FTL.loadLastByLoan(loan);
        em.detach(lastFtl);

        FTL ftl = new FTL(ft);
        ftl.setTransactionAmount(totalAmount);
        ftl.setAppliedToBalance(payment.getAppliedToBalance());
        ftl.setAppliedToXXX(payment.getAppliedToLateCharge());
        ftl.setAppliedToYYY(payment.getAppliedToMaturityInterest());
        ftl.setAppliedToZZZ(payment.getAppliedToNsfCharge());
        ftl.setTransactionCode(TransactionCode.Collection);
        ftl.setNewEndingBalance(lastFtl.getNewEndingBalance().subtract(payment.getAppliedToBalance()));
        ftl.setLoan(loan);

        em.persist(ftl);
        em.flush();         
        em.refresh(ft);
        em.refresh(ftl); //THIS IS WHERE THE EXCEPTION OCCURES WHEN GOING THROUGH DEBUG
        em.detach(ftl);

        evict(Loan.class, loan.getLoanId());

        return ftl;
    }

FTL_loan_movement_trigger

    CREATE TRIGGER FTL_loan_movement_trigger
    AFTER INSERT
    ON FTL
    FOR EACH ROW
    EXECUTE PROCEDURE loan_movement_trigger();

loan_movement_trigger

    CREATE OR REPLACE FUNCTION loan_movement_trigger()
      RETURNS trigger AS
    $BODY$
    DECLARE
        _loan_id bigint;
        _loan_movement_status integer;
        _loan_current_status integer;
        _ending_balance numeric;
    BEGIN
      _loan_id := NEW.loan_id;

      RAISE NOTICE 'Finding the statuses of the loan: %', _loan_id;

      --Find the currently saved off status of the loan
      _loan_movement_status := (SELECT fls.status FROM find_loan_status(_loan_id, now()::date) fls);
      --Calculate the current status of the loan
      _loan_current_status  := (SELECT * FROM calc_loan_current_status(_loan_id) clcs);

      RAISE NOTICE 'Movement % and Current %', _loan_movement_status, _loan_current_status;

      IF(_loan_movement_status != _loan_current_status) THEN
         _ending_balance := (SELECT * FROM calc_ending_balance(_loan_id, now()::date));

         RAISE NOTICE 'New ending balance: %', _ending_balance;
    --/*  THIS INSERT RIGHT HERE IS WHAT IS CAUSING THE EXCEPTION!!!!!!!
         INSERT INTO loan_movement
        (movement_date,
            loan_id,
            status,
            remaining_balance,  
            created_by,
            organization_id) 
        VALUES
        (now(), 
            9999999, 
            _loan_current_status, 
            _ending_balance,
            1,
            1);

         RAISE NOTICE 'Inserted into loan_movement';
     --*/

      END IF;
      RETURN NEW;

    END;

Answer 1

PL/pgSQL 应该是服务器端执行(多个)SQL 语句的粘合剂。不要像 Java 这样的过程语言那样使用子函数和赋值来模块化代码，这是非常低效的。尝试使用少量的 SQL 语句来同时完成大量工作。围绕它构建您的 plpgsql 代码。

您的触发器函数可以通过单个 SQL 语句得到极大简化:

CREATE OR REPLACE FUNCTION loan_movement_trigger()
  RETURNS trigger AS
$func$
BEGIN
   WITH loan AS (SELECT calc_loan_current_status(NEW.loan_id) AS _current)
   INSERT INTO loan_movement (movement_date, loan_id, status
             , remaining_balance, created_by, organization_id) 
   SELECT now(), 9999999, l._current
        , calc_ending_balance(NEW.loan_id, now()::date), 1, 1
   FROM   loan l
   WHERE  l._current IS DISTINCT FROM
         (find_loan_status(NEW.loan_id, now()::date)).status;

   RETURN NULL;  -- can just be NULL for AFTER trigger
END
$func$ LANGUAGE plpgsql;

您的 IF 表达式可能是错误的，因为它不适用于 NULL 值。

IF(_loan_movement_status != _loan_current_status) THEN

仅当两个值都不为 NULL 且不同时才计算为 TRUE。我用 IS DISTINCT FROM 重写来覆盖 NULL 值。
另外，我将整个 IF 结构替换为 INSERT 的 WHERE 条件，而且基本上其他所有内容也都替换了。