Q
Problem solve Get help with specific problems with your technologies, process and projects.

Trigger to populate the date field on a table being updated

I need a trigger that populates a date field in the same table on which I am doing an insert or update. How can I achieve this without the mutating error that I am currently getting?

You can perform this quite easily. I'll give you an example which you can modify to suit your needs. For my example, I'll create a simple table:

ORA9I SQL> create table test (
  2  id number,
  3  day_time date);

Table created.
Next, I'll create a trigger on this table:
ORA9I SQL> create trigger test_trig
  2  before insert on test
  3  for each row
  4  begin
  5     :new.day_time := sysdate;
  6  end;
  7  /

Trigger created.
Notice that this trigger doesn't issue an UPDATE to modify the column. Instead, it makes the "new" value of this column equal to SYSDATE.

Now, we can test the trigger as follows:

ORA9I SQL> insert into test (id) values (1);

1 row created.

ORA9I SQL> select * from test;

        ID DAY_TIME
---------- ---------
         1 09-DEC-02
The trigger performed as expected!

For More Information


Dig Deeper on Oracle database design and architecture

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close