Trying to run a trigger with an ID from a webuser table,not an oracle user...is this possible?

[jonesad]

Hello,
I am working on an application that uses Coldfusion 7 and Oracle 9i. We
currently handle all user managment via a webuser table in the oracle DB (with
fields for username/password/user_id). We would like to make a trigger that
will fire when a table is updated (set updated date to sysdate and updated by
to userid), but the problem is getting oracle to recognize the userid from the
webuser. Right now, Coldfusion is connecting to the ORACLE dsn with 1 oracle
username/password so we don't have to create an oracle user for each of our
webusers.. Has anyone done this before?

[jonwrob]

I don't think it's possible, although I would love for someone to prove me
wrong. The problems are two-fold.

1. CF uses only one user to connect to the database.
2. Whatever database sessions that CF creates as it connects to Oracle have
no corelation to CF sessions.

I remember that in the client-server universe, there was a dbms
package/procedure that you could run to set a kind of global variable for your
session identifying something like a user name or ID. The variable was valid
for the duration of your session. Your application could connect all users
with a single username/password, and use the global variable to identify the
individual session.

But, CF cannot connect a database session with a CF session in any meaningful
way.

JR

[klwil3]

Unfortunately, you are both correct. I'm hoping that someone from Macromedia
can remedy this in a future version by allowing the cfquery tag to pass a
variable for something such as the user id doing the deleting. For now, this
is the way we went in our project:

1) Create audit columns on each table you want to audit:
create_by
create_date
mod_by
mod_date

2) Create a history table for the table you want to audit:
create table TABLENAME_H (
,
chg_by,
chg_date,
chg_type);

3) When you insert into the table to audit, include values for create_by and
create_date.

4) When you update the table to audit, include values for mod_by and mod_date.

5) Create an insert an trigger for the audit table that copies the columns
into the history table with TABLENAME.create_by = TABLENAME_H.chg_by,
TABLENAME.create_date = TABLENAME.chg_date, 'I' = chg_type (or however you want
the audit table to reflect an insert).

6) Create an update an trigger (on the mod_by column if you don't want to
audit every action...if you don't reference mod_by, you won't audit...good for
system updates...otherwise on any update of the table) for the audit table that
copies the columns into the history table with TABLENAME.mod_by =
TABLENAME_H.chg_by, TABLENAME.mod_date = TABLENAME.chg_date, 'U' = chg_type (or
however you want the audit table to reflect an update).

7) Deletes were tricky. I had to create a stored procedure to call in the
webpage instead of using a cfquery delete. I passed the stored procedure my
column name, column id, and the user_id doing the action. The stored proc then
updated the history table and deleted the info.

This works, but it takes a little patience. If there's a better way, I'd like
to know, but right now that seems to be a real limitation of CF (only having
one user/pass for the datasource without a way of saying who's doing the
changes). Good luck.