I am trying to create a trigger (or something alike) that inserts a new row when one row is updated. The scenario is: I have a table of tasks that should be done every month. When a task is set to completed (this is the update statement) i want a new task to be created, with the same parameters but a diferent date. So I was thinking of an after update trigger that fires an insert statement onto the same table, but oracle won't allow this.
What is a good way to achive this?
Thanks in advance.
What is the error you are encountering? If it's mutating table, there are workarounds but they are clumsy. See this AskTom thread if you really want to do it. However I would recommend against triggers unless absolutely necessary. As long as we're linking to Tom Kyte, see his article "The Trouble with Triggers".
What you could do is in your code that executes the update, simply execute an insert after when the status is set to completed. I think that is a much better solution unless something precludes it.
