title: A field value returned by a trigger after insert is not synchronized in JPA with a method annotated with @Transactional and saveAndFlush() invocation
body: <p>The trigger is working perfectly and generates the value when executed in a script. However, in JPA, the trigger generated value is null after the method to save with @Transactional annotation is executed.  When checked in the database this field has a value. I tried to use findById to refresh the record after the save method but the field is still null. The weird part is that when I run the API to just get the record which executes the same findById method, it displays the value.</p>
  <p>I have a similar code for another table and it works perfectly. The difference is that the function invoked in the trigger is executed faster than this one.</p>
  <p>Is @Transactional executes a commit? When the execution of the method with @Transaction annotation is complete, is the commit is also complete? So why is the database record not the same as the entity record when the findById is executed?</p>
  <p>Below are sample code snippets for reference:</p>
  <pre><code>@Entity
  @Table(name = &quot;my_table&quot;)
  @EntityListeners(AuditingEntityListener.class)
  public class MyTableEntity implements Serializable {
  ...
  @Id
  @GeneratedValue(generator = &quot;iDGenerator&quot;)
  @Column(name=&quot;column1&quot;, nullable=false, updatable=false)
  private Long column1;
  
  @Column(name=&quot;column2&quot;, length=5, nullable=false, updatable=false)
  private String column2;
  
  @Column(name=&quot;column3&quot;, length=10)
  private String column3;
  
  @Column(name=&quot;column4&quot;, insertable=false, updatable=false)
  private Long column4;
  
  @Column(name=&quot;cre_userid&quot;, length=30, nullable=false, updatable=false)
  @CreatedBy
  private String createdBy;
  
  @Column(name=&quot;cre_date&quot;, nullable=false, updatable=false)
  @CreatedDate
  private LocalDateTime dateCreated;
  ...
  </code></pre>
  <p>Its table has a trigger that sets the value of column4 as follows:</p>
  <pre><code>CREATE OR REPLACE TRIGGER MY_TABLE_BIUFER 
  BEFORE INSERT ON MY_TABLE
  REFERENCING OLD AS OLD NEW AS NEW
  FOR EACH ROW
  BEGIN
    :new.column4 := workflow_pkg.create_workflow (:new.column1);
  END;
  </code></pre>
  <p>The controller and the service code snippets are listed below:</p>
  <pre><code>@Service
  public class MyServiceImpl implements MyService{
  
      @Autowired
      MyTableRepository myTableRepo;
  
      @Override
      public MyTableDto getRecord(Long pk) {
          MyTableDto returnValue;
          MyTableEntity myTableEntity = myTableRepo.findById(pk)
                                                   .orElseThrow(() -&gt; new IIBSException(&quot;Transaction Id &quot; + pk + &quot; not found. Process Terminated.&quot;));
          myTableDto = convertToDto(myTableEntity); //use ModelMapper to convert entity to dto
          return returnValue;
      }
  
      @Override
      @Transactional
      public Long saveRecord(MyTableDto myTableDto) {
          MyTableEntity myTableEntity, storedDetails;
          Long returnValue;
  
          myTableEntity = convertToEntity(myTableDto); //use ModelMapper to convert dto to entity
          storedDetails = myTableRepo.saveAndFlush(myTableEntity);
  
          returnValue = storedDetails.getQuotApplTransNo();
          return returnValue;                 
      }
  }
  
  
  
  @RestController
  @RequestMapping
  public class MyController {
  
      @Autowired
      MyService myService;
  
      @GetMapping(path=&quot;/{pk}&quot;)
      public MyTableDto getRecord (@PathVariable Long pk) throws MyException {        
          if (pk == null) throw new MyException(ErrorMessage.MISSING_PARAMETER.getErrorMessage());
          
          MyTableDto returnValue = myService.getRecord(pk);   
          return returnValue;
      }
  
      @PostMapping
      public MyTableDto saveRecord (@RequestBody MyTableDto myTableDto) throws MyException
      {       
          //.. validations .. //
  
          Long pk = myService.saveRecord(myTableDto); 
          if (pk == null) throw new MyException(&quot;Error in saving Request for Quotation Package&quot;);
  
          MyTableDto returnValue = myService.getRecord(pk);   
          return returnValue;
      }
  }
  </code></pre>