One way around the problem (if possible) is to remove the table VIJAY from the view VIEWVIJAY and use :NEW values in place of the columns from the table VIJAY when inserting into MASTERTAB. Obviously you'll need to change the WHERE clause on the SELECT statement as well.
HTH
Cardy
P.S. (And this goes for everyone) - When replying to a solution to your problem please send the email to the LazyDBA forum not just to the person who replied to your problem.
-----Original Message-----
From: v [mailto:v.[Email address protected]
Sent: 31 May 2005 09:14
To: Cardus, Ian
Subject: RE: Triggers problem
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as normal
**Anything below this line is the original email text
Yes you are very right ... the error is mutating table..
but see the scenerio.. of mine ..
i am creating a table which has almost all the information of the
transaction .. and the table where first entry is made is VIJAY and the
primary key is ID_V , now there are more information required from other
tables regarding this ID_V
so the view VIEWVIJAY gathers all the information from different tables and
those values i insert in the master table MASTERTAB
and thus the view VIEWVIJAY is using the table VIJAY to get ID_V and
collecting information from other tables..
now as such i am not creating an entire new application .. i am modifying
the application where i am doing this to avoids joins in the query and just
getting data from MASTERTAB..
How do i achieve this . please explain .. or if you have any other idea of
doing it
Regards
Vijay Shah
Software Developer
|---------+--------------------------------------------------------->
| | "Cardus Ian " |
| | [Email address protected]
| | azyDBA.com> |
| | |
| | 05/31/2005 11:55 AM |
| | |
|---------+--------------------------------------------------------->
>--------------------------------------------------------------------------------------------------------------------------------------------------|
| |
| To: "LazyDBA Discussion" [Email address protected] |
| cc: |
| Subject: RE: Triggers problem |
>--------------------------------------------------------------------------------------------------------------------------------------------------|
If VIEWVIJAY is a view of the table that you're inserting into then I
suspect the error is a "mutating table" error. You can't reference the
table you're changing in a "FOR EACH ROW" trigger. To check change your
WHEN OTHERS clause to
WHEN OTHERS THEN INSERT INTO TBL_TEMP_ERR VALUES (I, 'ERROR WHILE INSERT
OTHERS: ' || SQLCODE);
or try
WHEN OTHERS THEN INSERT INTO TBL_TEMP_ERR VALUES (I, 'ERROR WHILE INSERT
OTHERS: ' || SQLERRM);
if your column is large enough.
HTH
Cardy
-----Original Message-----
From: Biswa R Singh
[mailto:oracledba-ezmlmshield-x50665152.[Email address protected]
Sent: 31 May 2005 07:58
To: LazyDBA Discussion
Subject: Re: Triggers problem
Hi Vijay,
I think the problem is the way you initialised your variable I in the
declaration section.
I NUMBER := :NEW.ID_V;
Now you have used I throughout the trigger. Can you please replace I with
:NEW.ID_V where you used and compile the trigger. I hope that will run
successfully. I think that initialisation is not leagal so the control is
failing in the 1st insert statement and going to the WHEN OTHERS of the
exception section. Also if you can use the :NEW.ID_V directly when you
require then there is no point to store that in any variable. Also
:NEW.ID_V is more meaningful.
Thanks & Regards
Biswa Ranjan Singh
Software Engineer
Telecom & Media (SPRINT Wave 1.1)
IBM Global Services India Ltd.
Tel : +91 80 220 79999 Extn : 5112
"v" <oracledba-ezmlmshield-x14427938.[Email address protected]
05/31/2005 12:13
To
"LazyDBA Discussion" <[Email address protected]
cc
Subject
Triggers problem
Hello,
I am trying to create trigger [Email address protected] into a table so i wrote
CREATE OR REPLACE TRIGGER V_INSERT_TRIGGER
AFTER INSERT ON VIJAY FOR EACH ROW
DECLARE
I NUMBER := :NEW.ID_V;
BEGIN
INSERT INTO TBL_TEMP_ERR VALUES (I, 'STARTING INSERTING ');
INSERT INTO MASTERTAB SELECT * FROM VIEWVIJAY WHERE ID_V = I;
EXCEPTION
WHEN NO_DATA_FOUND THEN INSERT INTO TBL_TEMP_ERR VALUES (I, 'ERROR WHILE
INSERT NO DATA FOUND');
WHEN INVALID_NUMBER THEN INSERT INTO TBL_TEMP_ERR VALUES (I, 'ERROR WHILE
INSERT INVALID NUMBER');
WHEN TOO_MANY_ROWS THEN INSERT INTO TBL_TEMP_ERR VALUES (I,'ERROR WHILE
INSERT TOO MANY ROWS IN SELECT');
WHEN VALUE_ERROR THEN INSERT INTO TBL_TEMP_ERR VALUES (I,'ERROR WHILE
INSERT VALUE ERROR');
WHEN NOT_LOGGED_ON THEN INSERT INTO TBL_TEMP_ERR VALUES (I,'ERROR WHILE
INSERT NOT LOGGED ON');
WHEN PROGRAM_ERROR THEN INSERT INTO TBL_TEMP_ERR VALUES (I,'ERROR WHILE
INSERT PROGRAM ERROR');
WHEN STORAGE_ERROR THEN INSERT INTO TBL_TEMP_ERR VALUES (I,'ERROR WHILE
INSERT STORAGE ERROR');
WHEN TIMEOUT_ON_RESOURCE THEN INSERT INTO TBL_TEMP_ERR VALUES (I,'ERROR
WHILE INSERT TIMEOUT ON RESOURCE');
WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('PRBLM');
WHEN OTHERS THEN INSERT INTO TBL_TEMP_ERR VALUES (I, 'ERROR WHILE INSERT
OTHERS');
END V_INSERT_TRIGGER;
But when this is executed on insert of any record in table VIJAY it is
giving an entry in tbl_temp_err with record
the new id , ERROR WHILE INSERT OTHERS
and what is the error i do not know .. how do i solve this error
Vijay Shah
Software Developer
--------------------------
This message contains confidential information and is intended only for
the
individual named. If you are not the named addressee you should not
disseminate, distribute or copy this email. Please notify the sender
immediately by email if you have received this email by mistake and delete
this email from your system.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:
http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:
http://www.lazydba.com/legal.html
*****************************************************************************
This e-mail and its attachments are confidential and are intended for the
above named recipient only. If this has come to you in error, please notify
the sender immediately and delete this e-mail from your system. You must
take no action based on this, nor must you copy or disclose it or any part
of its contents to any person or organisation. Statements and opinions
contained in this email may not necessarily represent those of Littlewoods
Group Limited or its subsidiaries. Please note that e-mail communications
may be monitored. The Registered Office of Littlewoods Group Limited and
its subsidiaries is 100 Old Hall Street, Liverpool, L70 1AB. Registered
number of Littlewoods Group Limited is 5059352.
*****************************************************************************
This message has been scanned for viruses by BlackSpider MailControl -
www.blackspider.com
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:
http://www.lazydba.com/legal.html
Oracle LazyDBA home page