Re: Recursive Triggers - Sybase to Oracle port

 technet.oracle.com
 search for triggers use
 :old and :new
in place of the inserted and deleted tables

Brad Lotsberg wrote:

> 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 @@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.
> -----------------
Received on Thu Mar 23 2000 - 15:07:43 CST