I am involved in porting a custom application from Sybase to Oracle and
have run into a problem with creating duplicates of the Sybase
triggers. Our application makes use of INSERT, UPDATE, and DELETE
triggers that must modify the same table as the original SQL statement
that fired the trigger.
For example, for an UPDATE, the trigger needs to change the value of a
column in the table to reflect who just updated the row. Of course
doing the UPDATE inside the trigger fires the UPDATE trigger again and
so on until Oracle gets upset about too many recursion levels.
In Sybase you can do a statement like: IF _at__at_nestlevel > 1 RETURN which
will skip executing the trigger if it is not the "top" level of
execution.
Is there any Oracle equivalent to this?
Also, we make use of the Sybase 'inserted' and 'deleted' temporary
tables to access the changed rows. We can mostly work around Oracle's
lack of these temporary tables by using several triggers and package
variables for each table, but this is a bit messy. Does anyone know of
a better way to simulate this Sybase feature, keeping in mind we need to
do multi-row inserts and updates of the form: " insert into a_table
select * from a_table where ..... "
We are not using any standard front end tools - just custom C++ code and
the OCI, so any features of tools like Oracle Forms or similar are of no
use to us and because the end application already exists we cannot
change the structure of the database (ie. moving some of the columns
into other tables to avoid the recursive trigger problem is not an
option).
Thanks in advance.
Brad Lotsberg, EET.