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-02The trigger performed as expected!
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.