A vendor migrated our WANG tables to Oracle. The row-level table triggers retain a piece of code which allows for the trigger to be bypassed if a package spec variable is set to 'NO UP'. This is fine, except at the end of the same trigger that package spec value is set back to a default value (WANG programs were based on updating a single row at a time). So, when running UPDATE/INSERT/DELETE queries which modify several records, the value of the package spec variable gets set back after the first row is changed.

We have quite a few tables now set up this way, and we're trying to find the easiest way to update the table triggers so that multiple rows can be updated, while retaining the initial value of this package spec variable.

Here are the options we've come up with:
1) move the resetting of the package spec variable to a statement-level after trigger, once all the rows have been modified
2) move the resetting of the package spec variable to the package which is making the update
3) create a function that you can add to the WHERE clause of the SQL statement which resets the package spec variable for each record updated.

1) & 2) are quite a bit of work
3) this would be great, but it's been disproven, unless I did something wrong (see code below)

Can anyone else brainstorm a solution that might be less work?

-Thanks
Chuck

Code:
SQL> CREATE TABLE forbesc.TEST_OP_NAME (
2      myname VARCHAR2(10), 
3      age INTEGER, 
4      arg_val VARCHAR2(10));

Table created.

SQL> CREATE OR REPLACE TRIGGER forbesc.test_op_name_bt
2      BEFORE 
3      UPDATE 
4      ON forbesc.TEST_OP_NAME
5      FOR EACH ROW
6      BEGIN
7           IF pacepack.get_operation_name = 'NO UP' THEN
8                :NEW.arg_val := pacepack.get_operation_name;
9           ELSE
10               :NEW.arg_val := 'NOT NO UP';
11         END IF;
12 
13         -- reset the value after every row 
14         pacepack.set_operation_name('');
15    END;
16 /

Trigger created.

SQL> CREATE OR REPLACE FUNCTION forbesc.set_op_name (arg VARCHAR2)     RETURN VARCHAR2
2       IS
3       BEGIN
4            pacepack.set_operation_name(arg);
5            IF pacepack.get_operation_name = arg THEN
6                  RETURN 'TRUE';
7            ELSE
8                  RETURN 'FALSE';
9            END IF;
10      END;
11 /

Function created.

SQL> INSERT INTO forbesc.TEST_OP_NAME (myname, age) VALUES ('Chuck', 33);

1 row created.

SQL> INSERT INTO forbesc.TEST_OP_NAME (myname, age) VALUES ('Jody', 29);

1 row created.

SQL> INSERT INTO forbesc.TEST_OP_NAME (myname, age) VALUES ('Chip', 25);

1 row created.

SQL> SELECT * FROM forbesc.TEST_OP_NAME;
Chuck  33
Jody    29
Chip     25

SQL> UPDATE forbesc.TEST_OP_NAME 
2      SET age = age + 1 
3      WHERE forbesc.set_op_name('NO UP') = 'TRUE';

3 rows updated.

SQL> select * from forbesc.test_op_name;
Chuck  34  NO UP
Jody    30  NOT NO UP
Chip     26 NOT NO UP
-Chuck