-
Logical Delete
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.
How can i fix this problem in oracle?
Thank
-
Could you do the delete via a view with an INSTEAD OF trigger?
http://download-uk.oracle.com/docs/c...13trg.htm#8307
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Create a view on the table and define Instead of trigger
for example...
base table - tab
view - vw
Code:
create or replace trigger v1
INSTEAD of delete on vw
begin
update tab
set deleted = 'Y'
where ....;
end;
and run delete command on the view
Last edited by tabreaz; 09-11-2006 at 02:30 PM.
-
You update the col with "YES" or "NO" for a ID, then you delete the row.
Why do you want to update the col?
Tamil
-
Hi tamil,
HTML Code:
Xeneize:
In postgres i can do this with a trigger on delete, set the flag on YES and then interrup the delete.
The row does not need to deleted, set the column deleted to be 'YES' when ever a delete command is run.
-
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?
Tamil
-
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.]
Alternatively look into workspace manager which does this sort of thing
http://download-west.oracle.com/docs...nt.htm#1002069
-
Originally Posted by tabreaz
Create a view on the table and define Instead of trigger
for example...
base table - tab
view - vw
Code:
create or replace trigger v1
INSTEAD of delete on vw
begin
update tab
set deleted = 'Y'
where ....;
end;
and run delete command on the view
Thankz, that works perfect.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|