Home » SQL & PL/SQL » SQL & PL/SQL » to insert a record thru view
to insert a record thru view [message #252852] Fri, 20 July 2007 05:58 Go to next message
spsbombay
Messages: 29
Registered: June 2007
Junior Member


I have created table with following fields:
create table emp (EMP_ID NUMBER , ename varchar2(20),MGR_ID NUMBER , DEPT_ID NUMBER);

and a view on this table
CREATE VIEW EMPV AS
SELECT A.EMP_ID EMPID,A.ENAME EMPNAME,A.MGR_ID MGRID, B.ENAME MGRNAME FROM
EMP A LEFT OUTER JOIN EMP B ON (A.MGR_ID = B.EMP_ID)

i m trying to insert record thru this view but i m getting this error
ORA-01779: cannot modify a column which maps to a non key-preserved table

Is there any way to insert record thru this view.

Regards
Prashant
Re: to insert a record thru view [message #252853 is a reply to message #252852] Fri, 20 July 2007 06:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add a primary key on your table.

Which statement you execute?
Always copy and paste what you do.

And read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: to insert a record thru view [message #252855 is a reply to message #252853] Fri, 20 July 2007 07:06 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
No, you can only insert into either view over a single table, and perhaps on a join view where the join key is unique in both tables.

For yours to work, you would need a unique key on MGR_ID, but you probably don't want that column to be unique, because it would mean a manager could only have one employee.

Search the doco on INSTEAD OF triggers.

Ross Leishman
Re: to insert a record thru view [message #252868 is a reply to message #252855] Fri, 20 July 2007 07:52 Go to previous messageGo to next message
spsbombay
Messages: 29
Registered: June 2007
Junior Member

Thanx Ross... i m able to do the same with INSTEAD OF TRIGGER.

 create  or replace trigger emp_test 
 instead of insert on empv
 for each row
 begin
      insert into emp values (:new.empid,:new.empname,:new.mgrid,'');
 end;
 /


Michel, thanx for the suggestion. i will definitly take care of formatting the code on future.

Regards,
Prashant

Re: to insert a record thru view [message #252872 is a reply to message #252868] Fri, 20 July 2007 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What happens to manager name in your trigger?

If I do:
insert into empv (1,'Michel',10,'Joe');
What does 'Joe' go?

Does it not matter that dept_id of emp is not fit when you update through the view?

Many questions... Solution depends on the goal of the view.

Regards
Michel
Re: to insert a record thru view [message #252891 is a reply to message #252872] Fri, 20 July 2007 08:52 Go to previous message
spsbombay
Messages: 29
Registered: June 2007
Junior Member



It doesn't matter what value i m giving for manager name as i have not mentioned the manager name in insert statement in trigger.
it will simply fetch the same manager name when i query the view.

I am doing this on a sample table where i choosen dept_id can contain NULL values. So this time its not bothering me.
But in case there are any NOT NULL columns which are not present in the view then i have to take care for the same( can provide default value for that column if possible)

Regards,
Prashant
Previous Topic: returning distinct ID from both queries using OR
Next Topic: SQL query not calculating
Goto Forum:
  


Current Time: Wed Dec 07 18:48:50 CST 2016

Total time taken to generate the page: 0.10812 seconds