Home » SQL & PL/SQL » SQL & PL/SQL » create trigger to create view but give me error (oracle 10g)
create trigger to create view but give me error [message #576024] Wed, 30 January 2013 14:50 Go to next message
mosman
Messages: 9
Registered: April 2005
Junior Member
i try to make trigger to execute immedate create or replace view

(
CREATE OR REPLACE TRIGGER xxxx
BEFORE INSERT ON table1
REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW
declare
l number ;
v_ddl varchar2(4000);
v_job number;

begin

v_ddl := 'create or replace view allp as select * from allx';
execute immediate v_ddl ;
end if;
end;
)

it give me error for insuffition privilage in execute immediate
and after i make
GRANT CREATE ANY TRIGGER TO user
give me error can't commit in trigger

any one can help me
Re: create trigger to create view but give me error [message #576025 is a reply to message #576024] Wed, 30 January 2013 14:54 Go to previous messageGo to next message
Michel Cadot
Messages: 60060
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to create the same view again and again each time a row is inserted in a (not related) table?
What are you trying to do? Or, if you prefer, why are you trying to do this?

Regards
Michel

[Updated on: Wed, 30 January 2013 14:55]

Report message to a moderator

Re: create trigger to create view but give me error [message #576026 is a reply to message #576024] Wed, 30 January 2013 14:56 Go to previous messageGo to next message
Michel Cadot
Messages: 60060
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

In the end, when you post such question, use SQL*Plus and copy and paste your session, the WHOLE session with the creation DDL of ALL objects involved.

Regards
Michel

[Updated on: Wed, 30 January 2013 14:56]

Report message to a moderator

Re: create trigger to create view but give me error [message #576027 is a reply to message #576024] Wed, 30 January 2013 15:21 Go to previous messageGo to next message
joy_division
Messages: 4559
Registered: February 2005
Location: East Coast USA
Senior Member
In addition to the point Michel mentioned, you are creating a view, so THAT is the privilege you need.
Re: create trigger to create view but give me error [message #576034 is a reply to message #576027] Thu, 31 January 2013 00:08 Go to previous messageGo to next message
Littlefoot
Messages: 19901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
give me error can't commit in trigger

DDL (CREATE VIEW in your case) implicitly commits (actually, it commits twice - before and after the DDL statement), and you can't commit in a trigger. Once again, to rephrase what people already asked: what problem are you trying to solve? (Are you coming from SQL Sever to Oracle technology, perhaps?)
Re: create trigger to create view but give me error [message #576459 is a reply to message #576034] Tue, 05 February 2013 04:04 Go to previous messageGo to next message
mvmkandan
Messages: 67
Registered: May 2010
Location: Trivendrum
Member
view is no need to create after each record is inserted into that table.

to avoid this error, we can use PRAGMA AUTONOMOUS_TRANSACTION

Veera
Re: create trigger to create view but give me error [message #576468 is a reply to message #576459] Tue, 05 February 2013 05:23 Go to previous messageGo to next message
cookiemonster
Messages: 11287
Registered: September 2008
Location: Rainy Manchester
Senior Member
No, you avoid the error by not creating the view in the trigger at all.
Re: create trigger to create view but give me error [message #576470 is a reply to message #576459] Tue, 05 February 2013 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 60060
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you (think you) need autonomous_transaction in a trigger then most likely something is wrong in your design.

Regards
Michel
Re: create trigger to create view but give me error [message #576513 is a reply to message #576470] Tue, 05 February 2013 15:20 Go to previous message
Bill B
Messages: 1141
Registered: December 2004
Senior Member
A view in oracle is NOT like a temp table in TSQL. It is only a stored query and only needs to be created ONCE.
Previous Topic: Customized Query Builder Architecture
Next Topic: How to retrieve unique values from a string
Goto Forum:
  


Current Time: Fri Dec 26 01:22:20 CST 2014

Total time taken to generate the page: 0.08000 seconds