| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Recursive Triggers - Sybase to Oracle port
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.
 |
 |