Re: Trigger Problem - Server 7.3.3

That trigger makes no sense - you're repeating what the update is already doing?
If you want to manipulate data on the current row in a BEFORE update STATEMENT trigger, just assign the new/altered value to the :NEW variable. Other than that, it's not legal to update other rows in the same table (or related) in a trigger. There's workaround for situations where redundacy needs to be updated, but it's not easy. Basicly you need to buffer your keys that needs processing in your update row trigger, and in a after update STATEMENT trigger, you process this list and do your redundacy processing. You can buffer using a table, or PL/SQL tables defined static in a package.

Regards
  P. Larsen
"Ira Rosen" <IROSEN1_at_email.mot.com> wrote in message news:392FD8E0.4858198F_at_email.mot.com... While testing update triggers within my DB, the server (Release 7.3.3 on Solaris 2.6) gave me error ORA-04091. The full description of this error is below:

ORA-04091 table name is mutating, trigger/function may not see it  Cause:
A trigger or a user-defined PL/SQL function that is referenced in the statement attempted to query or modify a table that was in the middle of being modified by the statement that fired the trigger. Action:
Rewrite the trigger or function so it does not read the table.

Here is the trigger I was trying to execute: CREATE OR REPLACE TRIGGER U_TestResults_TCID AFTER UPDATE ON TestCases
FOR EACH ROW
BEGIN
    UPDATE TestResults
    SET TestCaseID = :new.TestCaseID
    WHERE TestCaseID = :old.TestCaseID; END; I have a similar update trigger between one other source table and the same destination table. That trigger previously worked fine, but is now responding with the same server error. There have been no changes in the server release that I can detect since I implemented the first rigger. The error messages for my triggers also include ORA-6512 which points to an error in line two of the trigger. In addition, I have constraints issuing delete statements between the same tables referenced in my triggers. The idea is that when a user updates or deletes a record in the source table, the same operation is performed on records in the destination table with the same record value as the source table. I find the ORA-04091 error rather confusing because the message is reporting an error condition within a trigger that describes the situation where it is appropriate for a trigger to fire: when a table is being modified. Unless I have some misunderstanding regarding the CREATE TRIGGER command, isn't this timing the reason for the 'AFTER UPDATE' clause I provided? I have temporarily dropped the offending triggers since these errors do not allow any actions to be taken on the source table. In case the 'table in the middle of being modified' is a reference to uncommitted transactions before firing the trigger, the same error occurs if I manually issue a COMMIT statement just before executing the UPDATE statement that fires the triggers. Any suggestions? TIA for any help that you can offer.
-Ira Rosen Received on Fri Jun 02 2000 - 00:00:00 CEST