Ask the Expert

Timestamping all rows of some tables on insert and update

I want to timestamp all the rows of some tables, whenever they are inserted or updated. Insertion is no problem - just set DEFAULT sysdate - but I'm stuck on updates. I thought I could use a trigger, but I get an error ORA-04091, that the table is mutating - basically I can't trigger an update in a row that is being updated. Fair enough, I guess, but how could I get around this?

    Requires Free Membership to View

You are getting an ORA-4091 table mutating because probably your UPDATE statement in the trigger is causing the entire table to mutate. This is a common mistake by novice programmers. Instead, the trigger approach is good; try writing:

  :new.timestamp_column := SYSDATE;
This should enter a SYSDATE every time you update the row.

For More Information


This was first published in July 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: