By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,986 Members | 1,905 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,986 IT Pros & Developers. It's quick & easy.

Problem inserting into a view

priyan
P: 54
Hi all,
I had created a view from a table employee from my database. View is created sucuessfully and when i inserted data into employee table it is also updated in the view but i tried to insert data into my view but it shows an error.
Expand|Select|Wrap|Line Numbers
  1. create view empp as select * from employee
  2.  
Expand|Select|Wrap|Line Numbers
  1. Name                    DOB             id
  2. ---------           -----------       ------
  3. priya              2007-08-20 02:21:41     2
  4. chitra             2007-08-20 03:52:29     3
  5. lavanya          2007-09-13 16:42:43      4
  6. arun              2007-08-20 00:30:11      1
  7.  
When I execute this query:
Expand|Select|Wrap|Line Numbers
  1. insert into empp values('Kirthi',now(),5)
  2.  
Expand|Select|Wrap|Line Numbers
  1. ERROR: cannot insert into a view
  2. SQL state: 0A000
  3. Hint: You need an unconditional ON INSERT DO INSTEAD rule.
  4.  

Please help to solve this problem... But inserting into a view in oracle is working good......please help me..

Thanks
priyan..
Sep 20 '07 #1
Share this Question
Share on Google+
16 Replies


amitpatel66
Expert 100+
P: 2,367
Hi all,
I had created a view from a table employee from my database. View is created sucuessfully and when i inserted data into employee table it is also updated in the view but i tried to insert data into my view but it shows an error.
Expand|Select|Wrap|Line Numbers
  1. create view empp as select * from employee
  2.  
Expand|Select|Wrap|Line Numbers
  1. Name                    DOB             id
  2. ---------           -----------       ------
  3. priya              2007-08-20 02:21:41     2
  4. chitra             2007-08-20 03:52:29     3
  5. lavanya          2007-09-13 16:42:43      4
  6. arun              2007-08-20 00:30:11      1
  7.  
When I execute this query:
Expand|Select|Wrap|Line Numbers
  1. insert into empp values('Kirthi',now(),5)
  2.  
Expand|Select|Wrap|Line Numbers
  1. ERROR: cannot insert into a view
  2. SQL state: 0A000
  3. Hint: You need an unconditional ON INSERT DO INSTEAD rule.
  4.  

Please help to solve this problem... But inserting into a view in oracle is working good......please help me..

Thanks
priyan..
Write an INSTEAD OF TRIGGER on this view and insert the record in to table emp in the trigger
Sep 20 '07 #2

priyan
P: 54
Write an INSTEAD OF TRIGGER on this view and insert the record in to table emp in the trigger

Hi amitpatel,
Thanks for quick reply but can explain me in detail with example.
If u do like that it would be very helpful for me...... Please help me.....
Sep 20 '07 #3

amitpatel66
Expert 100+
P: 2,367
Hi amitpatel,
Thanks for quick reply but can explain me in detail with example.
If u do like that it would be very helpful for me...... Please help me.....

Check about writing Triggers in Postgresql here

So you can implement a rulw ON INSERT DO INSTEAD for a view and then INSERT the data into the base table on which the view is based.You can look for LOGIC of ON INSERT DO INSTEAD rule in the link that I have provided.
DO POST your comments after trying this.

Thanks
Amit
Sep 20 '07 #4

priyan
P: 54
Check about writing Triggers in Postgresql here

So you can implement a rulw ON INSERT DO INSTEAD for a view and then INSERT the data into the base table on which the view is based.You can look for LOGIC of ON INSERT DO INSTEAD rule in the link that I have provided.
DO POST your comments after trying this.

Thanks
Amit
hi
I created rule as if u said
Expand|Select|Wrap|Line Numbers
  1. CREATE RULE emp_ins AS ON INSERT TO empp
  2.     DO INSTEAD
  3.     INSERT INTO empp VALUES ('Kirthi',now(),5);
  4.  
It runs sucessfully but when I executed select command in both the table and view but it does not get inserted. I don't know wheather my command above given is correct or not.....Please help me by giving me an example.........If u give an example it would be very much helpfull for me. please do me this help.....

Thanks priyan.
Sep 20 '07 #5

amitpatel66
Expert 100+
P: 2,367
hi
I created rule as if u said
Expand|Select|Wrap|Line Numbers
  1. CREATE RULE emp_ins AS ON INSERT TO empp
  2.     DO INSTEAD
  3.     INSERT INTO empp VALUES ('Kirthi',now(),5);
  4.  
It runs sucessfully but when I executed select command in both the table and view but it does not get inserted. I don't know wheather my command above given is correct or not.....Please help me by giving me an example.........If u give an example it would be very much helpfull for me. please do me this help.....

Thanks priyan.
Try below code:
Expand|Select|Wrap|Line Numbers
  1. CREATE RULE emp_ins AS ON INSERT TO empp
  2.     DO INSTEAD
  3.     INSERT INTO emp VALUES (NEW.name,now(),NEW.id);
  4.  
Now try inserting into empp and post back
Sep 20 '07 #6

