Home » SQL & PL/SQL » SQL & PL/SQL » View Insert (Oracle9i)
View Insert [message #449150] Sat, 27 March 2010 07:31 Go to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Hi All,
I have created a view in that the primary key column is not included,so that we cannot insert data into that view,but i want to insert data into that view without using Instead of Triggers.

Please help me.




CM: swapped version and title so they're the right way round.

[Updated on: Sat, 27 March 2010 16:03] by Moderator

Report message to a moderator

Re: Oracle9i [message #449156 is a reply to message #449150] Sat, 27 March 2010 09:54 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>I have created a view in that the primary key column is not included,so that we cannot insert data into that view,but i want to insert data into that view without using Instead of Triggers.


You can't get there from here.
What value should the PK contain?
Re: Oracle9i [message #449158 is a reply to message #449150] Sat, 27 March 2010 10:00 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
prakasha wrote on Sat, 27 March 2010 07:31

I have created a view in that the primary key column is not included,so that we cannot insert data into that view



Not only primary key ..even if you don't include any other column with "NOT NULL" constraint then also you wont be insert the values either.
Re: Oracle9i [message #449160 is a reply to message #449150] Sat, 27 March 2010 10:02 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
And BTW

1/ Why you want to use view to insert the values ?
2/ Cant you directly insert in table ?
Re: Oracle9i [message #449173 is a reply to message #449160] Sat, 27 March 2010 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@rahulvb

1/ Because it is not allowed to end user to access the table only the view
2/ No because some columns and rows must be hidden from users.

@prakasha

You can't.

Regards
Michel

[Updated on: Sat, 27 March 2010 11:07]

Report message to a moderator

Re: Oracle9i [message #449174 is a reply to message #449150] Sat, 27 March 2010 11:19 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Assuming you could do the insert, what value would you want for the primary key when the row finnaly hit the table?

Kevin
Re: Oracle9i [message #449180 is a reply to message #449150] Sat, 27 March 2010 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is possible if you have a before insert trigger on the table:
SQL> create table t (id integer, val varchar2(10));

Table created.

SQL> alter table t add primary key (id);

Table altered.

SQL> create sequence s;

Sequence created.

SQL> create or replace trigger t_bi before insert on t for each row
  2  begin
  3    select s.nextval into :new.id from dual;
  4  end;
  5  /

Trigger created.

SQL> create view v as select val from t;

View created.

SQL> insert into v values('test');

1 row created.

SQL> select * from v;
VAL
----------
test

1 row selected.

SQL> select * from t;
        ID VAL
---------- ----------
         1 test

1 row selected.

Regards
Michel
Re: Oracle9i [message #449190 is a reply to message #449150] Sat, 27 March 2010 12:00 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
In the end I might go back and ask why the restriction on not using INSTEAD-OF-TRIGGERS.

Kevin
Re: Oracle9i [message #449191 is a reply to message #449190] Sat, 27 March 2010 12:13 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Kevin Meade wrote on Sat, 27 March 2010 12:00
In the end I might go back and ask why the restriction on not using INSTEAD-OF-TRIGGERS.

Kevin


precisely. Smile
Re: Oracle9i [message #449195 is a reply to message #449190] Sat, 27 March 2010 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Kevin Meade wrote on Sat, 27 March 2010 18:00
In the end I might go back and ask why the restriction on not using INSTEAD-OF-TRIGGERS.

Kevin

Maybe they are not useful in this case.
For instance, some users have the right to insert through the table and others only through the view, then the trigger must be on the table not on the view.

And also maybe it is just an interview question that has no purpose but check the interviewee's skills.

Regards
Michel

[Updated on: Sat, 27 March 2010 12:33]

Report message to a moderator

Re: Oracle9i [message #449196 is a reply to message #449150] Sat, 27 March 2010 12:55 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
good points Michel.

In the first case I would suggest all access be done through the view and no access to the table directly.

In the second, talk till you think you have said enough and then say no more.

Kevin
Re: Oracle9i [message #449201 is a reply to message #449196] Sat, 27 March 2010 13:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In the first case I would suggest all access be done through the view and no access to the table directly.

This may not be possible.
For instance if you have a view that hides a SSN column or some departments (you are allowed to view only yours) for confidentiality then you must have access to the table to insert new SSN (or modified a current one which is/was possible in France for some very rare cases) or for the boss to see all departments (although with this example we can do it with a single view definition, should find a better example but you see what I mean).

Regards
Michel
Re: Oracle9i [message #449203 is a reply to message #449150] Sat, 27 March 2010 15:16 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
It is always possible, one just needs multiple views. By definition when you change the record layout or the rows selected, you have created a different rowsource and thus you should have a different object.

The question like many goes deep into system design and wether or not the people in charge of the database have a plan in mind of not. I never allow access to a real table any more. I routinely use multiple view layers. But I suppose I am far ahead of most people designing oracle databases these days. I believe it is no long sufficient just to apply third normal form and be done. One must consider security and row seperation issues (as you have indicated), as well as historical access and other hidden database behaviors, and additionally how general access to the system will occur.

So for me, unless the system being created is one that from the start is intended to handle massive amounts of concurrent transactions, then there is to my mind no reason on to use views and instead of triggers in one or more layers above the tables.

I talk to much.

Kevin
Previous Topic: Execute privilege
Next Topic: Partition
Goto Forum:
  


Current Time: Sun Sep 25 17:59:56 CDT 2016

Total time taken to generate the page: 0.07456 seconds