Re: Persistent data per connection

On Fri, Mar 25, 2005 at 12:21:28PM -0600, Jeff Amiel wrote:
> Steve Atkins wrote:
>
> >Rather than grovel through the system tables during the trigger I'd do
> >all the work at the client session setup. As the first thing, write
> >the token into the temporary table. If that fails, create the
> >temporary table and retry. Then create the transaction you want to use
> >for all the other operations in the session.
>
> Our problem is that we have batch/standalone applications that will not
> be using the connection pool (as well as developers that may directly
> modify production tables in rare instances).  In those cases (heck, all
> cases) , the audit triggers themselves (when an update/insert/delete
> takes place) have to look to see if a temp table exists  to pull the
> user data from.  It wont exist in tese cases, but the triggers dont know
> this.....

A temporary table will mask the underlying table with the same name,
so you can have a generic user in a permanent table, then create a new
temporary table with the same name at the beginning of each session.

So in your schema you'd have a normal table called, say, "sessiondata"
with a single column "id" and a single row containing "not-in-a-session".

At the beginning of every client session you'd select id from sessiondata
and look at the value. If the value is "not-in-a-session" then you create
a temporary table called "sessiondata" with the same structure and
INSERT your sessionid.

Otherwise, UPDATE the id in sessiondata to your session id.

Then you'll have a valid session id in that table at all times. If you're
in a client session, it'll be the sessionid for that client session. If
not, it'll be "not-in-a-session".

(Obviously you can have more columns in the table and keep other session
data in it as well).

Cheers,
  Steve