Expert 100+
P: 700
hi
I created rule as if u said
Expand|Select|Wrap|Line Numbers
  1. CREATE RULE emp_ins AS ON INSERT TO empp
  2.     DO INSTEAD
  3.     INSERT INTO empp VALUES ('Kirthi',now(),5);
  4.  
It runs sucessfully but when I executed select command in both the table and view but it does not get inserted. I don't know wheather my command above given is correct or not.....Please help me by giving me an example.........If u give an example it would be very much helpfull for me. please do me this help.....

Thanks priyan.
Sholudn't it be

CREATE RULE emp_ins AS ON INSERT TO empp
DO INSTEAD
INSERT INTO employee VALUES ('Kirthi',now(),5);

and I think the better solution would be

CREATE RULE emp_ins AS ON INSERT TO empp
DO INSTEAD
INSERT INTO employee VALUES (NEW.name,now(),5);
Sep 20 '07 #7

priyan
P: 54
Sholudn't it be

CREATE RULE emp_ins AS ON INSERT TO empp
DO INSTEAD
INSERT INTO employee VALUES ('Kirthi',now(),5);

and I think the better solution would be

CREATE RULE emp_ins AS ON INSERT TO empp
DO INSTEAD
INSERT INTO employee VALUES (NEW.name,now(),5);

hi
I tried ur query it is executed sucessfully and now i tried to insert into the view empp but I got the following error.
Expand|Select|Wrap|Line Numbers
  1.  
  2.       CREATE RULE emp2_ins AS ON INSERT TO empp
  3.              DO INSTEAD
  4.              INSERT INTO employee VALUES (NEW.name,now(),NEW.id);
  5.  
  6. insert into empp values('shylaja',now(),5);
  7.  
Expand|Select|Wrap|Line Numbers
  1. ERROR: infinite recursion detected in rules for relation "empp"
  2. SQL state: 42P17
  3.  
Sep 20 '07 #8

amitpatel66
Expert 100+
P: 2,367
hi
I tried ur query it is executed sucessfully and now i tried to insert into the view empp but I got the following error.
Expand|Select|Wrap|Line Numbers
  1.  
  2.       CREATE RULE emp2_ins AS ON INSERT TO empp
  3.              DO INSTEAD
  4.              INSERT INTO employee VALUES (NEW.name,now(),NEW.id);
  5.  
  6. insert into empp values('shylaja',now(),5);
  7.  
Expand|Select|Wrap|Line Numbers
  1. ERROR: infinite recursion detected in rules for relation "empp"
  2. SQL state: 42P17
  3.  
Try below code:
Expand|Select|Wrap|Line Numbers
  1.  
  2.       CREATE RULE emp2_ins AS ON INSERT TO empp
  3.              DO INSTEAD
  4.              INSERT INTO employee VALUES (NEW.name,NEW.dob,NEW.id);
  5.  
Sep 20 '07 #9

Expert 100+
P: 700
hi
I tried ur query it is executed sucessfully and now i tried to insert into the view empp but I got the following error.
Expand|Select|Wrap|Line Numbers
  1.  
  2.       CREATE RULE emp2_ins AS ON INSERT TO empp
  3.              DO INSTEAD
  4.              INSERT INTO employee VALUES (NEW.name,now(),NEW.id);
  5.  
  6. insert into empp values('shylaja',now(),5);
  7.  
Expand|Select|Wrap|Line Numbers
  1. ERROR: infinite recursion detected in rules for relation "empp"
  2. SQL state: 42P17
  3.  

Did u delete the rule u created earlier?
Sep 20 '07 #10

priyan
P: 54
Did u delete the rule u created earlier?
no i din't delete it I have to delete it means how to delete it?
Sep 20 '07 #11

Expert 100+
P: 700
no i din't delete it I have to delete it means how to delete it?
drop rule emp_ins on empp.
Sep 20 '07 #12

priyan
P: 54
drop rule emp_ins on empp.
I deleted all the rule and created a new one and when execute insert statement it is showing me the same error......
Sep 20 '07 #13

amitpatel66
Expert 100+
P: 2,367
I deleted all the rule and created a new one and when execute insert statement it is showing me the same error......
Instead of using now(), specify some other date and then try inserting in to empp and check if the error is coming or not?
Sep 20 '07 #14

priyan
P: 54
Instead of using now(), specify some other date and then try inserting in to empp and check if the error is coming or not?

no now also same error.......
Sep 20 '07 #15

Expert 100+
P: 700
I deleted all the rule and created a new one and when execute insert statement it is showing me the same error......
I believe u created only this one rule (and deleted all the others)

CREATE RULE emp2_ins AS ON INSERT TO empp
DO INSTEAD
INSERT INTO employee VALUES (NEW.name,now(),NEW.id);

What does \d empp show
Sep 20 '07 #16

priyan
P: 54
I believe u created only this one rule (and deleted all the others)

CREATE RULE emp2_ins AS ON INSERT TO empp
DO INSTEAD
INSERT INTO employee VALUES (NEW.name,now(),NEW.id);

What does \d empp show

Hi
Thank u very much......It is now working correctly...... Once again thanks a lot for patiently answering all my questions and clearing my doubts......
Sep 21 '07 #17

Post your reply

Sign in to post your reply or Sign up for a free account.