trigger within procedure within trigger

This sounds worse than it is. I have a trigger A on table A that ensures the key for any row is drawn from a sequence. I have a procedure that will enable or disable any trigger if you pass it the trigger name and what you want to do. It's tested and it works. And I have a view B that hits ten tables and presents a useful set of information to a user.

When I update B I use an instead of trigger to update the underlying tables. On table A I want to control the key for a row rather than use the sequence which trigger A would provide. Then I can use this key value in tables that intersect with A. So in the instead of trigger I call the procedure to disable the trigger A. Then I do my inserts and call the procedure to enable the trigger A. However the trigger is not being disabled and enabled. I get no error message from Oracle - but it just doesn't happen.

My current workaround is to use a session-limited global temporary table to store the sequence value for reuse. But this doesn't seem particularly elegant. Any better ideas?

Thanks Received on Wed Mar 21 2001 - 18:40:37 CST