Subject Re: RDB$Set_Context v GTT v Disk writes
Hi Louis,
  Thanks for your reply. My application architecture is the same as yours. The user logs onto the middle tier. A session key is generated and stored and all further use from that user is associated with that session key and user id. So we are both in the position of knowing exactly what user is talking to our Middle tier.

  Now to take it a step further, lets say that a user calls a method which will update a record in the database. That record will fire an update trigger which inserts a row into an audit table. The audit table needs to know the user ID.

  The most obvious way to do this is to pass in the user id with whatever SQL is performing the update. That is fine but in my case this will require an update to a *lot* of pre existing SQL

  So I'm looking for a 'cheap' way to perform the following process.

1.) User calls some function on middle tier.
2.) Middle tier grabs a connection from the pool
3.) Middle tier sets the user ID context on the connection with RDB$SET_CONTEXT (or some other method..)
4.) Middle tier performs the database update required by the user
5.) The table update trigger fires and reads the user_id from the context variable.
5.) Operation finished, the connection is returned to the pool.

The issue here is that the current method, whether using RDB$SET_CONTEXT or GTT's requires an extra transaction for every user interaction with the server. i.e this is an appreciable amount of overhead.

The idea I'm experimenting with at the moment is to use a UDF to effectively call back into the middle tier application to fetch the user id as required.