| Subject | Re: [Firebird-devel] pass parameter to a trigger? |
|---|---|
| Author | unordained |
| Post date | 2008-09-19T20:04:19Z |
You never re-posted your question on firebird-support, that I can tell, but I thought I'd reply
directly anyway.
Please look at rdb$set_context() and rdb$get_context() as a place to store this information. You
can also use a temporary table (create global temporary table xxx ...), if you want an explicit
place for it, or care about datatypes. (Since you're eventually going to record this in the logging
tables, knowing ahead of time that the data is type-incompatible could be a good thing.)
Either way, triggers would have access to this sort of information, without having to pass it at
each insert/update/delete in your application. If you use the 'USER_SESSION' parameter for
rdb$set_context(), you can set this data just once when you connect to the database, and it will be
valid across all transactions; create a procedure that calls rdb$set_context, and pass it all the
data you'll need in your trigger, before you do anything else. If you use 'USER_SESSION', or use
temporary tables, you need to post the data at each transaction start, before you do anything else.
Either way, a single procedure would probably be helpful, to keep it all clean and in a way
document which parameters you need for good auditing.
If you're using standalone clients with a persistent connection, session-level data seems good. If
you're in a web environment, with connection pooling, you should do it per-transaction so you don't
get your data crossed between concurrent users. You might have the procedure throw an exception if
the user session/transaction has already identified itself, just so you know if you mess up.
(Particularly important in a connection-pooling environment.)
Since this is for auditing, you might also want to consider making your triggers do more than just
record this data in the logs; raise an exception in the trigger if the information isn't available.
That way, if someone manages to connect to the database directly (not via your application), at
least they won't be able to make changes to data without putting *something* in those variables.
(Security by obscurity, I'll admit.) It would also help you debug issues if you're forgetting to
set the audit variables somewhere in your code, or you have auto-commit set (after which the
variables would go back to NULL), or something else prevents your auditing data from getting
prepared for triggers.
In the trigger, use either rdb$get_context, or the temporary table you created, to retrieve the
current session/transaction "about the user" variables.
-Philip
---------- Original Message -----------
From: firebird <kornelraju@...>
To: firebird-devel@...
Sent: Thu, 18 Sep 2008 16:23:46 +0200
Subject: [Firebird-devel] pass parameter to a trigger?
directly anyway.
Please look at rdb$set_context() and rdb$get_context() as a place to store this information. You
can also use a temporary table (create global temporary table xxx ...), if you want an explicit
place for it, or care about datatypes. (Since you're eventually going to record this in the logging
tables, knowing ahead of time that the data is type-incompatible could be a good thing.)
Either way, triggers would have access to this sort of information, without having to pass it at
each insert/update/delete in your application. If you use the 'USER_SESSION' parameter for
rdb$set_context(), you can set this data just once when you connect to the database, and it will be
valid across all transactions; create a procedure that calls rdb$set_context, and pass it all the
data you'll need in your trigger, before you do anything else. If you use 'USER_SESSION', or use
temporary tables, you need to post the data at each transaction start, before you do anything else.
Either way, a single procedure would probably be helpful, to keep it all clean and in a way
document which parameters you need for good auditing.
If you're using standalone clients with a persistent connection, session-level data seems good. If
you're in a web environment, with connection pooling, you should do it per-transaction so you don't
get your data crossed between concurrent users. You might have the procedure throw an exception if
the user session/transaction has already identified itself, just so you know if you mess up.
(Particularly important in a connection-pooling environment.)
Since this is for auditing, you might also want to consider making your triggers do more than just
record this data in the logs; raise an exception in the trigger if the information isn't available.
That way, if someone manages to connect to the database directly (not via your application), at
least they won't be able to make changes to data without putting *something* in those variables.
(Security by obscurity, I'll admit.) It would also help you debug issues if you're forgetting to
set the audit variables somewhere in your code, or you have auto-commit set (after which the
variables would go back to NULL), or something else prevents your auditing data from getting
prepared for triggers.
In the trigger, use either rdb$get_context, or the temporary table you created, to retrieve the
current session/transaction "about the user" variables.
-Philip
---------- Original Message -----------
From: firebird <kornelraju@...>
To: firebird-devel@...
Sent: Thu, 18 Sep 2008 16:23:46 +0200
Subject: [Firebird-devel] pass parameter to a trigger?
> I have to do a change log for all the tables in my database.------- End of Original Message -------
> I need to store the name of user(not the database user) and other
> things, that comes from the application.
>
> any possibility?
>
> -------------------------------------------------------------------------
> This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
> Build the coolest Linux based applications with Moblin SDK & win great prizes
> Grand prize is a trip for two to an Open Source event anywhere in the world
> http://moblin-contest.org/redirect.php?banner_id=100&url=/
> Firebird-Devel mailing list, web interface at
> https://lists.sourceforge.net/lists/listinfo/firebird-devel