Q

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?

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close