How to post a record with a transaction that will rollback (PostgresSQL)

[seu_madruga]

Hello everyone.

I Have a transaction and I will execute a rollback, but I need to post a log table record.
This insert log record
Examle:

Code: Select all

var
  con : TZConnection;
  qry1, qry2, qry3, qryLog : TZQuery;
begin
  try
      con.TransactIsolationLevel := tiReadCommitted;
      con.StartTransaction;

      qry1.Connection := con;
      qry2.Connection := con;
      qry3.Connection := con;
      qryLog.Connection := con;
            

      // STEP 01
      qryLog.SQL.Text := 'insert into LOG'; // Log STEP 01
      qryLog.Execute; 
      
      qry1.SQL.Text := 'insert into XPTO1';
      qry1.Execute; // Maybe Error here    
          
      // STEP 02
      qryLog.SQL.Text := 'insert into LOG'; // Log STEP 02
      qryLog.Execute; 
      
      qry2.SQL.Text := 'insert into XPTO2';
      qry2.Execute; // Maybe Error here    
                  
      // STEP 03
      qryLog.SQL.Text := 'insert into LOG'; // Log STEP 03
      qryLog.Execute; 
      
      qry3.SQL.Text := 'insert into XPTO3;
      qry3.Execute; // Maybe Error here
            
      con.Commit;
    except             
      con.Rollback; // I need to cancel qry1, qry2 and qry3, but I need to post qryLog
    end;
  

Thanks

[marsupilami]

You could think about inserting in the log when the transaction is finished:

Code: Select all

var
  con : TZConnection;
  qry1, qry2, qry3, qryLog : TZQuery;
begin
  try
      con.TransactIsolationLevel := tiReadCommitted;
      con.StartTransaction;

      qry1.Connection := con;
      qry2.Connection := con;
      qry3.Connection := con;
      qryLog.Connection := con;
            

      // STEP 01
      qryLog.SQL.Text := 'insert into LOG'; // Log STEP 01
      qryLog.Execute; 
      
      qry1.SQL.Text := 'insert into XPTO1';
      qry1.Execute; // Maybe Error here    
          
      // STEP 02
      qryLog.SQL.Text := 'insert into LOG'; // Log STEP 02
      qryLog.Execute; 
      
      qry2.SQL.Text := 'insert into XPTO2';
      qry2.Execute; // Maybe Error here    
                        
      qry3.SQL.Text := 'insert into XPTO3;
      qry3.Execute; // Maybe Error here
            
      con.Commit;
    except             
      con.Rollback; // I need to cancel qry1, qry2 and qry3, but I need to post qryLog
    end;

    // STEP 03
    qryLog.SQL.Text := 'insert into LOG'; // Log STEP 03
    qryLog.Execute; 
  

[Fr0sT]

The most correct way is 2nd independent transaction. IDK about Postgres, maybe it would require second connection.