Newbie Trigger Question

[Andre Cerri]

A couple of questions for you all. I need to update an ODBC DSN when certain
SQL statements are executed. I either want to add an entry to a local
'Journal' Table, or update the ODBC DSN directly.

1) Does an Oracle trigger have access to the actual SQL statement that
caused it to be fired? For example, table ABC has an insert trigger that
says:

INSERT INTO Journal set STATEMENT = sqlstatement

So when I execute INSERT INTO ABC set COL1=1,COL2=2 the resultant
trigger evaluates to:

INSERT INTO Journal set STATEMENT = 'INSERT INTO ABC set COL1=1,COL2=2'

2) Can an Oracle trigger update an ODBC DSN directly? Is this possible using
the Transparent Gateway? If so, is the overhead of making and breaking the
connection each time to high to make this feasible?

Cheers

Andre
Note anti-spam measures in email address.....

[Billy Verreynne]

"Andre Cerri" <cerri@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

[Andre Cerri]

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" <vsl...@onwe.co.za> wrote in message
news:9j19kv$f2h$1...@ctb-nnrp2.saix.net...