Question regarding storing session with set context_info

Currently our need is to track the data that is changed by the user and record it as a part of the Audit Log.

We have accomplished this need ny making use ofset context_infoand also by passing the UserId from all the Layers and finally log the user name using the set context_info in the stored proc.

What we thought of was using the trigger for each update,delete records for the table.So by setting the context in teh stored proc we could access the UserId in the trigger and able to record the changes made by Tagged userId.

What we want to make sure before making this as our permananet solution is

(1) Reliability - In a web application (N Tier) how would the context not get out of scope.

2) any Performannce related issues, you guys can think of.

3) This Context used in the database -- would that be the part of the connection used from the UI, meaning the if a request is made from UI to the Database . would the context be alive till the connection doesn't go back to the connection pool.

Thanks

Sweety.

No answers yet.

Any idea wether this approach would be good or not.

Need your Input

Thanks

Question on OUTPUT feature of DML

I need to audit inserts/updates/deletes on active tables to audit tables on a set of tables that have foreign key constaints with the update cascade and delete cascade defined. I can explicitly code the delete/update on the parent table to perform an OUTPUT to an audit table, but how do I OUTPUT the cascaded delete/update that happens on the child table because of the FK constraint with delete cascade defined without having to resort to triggers.

Thanks,

-chiraj

You might want to check into the use of DML triggers for this kind of issue, but this is not something that I have used much.

Dave

|||

Thanks for the response.

Using DML triggers is a no-brainer. I have used them all my life. I was wondering if it could be accomplished with the OUTPUT clause. I believe it is a limitation of the OUTPUT clause unless someone can show me otherwise.

Thanks,

-chiraj.