Re: Newbie Trigger Question

"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 - 07:04:21 CDT