RE: Triggers problem

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 @insert 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

Oracle LazyDBA home page