Hi, I have a table with a flag (DELETE = YES / NO )
I need that every time i do a DELETE ..... FROM table WHERE ID..
the flag change to delete = YES on that id.
In postgres i can do this with a trigger on delete, set the flag on YES and then interrup the delete.
On Oracle I'm Getting an error because of muttating table.
Instead of just deleting the row, you first update a column, that needs a trigger and a view.
Since the rows are left behind in the table, will your application work normally?
Any query against the table must have WHERE clause "DELETED_COL="YES", don't you think it is a over head?
How about a BEFORE DELETE FOR EACH ROW trigger that inserts the row into another table ? [It could insert it back to the same table with the DELETE_FLAG = 'Y' in an AFTER DELETE trigger if you REALLY want.]
For that level of effort one might do well to move the deleted rows to a new archive table instead of flagging them. My feeling is that it would be more optimizer firendly, and you wouldn't have to maintain indexes on the deleted rows.