Pass CF variable to Oracle Trigger

[tbemcf14]

Anyone know the best way (if possible) to pass a variable to a trigger through coldfusion? Say I have a table "games" with a trigger on it that inserts the transaction history from that table into an audit table called "games_audit". The app user name is defined by appuser, and I need to get that value into my audit table as the change user.

<cfset appuser = "Firstname Lastname">
<cfquery>insert into games (game_num, game_name) values (1, 'Scrabble')</cfquery>

... then this trigger in the database runs...

create or replace trigger trg_insert_audit
after insert on games...
insert into games_audit (game_num, change_type, change_date, change_user)
values (1, 'Insert', SYSDATE, #appuser#)
...

How do I pass #appuser# into the trigger?

Note: Oracle's USER value will not work in this situation. Our database uses a generic user for all web users. I need the specific web user defined in cf.

CFMX 7, Oracle 10g

Thanks!

[Newsgroup_User]

I'm pretty sure the trigger will only receive the :NEW values from the
query which causes the trigger to fire.

NB: CF has nothing to do with this process, as it all takes place on the DB
server. You're better off asking thing on an Oracle forum (although good
luck with that: the Oracle "community" seem to be a bunch of self-righteous
@rseholes, in my experience(*)).

The question is more likely "how do I pass an addition value from an INSERT
query to an AFTER INSERT trigger".

Have you searched the relevant Oracle docs?

--
Adam

(*) paross1, should you see my comment above: you're a rare exception to
this demographic. But then again you're not solely an Oracle bod,
obviously.

[paross1]

Oracle users "self-righteous"? Snooty? Condescending? Hmmmm, probably not any more than your average UNIX administrator/guru....

As for the trigger issue, I believe that Adam is correct, as there really isn't any way that I know of to "pass" the value of #appuser# to your games_audit table, since the trigger that is performing the insert gets its data from the action that initiated it, which in this case is an INSERT into the games table. In other words, the trigger only "knows" about the OLD values of the affected columns in games and the NEW values that are in the INSERT or UPDATE statement agains games, not games_audit. Since you are not using the value of #appuser# to update or insert into games, the trigger has nothing to use on the insert into games_audit.

Since you do have any control over the trigger, and it fires whenever you insert into games, one thing that you might consider trying would be to put your insert statement for the games table into a PL/SQL procedure, where you would pass the value of #appuser# as one of the parameters. Then, after the insert query, and before the commit, select from the games_audit table the rowid of the row that was just inserted by the trigger and write an update of games_audit that updates the value of change_user with the value of #appuser# that you passed as an IN parameter. Then commit and exit the procedure.

Phil

[tbemcf14]

Thanks Phil... that sounds like a good idea. I'll give the stored proc method a try.