3 Replies Latest reply on Mar 12, 2008 10:50 AM by tbemcf14

    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!
        • 1. Re: Pass CF variable to Oracle Trigger
          Level 7
          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.
          • 2. Re: Pass CF variable to Oracle Trigger
            paross1 Level 2
            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
            • 3. Re: Pass CF variable to Oracle Trigger
              tbemcf14 Level 1
              Thanks Phil... that sounds like a good idea. I'll give the stored proc method a try.