Home » SQL & PL/SQL » SQL & PL/SQL » Logical Delete
Logical Delete [message #192239] Mon, 11 September 2006 09:08 Go to next message
Xeneize
Messages: 6
Registered: September 2006
Junior Member
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
Re: Logical Delete [message #192266 is a reply to message #192239] Mon, 11 September 2006 11:21 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
create a view on the table. create an INSTEAD OF trigger on the view - the trigger will do the update. have the users delete from the view, NOT THE TABLE.
Re: Logical Delete [message #192291 is a reply to message #192239] Mon, 11 September 2006 12:42 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
You might try updating the table value from the trigger itself if you are doing it from a procedure. See http://asktom.oracle.com/pls/ask/f?p=4950:8:350731402480814462::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:469621337269 for a discussion of this issue and how to deal with it.
Re: Logical Delete [message #192771 is a reply to message #192266] Wed, 13 September 2006 08:57 Go to previous message
Xeneize
Messages: 6
Registered: September 2006
Junior Member
shoblock wrote on Mon, 11 September 2006 11:21

create a view on the table. create an INSTEAD OF trigger on the view - the trigger will do the update. have the users delete from the view, NOT THE TABLE.


Thankz, that works perfect.
Previous Topic: cursor & decode
Next Topic: retrieve store procedure DDL in other schema
Goto Forum:
  


Current Time: Wed Dec 07 13:02:37 CST 2016

Total time taken to generate the page: 0.06023 seconds