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.
- insert into folder (name)values("new");
- update folder set count = count + 1 where idfolder=1;
- insert into tag(name,folder_Id)values("dsdss",@pFolderId );
- 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 $$