DOES THE BEFORE UPDATE TRIGGER FIRE BEFORE COMMIT OF AFTER?

[hubud]

I have a procedure that updates a field in a table as part of an open cursor, looping through rows and updating an error_code field. Only after the cursor runs out does it issue a commit statment.

I am thinking of adding a before update trigger to the target table so that any rows flagged in the procedure are automatically passed to an exception table which in turn fires a trigger that deletes back on the pk. In this way rows flagged as not null on the target table never remain on the target table.

The question is if all these updates are part of a cursor then there will be loads of updates that have not committed on the target table. Will the before update trigger fire as each prior-to-commit row is updated on the target table or will it fire when the commit is issued at the end of the cursor on the commit.

If it's the latter ( which I think it is due to data consistency ) does the trigger fire for each row that has been updated on the commit on a row by row basis. Does this even matter? I could call a separate procedure to do this if it is a problem. Would it be better to use an after update trigger instead?

Any advise would be appreciated.


cheers

simmo

 

[hubud]

Just thinking further on this.

If I user a before update trigger as the row hits the target table and pass this row to the exception table prior to update then this would prevent fragmentation on the table?, as the row was never inserted?
This would mean a before update trigger sends the row to the exception table by passing the target table.

Would this be a correct understanding of the principle?

cheers

simmo

 

[SantaMufasa]

Simmo,

Both BEFORE and AFTER triggers occur before COMMIT/ROLLBACK. If COMMITs occurred while you made changes, then you would lose the contents of your CURSOR.

As far as your "fragmentation" idea is concerned, remember that in Oracle, INSERTs, UPDATEs, and DELETEs happen to the data blocks as you execute the command -- Those actions are not "virtual" changes that wait until COMMIT happens to actually write to the data blocks. So, fragmentation that may occur happens before the COMMIT. If you issue a ROLLBACK (UNDO), then the Oracle server process puts data back the way it was before the transaction began.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:24 (25Apr04) UTC (aka "GMT" and "Zulu"), 13:24 (25Apr04) Mountain Time)