No Data Found Error In Trigger
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Trigger error “ORA-01403: no data found” when It is not fire up vote 0 down vote favorite I am creating a trigger below, as long as I insert something already existed in the table, then print other wise do nothing. create or replace trigger TR_insert Before INSERT On A For each row DECLARE l_act integer; l_name varchar(30); l_minute integer; Begin select count(1) into l_act From A Where Activity=:new.Activity; if(l_act>0) then DBMS_OUTPUT.PUT_LINE ('There is duplicate.'); else DBMS_OUTPUT.PUT_LINE(''); end if; end; It is working fine when I insert something existed, but when I insert new item, then it gives out error "SQL Error: ORA-01403: no data found", why is that please? sql oracle oracle-sqldeveloper share|improve this question asked Nov 24 '14 at 18:10 henduo qian 537 1 Why would you create a trigger rather than creating a unique constraint? –Justin Cave Nov 24 '14 at 18:12 Thanks for reply. Just want to learn to use trigger. –henduo qian Nov 24 '14 at 18:17 1 But this isn't a useful thing for a trigger to do. In general, a row-level trigger on A cannot query A-- that will generally cause a mutating table exception. Triggers should not be used to validate conditions that require querying other rows in the same table. –Justin Cave Nov 24 '14 at 18:19 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote accepted You're getting the error because your select does not return any value for your new item. The below shud work. begin select count(1) into l_act From A Where Activity=:new.Activity; if(l_act>0) then DBMS_OUTPUT.PUT_LINE ('There is duplicate.'); end if; exception when NO_DATA_FOUND then DBMS_OUTPUT.PUT_LINE(''); end; share|improve this answer answered Nov 24 '14 at 18:18 Shankar 796311 if(l_act>0) then... is pointless: if the code gets to that line, then there must be at least one row. –Allan Nov 24 '14 at 18:50 yeah, you're right, the if clause can be ignored. –Shankar Nov 24 '14 at 20:22 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using E
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, http://stackoverflow.com/questions/27111388/trigger-error-ora-01403-no-data-found-when-it-is-not-fire helping each other. Join them; it only takes a minute: Sign up ora-01403 no data found error,ora-04088 error during execution of trigger up vote 1 down vote favorite Hi I have this trigger create or replace TRIGGER trg_cust after insert or update on nbty_customer referencing old as old new as new for each row declare pragma autonomous_transaction; http://stackoverflow.com/questions/23390689/ora-01403-no-data-found-error-ora-04088-error-during-execution-of-trigger V_REQ VARCHAR2(10); begin IF(:NEW.cust_sal<1000) THEN select :NEW.cust_sal+:OLD.cust_sal INTO V_REQ FROM nbty_customer where cust_id=:old.cust_id; ELSE SELECT :NEW.cust_sal-:OLD.cust_sal INTO V_REQ FROM nbty_customer where cust_id=:old.cust_id; end if; merge into nbty_cache_cust using (select distinct :new.cust_id cust_id, :new.cust_name cust_name,V_REQ V_REQ from nbty_customer) b on (cust_nbty_id = b.cust_id) when matched then update set cust_nbty_name = b.cust_name, cust_nbty_sal = b.V_REQ when not matched then insert (cust_nbty_id, cust_nbty_name, cust_nbty_sal) values (b.cust_id, b.cust_name, b.V_REQ); commit; end; Which compiles properly but when an insertion is done on the table nbty_customer like for example insert into nbty_customer values('2','abc','200') It throws ora-01403 no data found error,ora-04088 error during execution of trigger Kindly help, I am not able to figure out what the issue is? oracle plsql share|improve this question edited Apr 30 '14 at 14:26 Ibrahim Apachi 1,31431543 asked Apr 30 '14 at 14:21 saniya mapari 388 We can't tell without the data. The fact is, there is a select out there yielding no results, which you don't catch. –Patrick Hofman Apr 30 '14 at 14:28 add a commen
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies http://stackoverflow.com/questions/7661273/no-data-found-in-trigger of this site About Us Learn more about Stack Overflow the company Business http://www.dbasupport.com/forums/showthread.php?8309-ORA-01403-no-data-found-how-do-you-solve-this Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign no data up No data found in trigger up vote 0 down vote favorite I have a problem with my trigger. It returns "no data found" and i don't know how to resolve it. Can you help me ? create or replace TRIGGER nb_action AFTER INSERT ON Message FOR EACH ROW DECLARE vAuteur integer; PRAGMA AUTONOMOUS_TRANSACTION; BEGIN SELECT id_auteur INTO vAuteur FROM Message where no data found id_message = :new.id_message; UPDATE Utilisateur SET nb_action=nb_action+1 where id_utilisateur=vAuteur; END ; sql oracle share|improve this question edited Oct 5 '11 at 12:26 AVD 72k1489139 asked Oct 5 '11 at 12:24 tristan 1 add a comment| 2 Answers 2 active oldest votes up vote 5 down vote Do not use autonomous transactions in normal code. The only time that autonomous transactions are really appropriate are cases when you want to write data to a log table whether or not the underlying action commits. For example, if you want to log an error, rollback the transaction, and raise the exception, you probably don't want the log message to be rolled back. You should absolutely never use an autonomous transaction to work around a mutating table exception which is, I assume, the reason you used an autonomous transaction here since the query against the Message table would raise a mutating table exception if it was not in an autonomous transaction. Fortunately, in this case, there is no need to query the table on which the trigger is defined and no need to use an autonomous transaction. Simply create or
data found, how do you solve this? If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Page 1 of 2 12 Last Jump to page: Results 1 to 10 of 13 Thread: ORA-01403: no data found, how do you solve this? Tweet Thread Tools Show Printable Version Email this Page… Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 03-22-2001,02:37 PM #1 coolmandba View Profile View Forum Posts Junior Member Join Date Dec 2000 Posts 87 Hi all, I'm trying do a 'INSERT' into a table and I received this message... ORA-01403: no data found What does it mean, and how do you solve it? Thanks. Reply With Quote 03-22-2001,03:14 PM #2 irehman View Profile View Forum Posts Senior Member Join Date Dec 2000 Location Virginia, USA Posts 455 I got this from Oracle Book. I don't know if this will help you. ORA-01403 no data found Cause: In a host language program, all records have been fetched. The return code from the fetch was +4, indicating that all records have been returned from the SQL query. Action: Terminate processing for the SELECT statement. Reply With Quote 03-22-2001,03:19 PM #3 coolmandba View Profile View Forum Posts Junior Member Join Date Dec 2000 Posts 87 Thanks. I also checked the documentation and yield exact the same content as what you got here, but it still doesn't solve my problem. Reply With Quote 03-22-2001,03:21 PM #4 irehman View Profile View Forum Posts Senior Member Join Date Dec 2000 Location Virginia, USA Posts 455 Can you post your insert statement with Table Defination? Reply With Quote 03-22-2001,03:48 PM #5 coolmandba View Profile View Forum Posts Junior Member Join Date Dec 2000 Posts 87 Actually, the problem laid on the trigger that associate with the table, the part starting with select nvl(keypart1_use,'nothing')..... if ....... If I comment out that section there is no problem, just that primary key platformid is not generated. So I'm not too sure if there is something wrong with it. I was trying to do insert as: INSERT INTO Platforms (Platform, DefAssignedToType, KeyPart1_Use, KeyPart2_Use, KeyPart3_Use, DistributedSystem, AllowNoCharge, SupportFac, VendorID) VALUES ('Test Platform', 'n/a','User ID','Password',null,0, 0,0,560); ************************** The trigger codes are: create or replace trigger tri_platforms before insert on platforms for each row declare stoo_selcnt integer; stoo_error integer; stoo_rowcnt integer := 1; stoo_crow