Problem inserting into a view

**amitpatel66** · Sep 20 '07, 06:17 AM

Originally posted by priyan

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.
[code=sql]
create view empp as select * from employee
[/code]
[code=text]
Name DOB id
--------- ----------- ------
priya 2007-08-20 02:21:41 2
chitra 2007-08-20 03:52:29 3
lavanya 2007-09-13 16:42:43 4
arun 2007-08-20 00:30:11 1
[/code]
When I execute this query:
[code=sql]
insert into empp values('Kirthi' ,now(),5)
[/code]
[code=text]
ERROR: cannot insert into a view
SQL state: 0A000
Hint: You need an unconditional ON INSERT DO INSTEAD rule.
[/code]

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

Thanks
priyan..

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

**priyan** · Sep 20 '07, 06:23 AM

Originally posted by amitpatel66

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.....

**amitpatel66** · Sep 20 '07, 06:57 AM

Originally posted by priyan

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

**priyan** · Sep 20 '07, 07:31 AM

Originally posted by amitpatel66

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
[code=sql]
CREATE RULE emp_ins AS ON INSERT TO empp
DO INSTEAD
INSERT INTO empp VALUES ('Kirthi',now() ,5);
[/code]
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.

**amitpatel66** · Sep 20 '07, 08:17 AM

Originally posted by priyan

hi
I created rule as if u said
[code=sql]
CREATE RULE emp_ins AS ON INSERT TO empp
DO INSTEAD
INSERT INTO empp VALUES ('Kirthi',now() ,5);
[/code]
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:
[code=sql]
CREATE RULE emp_ins AS ON INSERT TO empp
DO INSTEAD
INSERT INTO emp VALUES (NEW.name,now() ,NEW.id);
[/code]

Now try inserting into empp and post back

**rski** · Sep 20 '07, 08:18 AM

Originally posted by priyan

hi
I created rule as if u said
[code=sql]
CREATE RULE emp_ins AS ON INSERT TO empp
DO INSTEAD
INSERT INTO empp VALUES ('Kirthi',now() ,5);
[/code]
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);

**priyan** · Sep 20 '07, 08:53 AM

Originally posted by rski

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.
[code=sql]

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

insert into empp values('shylaja ',now(),5);
[/code]
[code=text]
ERROR: infinite recursion detected in rules for relation "empp"
SQL state: 42P17
[/code]

**amitpatel66** · Sep 20 '07, 08:57 AM

Originally posted by priyan

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.
[code=sql]

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

insert into empp values('shylaja ',now(),5);
[/code]
[code=text]
ERROR: infinite recursion detected in rules for relation "empp"
SQL state: 42P17
[/code]

Try below code:
[code=sql]

CREATE RULE emp2_ins AS ON INSERT TO empp
DO INSTEAD
INSERT INTO employee VALUES (NEW.name,NEW.d ob,NEW.id);
[/code]

**rski** · Sep 20 '07, 08:59 AM

Originally posted by priyan

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.
[code=sql]

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

insert into empp values('shylaja ',now(),5);
[/code]
[code=text]
ERROR: infinite recursion detected in rules for relation "empp"
SQL state: 42P17
[/code]

Did u delete the rule u created earlier?

**priyan** · Sep 20 '07, 09:05 AM

Originally posted by rski

Did u delete the rule u created earlier?

no i din't delete it I have to delete it means how to delete it?

**rski** · Sep 20 '07, 09:43 AM

Originally posted by priyan

no i din't delete it I have to delete it means how to delete it?

drop rule emp_ins on empp.

**priyan** · Sep 20 '07, 09:52 AM

Originally posted by rski

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......

**amitpatel66** · Sep 20 '07, 09:53 AM

Originally posted by priyan

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?

**priyan** · Sep 20 '07, 10:01 AM

Originally posted by amitpatel66

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.......

Problem inserting into a view

Problem inserting into a view

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment