Proposal:

• Create your change log table
• Add an insert something into your change log table in every trigger body you want to get logged

that should work.

I wrote an extension function (UDF) to do similar.

On Windows the OutputDebugString function exists since Win32 and my UDF sends log messages from each trigger body into the system from where it can be captured, visualized and logged to file with the DebugView tool.

Another UDF DebugLevel controls and returns the dLevel static variable and by this the degree of details which I need.

Built-in function format rsp. printf is my best friend.

static int dLevel = 1;  // DebugLevel, 0=off, 1=little, 2=more, the higher the more


static void DebugFunc( sqlite3_context *context, int argc, sqlite3_value **argv )
{
  char *pz, *rz;
  int i, blobSize=0;

  for(i=0; i<argc; i++)
	blobSize += sqlite3_value_bytes(argv[i]);
  pz = rz = sqlite3_malloc( blobSize+1 );  // one additional for zero terminator
  if( rz == NULL )
  {
	if( dLevel ) OutputDebugString( "NULL" );
	sqlite3_result_null(context );
  }
  else
  {
	for(i=0; i<argc; i++)
	{
	  int addSize = sqlite3_value_bytes(argv[i]);
	  if( addSize )
	  {
		strncpy( pz, sqlite3_value_text(argv[i]), addSize );
		pz += addSize; //strlen(padd);
	  }
	}
	*pz = '\x00';
	if( dLevel ) OutputDebugString( rz );
	sqlite3_result_text(context, rz, blobSize, sqlite3_free);
  }
}


static void DebugLevelFunc( sqlite3_context *context, int argc, sqlite3_value **argv )
{
  if( argc>=1 )
  {
	int PreviousDLevel = dLevel;

	if( sqlite3_value_numeric_type(argv[0])!= SQLITE_NULL )
	{
	  dLevel = sqlite3_value_int(argv[0]);
	}

	if( dLevel != PreviousDLevel )
	{
	  char sBuffer[80];
	  sprintf( sBuffer, "DebugLevel changed from %d to %d", PreviousDLevel, dLevel );
	  OutputInfoString( sBuffer );
	}
  }
  sqlite3_result_int(context, dLevel );
}


...
  sqlite3_create_function_v2(db, "Debug",       -1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0,  DebugFunc,        0, 0, NULL);

  sqlite3_create_function_v2(db, "DebugLevel",   0, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0,  DebugLevelFunc,   0, 0, NULL); // returns dLevel
  sqlite3_create_function_v2(db, "DebugLevel",   1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0,  DebugLevelFunc,   0, 0, NULL); // sets and returns dLevel
  

But that changelog trigger will be triggered twice, if a transaction inserts two rows! If I am doing

BEGIN;
INSERT INTO a(id) VALUES(...);
INSERT INTO b(id) VALUES(...);
COMMIT;

I want my change log to have two rows like this

id | table | transaction_id
----------------------------
1  | a     | 7461
2  | b     | 7461

Notice the transaction_id begin same value.

You could try my function DebugLevel to keep a modul global variable as your transaction ID. Because the increment is done within a transaction (as first action) there will be no other transaction doing this. The Transaction isolation makes the change of the variable save as well. - if in your log one transaction_id would be missing then because of a rolled-back transaction.

Because the globally available variable (dLevel in my case) is available always your trigger can use that value as the transaction_id.

BEGIN;
SELECT DebugLevel( DebugLevel() + 1 );
INSERT INTO a(id) VALUES(...);
INSERT INTO b(id) VALUES(...);
COMMIT;

When you application starts it should initialize the variable for the next transaction_id, otherwise it would start the inital value 1 again.

SELECT DebugLevel( (select max(transaction_id) from transaction_log) + 1 );

Note a timestamp for the transaction.

SELECT julianday('now');
2459868.95526247
sqlite> SELECT strftime('%Y%m%dT%H%M%f', 'now');
20221016T105751.665

This won't be always trues, the time between inserting rows in two tables, and then the triggers kicking in can be different by milliseconds.

What should happen if a transaction is rolled back? Yes, you won't end up with records in your audit log (because they'll be rolled back too) but should the transaction IDs of the successful transaction after that be a simple step on from the transaction ID of the successful transaction before that?

Do the transaction IDs have to be sequential? Or even numbers? (Would UUID strings be suitable?)

UUID will work, and the trigger is AFTER trigger so only after the changes are committed these triggers will fire. I want to avoid using any extensions, and still being able to do this. Is there something builtin?

Ya I won't have control over programs accessing DB, that would have been no brainer to do with bunch of hooks or workarounds.

I am very interested in a solution to this problem with a function, say, transaction_time(), that is deterministic and constant within a transaction and strictly monotonic(*). The application is for trigger-maintained temporal backlog (which can be further processed into a transaction-time state table). I don't care about it being human readable nor about there being any gaps. I am using this right now but of course it suffers from the issue of the values not being constant for all executions of the trigger within the same transaction.

format("%d.%d", strftime("%s","now"),substr(strftime("%f","now"),4))

(*) It would be OK to have value reuse as long as they can be easily disambiguated by a physical timestamp e.g. counter does not rollover within a second or minute or whatever granularity is used for the physical timestamp component.

It would be nice if the logical timestamps were globally orderable across all transactions but at the moment, it is sufficient for me that they can be used to identify which rows in the temporal backlog come from the same transaction. When 'packing' the backlog entries into a TTST, I can take care of fixing up the funky timestamps into nice, clean logical timestamps that impose a total ordering across groups of related backlog timestamps.