2

Below is my sample query, in which I first create new folder then update count then create a new tag and finally get new id.

  1. insert into folder (name)values("new");
  2. update folder set count = count + 1 where idfolder=1;
  3. insert into tag(name,folder_Id)values("dsdss",@pFolderId );
  4. SELECT last_insert_id() AS tag_id;

All queries are executed in same stored procedure in which @pFolderId is the id passed by user (as a parameter), foreign key constraint are applied on folderId of tag table.
When the user sends a valid folderId, it executes properly and I get a new tagid, but when the user sends an invalid folderid which does not exist in the folder table, it throws exception of foreign key.

In this case the first and second query is already executed and the 3rd query gives an error.

My question is how to avoid query from executing on any error of any query.

The first way is to use rollback, but when I used that it executes properly for 1 invalid folder request, but when on 2nd request it executed 1, 2 query and gave error on 3rd.

How can I achieve this and avoid execution on error?


Sample code (gathered from comments):

DELIMITER $$ 
CREATE DEFINER=root@localhost 
  PROCEDURE newClip (pFolderId VARCHAR(100)) 
BEGIN 
  DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING 
  BEGIN 
    SELECT SQLEXCEPTION.message; 
    ROLLBACK; 
  END; 
  SET @pFolderId = pFolderId; 
  START TRANSACTION; 
    insert into folder (name) 
      values ("new"); 
    update folder 
      set count = count + 1 
      where idfolder=1; 
    insert into tags (name,folderId)
      values ("dsdss",@pFolderId ); 
    SELECT last_insert_id() AS tag_id; 
  COMMIT; 
END
$$ 
  • Hi, Welcome to the site. Can you post the actual code or the stored procedure? – Tom V Dec 16 '15 at 13:14
  • Do you need BEGIN and COMMIT around the 4 statements? That way, the ROLLBACK rolls back all of the changes. – Rick James Dec 17 '15 at 0:44
  • Why does step #2 have a hard=coded idfolder; that won't be the one just inserted? – Rick James Dec 17 '15 at 0:45
  • DELIMITER $$ CREATE DEFINER=root@localhost PROCEDURE newClip(pFolderId VARCHAR(100)) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING BEGIN SELECT SQLEXCEPTION.message; ROLLBACK; END; SET @pFolderId = pFolderId; START TRANSACTION; insert into folder (name)values("new"); update folder set count = count + 1 where idfolder=1; insert into tags(name,folderId)values("dsdss",@pFolderId ); SELECT last_insert_id()AS tag_id; COMMIT; END$$ – pitu Dec 17 '15 at 3:33
  • #2 idfolder is hardcoded to increment the count, itz a sample code – pitu Dec 17 '15 at 3:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.