Re: PL/SQL Error

In article <258a1e3f.0110180417.302fa60d_at_posting.google.com>, tlmason840_at_aol.com says...
>
>I was trying to implement a trigger
> CREATE OR REPLACE TRIGGER answer_view_Trig
> AFTER UPDATE ON MON_Answer
> begin
> CREATE OR REPLACE VIEW Answer_View
> AS (SELECT A.MQ_ID, A.MQ_Number, A.MQ_Question, A.MP_ID, A.MQ_Type,
> B.MA_ID, B.MA_Answer, B.ME_ID
> FROM MON_Question A,
> MON_Answer B
> WHERE A.MQ_ID = B.MQ_ID)
>end
>
>and received the following error.
>

well, couple of issues.

o in order to do DDL in plsql, one must use dynamic sql. Since there ain't a version to be seen I can only say "see EXECUTE IMMEDIATE or DBMS_SQL depending on your version"

o DDL commits, Triggers cannot commit. An autonomous transaction in a trigger can commit however, there are serious side effects, especially in your case. What happens when the INSERT fires the trigger -- the view is created (and committed) and THEN the insert fails and gets rolled back? You have a view and no row!

o This trigger would create what appears to be a static view -- its the same view. The ONLY thing you seem to be accomplishing with this is a total slow down of your system, lots of cascading invalidations, lots of extra parsing (not to mention that ddl in itself is very expensive)

I recommend you rethink your processing. This looks like an extremely bad idea -- creating a view in a trigger. Why not just "create the view" instead of creating the trigger?

>PLS-00103: Encountered the symbol "(" when expecting one of the
>following:
> begin declare exit for goto if loop mod null pragma raise
> return select update while <an identifier>
> <a double-quoted delimited-identifier> <a bind variable> <<
> close current delete fetch lock insert open rollback
> savepoint set sql commit <a single-quoted SQL string>
>
>Can someone point me in a direction as to how to fix this error.
>
> Thanks,
> Tim Mason

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp