Re: Mutating Tables...how to handle..?

Russell Foster wrote:
>
> Charles Jardine wrote:
> >
> > Michael Joseph wrote:
> > >
> > >
> > > Does anyone know the best way to deal with this...?
> >
> > The solution depends on the fact that the triggers on B can safely
> > "trust" those on A. Before doing the insert, the after row trigger on
> > A can set a package variable to indicate to the before row trigger on
> > B that this insert is OK, and that the FK constraint does not need
> > checking. It is VITAL that the package variable be unset on ALL
> > routes out of the trigger on A. Use an EXCEPTION WHEN OTHERS clause.
>
> I tried exactly what is described here, but found a major problem...
>
> While the package variable had my referential integrity turned off, another user sent a sql
> statement that caused the instance to crash and require restart. After the restart of the
> instance, the referential integrity was still turned off, but was this unknown until several
> records became invalid. This is unacceptable.

I don't know how to put this without using unparliamentary language.

I don't believe the above story. Package variables are local to an Oracle session. A session's copy of a package variable is initialised as specified in the package body the first time that the package is used in the session. There is no way that one session can see changes made by another. There is certainly no way the value of a package variable could survive an instance restart.

There is a danger. Changes to package variables are not undone by ROLLBACK, so if a SQL statement fails, referential integrity might remain disabled for the rest of the current session (though not for other sessions). The EXCEPTION WHEN OTHERS clause deals with this case.

I have production code using logic like this. It really does work.

P.S. I have found that it is dangerous to use public package variables. There was a bug which sometimes stopped them being initialised in 7.1.3. I don't know whether it has been fixed. Use a private variable. Manipulate it with functions and procedures, as in the following example of an encapsulated boolean (again from a production database).

CREATE OR REPLACE PACKAGE delta_control
AS
  FUNCTION enabled RETURN BOOLEAN;
  PROCEDURE enable;
  PROCEDURE disable;
END;
/
CREATE OR REPLACE PACKAGE BODY delta_control AS
  internal_flag BOOLEAN := true;
  FUNCTION enabled RETURN BOOLEAN IS BEGIN RETURN internal_flag; END;   PROCEDURE enable IS BEGIN internal_flag := TRUE; END;   PROCEDURE disable IS BEGIN internal_flag := FALSE; END; END;
/ Received on Tue Apr 30 1996 - 00:00:00 CEST