Now ... you should do this in a before insert trigger rather than in an after update trigger using :new.hiredate := sysdate.
- If you do this you should be aware, that your frontend (if using one) is not aware of the changes you did - so it has to requery the row.
- Default is a good idea if the column is not part of the insert statement you are using, otherwise, if you pass a hiredate = NULL, default will not have any effects unless you are on oracle 12
Thanks Karthick for your reply.
But here, can you clarify me one thing.
I have created after insert tirgger and in that i have placed the UPDATE statement.
My assumption is, after inserting a record into emp, it will fire the trigger and in that it will fire the update statement. But, where is the error of mutating occurs here.
Please correct me if wrong: Mutating error is somthing we can not select a table through trigger which in state of change.
From the document
The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.
This restriction applies to all triggers that use the
FOR EACH ROWclause. Views being modified in
OFtriggers are not considered mutating.
When a trigger encounters a mutating table, a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application.
In addtion to the other posts, here's a demo and solutions:
"Mutating table exceptions occur when we try to reference the triggering table in a query from within row-level trigger code" which is exactly what you're doing...