Re: Newbie Trigger Question

Billy

Thanks for the help. BTW I'm looking into this approach as we are not allowed to change the customers application for this project, which is to get data off of Oracle and into our database for faster reporting purposes.

Sounds like I'll have to build the statement (or name/value pairs) to file into the Journal Table, then rebuild the i/u/d statement on our side to update our copy.

Andre
Note anti-spam measures in email address..... "Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:9j19kv$f2h$1_at_ctb-nnrp2.saix.net...
> "Andre Cerri" <cerri_at_inter_n_o_spa_m_systems.com> wrote
>
> > 1) Does an Oracle trigger have access to the actual SQL statement
 that
> > caused it to be fired?
>
> Not directly. It has access to the before and after images of the row,
> iow the "before and after columns".
>
> The current SQL for a session can be obtained via the V$SQLTEXT and
> V$SESSION tables (joining address with sql_address and ordering
> sql_text by piece).
>
> To access V$ tables require DBA privs - alternatively you can use
> synonyms or views to circumvent the issue of having to provide DBA
> privs to that Oracle user.
>
> Note that you will not likely get the column values (literals) used by
> the SQL insert or update statements. Most clients use bind variables
> as this result in less parsing overheads. The SQL statement that you
> will see will look something like
> UPDATE table
> SET c2 = :c2
> WHERE c1 = :c1
>
>
> > 2) Can an Oracle trigger update an ODBC DSN directly?
>
> No, not my knowledge. The only possibillity I can think of is to delve
> into external stored procedures to achieve this type of transperancy.
>
> > Is this possible using the Transparent Gateway?
>
> Yes it should be.
>
> > If so, is the overhead of making and breaking the connection each
> > time to high to make this feasible?
>
> IMO yes. One of the prime criteria of a transaction on a OLTP system
> is speed. Triggers should be used to ensure data integrity and nothing
> more. My hackles always rise when I see people wanting to add more
> than that to triggers.. like firing off e-mails, faxes, messages and
> so on. This IMO shows a lack of understanding of OLTP.
>
> regards,
> Billy
>
>
>
Received on Tue Jul 17 2001 - 14:22:00 CDT