Home » SQL & PL/SQL » SQL & PL/SQL » Deletion of record in a view reflecting in base table
Deletion of record in a view reflecting in base table [message #246237] Wed, 20 June 2007 06:52 Go to next message
hanish_reddy
Messages: 3
Registered: April 2007
Location: Chennai
Junior Member
I created a view on base table.
If I delete a record in a view also deleting that particular record in the base table.
In real time there may be so many views on a single base table.
For every deletion of a record in the view associated with this table deletes a record from the base table and it will cause to loose data.
Is there any alternative for this?

for example:

1. Created a view named 'emp_view' on the base table 'emp'

create view emp_view as select * from emp;

2. Deleted a record from 'emp_view' for empno 7566.

delete from emp_view where empno=7566;

3. If I query for empno=7566 in 'emp' table, it is retrieving 0 rows.
Re: Deletion of record in a view reflecting in base table [message #246243 is a reply to message #246237] Wed, 20 June 2007 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A view is a view nothing but a way to see real data.
Delete a row in the row is actually delete a row in the table.
Same thing as delete it in the table.
This is the expected behaviour.

Shooting someone through a window has the same result than shooting him directly in the room.

What do you want?
To delete a row in the view but it is not deleted in the table.
So it is not a view on the table but another table which has its own life.

Regards
Michel
Re: Deletion of record in a view reflecting in base table [message #246244 is a reply to message #246243] Wed, 20 June 2007 07:20 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Shooting someone through a window has the same result than shooting him directly in the room.
Laughing GREAT analogy
Re: Deletion of record in a view reflecting in base table [message #246247 is a reply to message #246237] Wed, 20 June 2007 07:27 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I don't really know what you mean. Do you want to be able to delete from the view without deleting from the base table ? Given that the view is just a representation of the data in the table, I don't see how you would achieve this.

You could use an "instead of" trigger on the view and have column on the table called is_deleted. You can then amend the view to be "select * from table where is_deleted is null". Your trigger could then amend is_deleted to 'Y'.

Previous Topic: How to change the structure of a table permanently
Next Topic: Oracle UTL_FILE.Read Error (Ora-29284)
Goto Forum:
  


Current Time: Fri Dec 02 16:48:21 CST 2016

Total time taken to generate the page: 0.23360 seconds