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
Oracle LazyDBA home page