Is setting an Oracle session variable from a web app a reliable way to pass a value from a web app to a trigger?

Question

I have a lookup table in Oracle 10g with a trigger that populates a history table on insert, update and delete. I want to be able to capture the user ID from the ColdFusion app in the history table via the trigger.

One potential way I found to do this was by using Oracle session variables: In the coldfusion code, I pass the user ID to the Oracle session right before the insert, update and delete code:

CALL dbms_session.set_identifier(12345);

In the trigger, I read the client_identifier var, swapping for "0" if null.

SELECT sys_context('USERENV','CLIENT_IDENTIFIER') INTO USER_ID FROM DUAL;
  IF USER_ID IS NULL THEN
    USER_ID         := 0;
  END IF;

In single user testing this works fine, but because I don't know how the CF app server's DB connections relate to Oracle sessions, I'm concerned that the value in the oracle session var will be unreliable with multiple simultaneous users.

Is setting an Oracle session variable from a web app a reliable way to pass a value from a web app to a trigger?

Answer 1

Suppose rather than doing what you've outlined above (which I don't think is workable), you add a column to your lookup table (let's call it MY_LOOKUP_TABLE) in which you store the user ID of the user making the update (or insert) to the table:

ALTER TABLE my_lookup_table
  ADD update_user_id NUMBER DEFAULT 0 NOT NULL;

(Assuming your lookup table isn't enormous and you don't mind setting a default for every existing row.)

The difficulty comes about when you need to delete a row from the lookup table ... how do you record the ID of the user who deleted it? In this case you would also need a column to record that the row was deleted, maybe DELETE_FLAG:

ALTER TABLE my_lookup_table
  ADD delete_flag CHAR(1) DEFAULT 'N' NOT NULL;

Then, in your update trigger, don't record updates where DELETE_FLAG = 'Y':

CREATE OR REPLACE TRIGGER record_update
AFTER UPDATE OF my_lookup_table
FOR EACH ROW
WHEN (new.delete_flag <> 'Y')

and when your app does a delete, you'll actually want an update followed by a delete:

<cftransaction>
<cfquery name="update_before_delete_lookup">
    UPDATE my_lookup_table
       SET update_user_id = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#user_id#" />
         , delete_flag = 'Y'
     WHERE <conditions>
</cfquery>

<cfquery name="delete_lookup">
    DELETE FROM my_lookup_table
     WHERE <conditions>
</cfquery>
</cftransaction>