Stored procedure transactions on delete??

I have a stored procedure that receives an id from an asp page and deletes
the records from three different tables. Do I need to handle errors in this
with transactions? If I tried to delete from a table where there was no
record it doesn't throw an error does it? I have removed a record from one
table manually and I can't get the error raised.

Should I do anything? I don't think I need to but ..maybe I am missing
something here.

Stored procedure below

Thanks
Mike

Stored procedure:CREATE PROCEDURE dbo.osp_delete_Single_Inst_Schd

@ScheduleID int

AS

Begin Transaction
Delete from Tab_Inst_Schedules where Schedule_ID = @ScheduleID
Delete from Tab_Instr_Sched_Comments where Schedule_ID = @ScheduleID
Delete from Tab_Instr_Sched_Details where Schedule_ID = @ScheduleID

IF @@ERROR != 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('There was an error here.', 11, 1)
RETURN
END
ELSE
COMMIT TRANSACTION

Re: Stored procedure transactions on delete??

Mike D wrote:
> I have a stored procedure that receives an id from an asp page and
> deletes the records from three different tables. Do I need to handle
> errors in this with transactions?

Perhaps. Only you can answer this.

> If I tried to delete from a table
> where there was no record it doesn't throw an error does it?

No

> I have
> removed a record from one table manually and I can't get the error
> raised.
>

That should answer your previous question.

> Should I do anything? I don't think I need to but ..maybe I am
> missing something here.

I don't know. What do you want it to do? Do you want to roll back if the id
isn't found in one of the tables? Or only if an error occurred while
attempting to delete from one of the tables?

You should check @@ERROR after each DELETE statement, not just the last.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Stored procedure transactions on delete??

"Bob Barrows [MVP]" wrote:

> Mike D wrote:
> > I have a stored procedure that receives an id from an asp page and
> > deletes the records from three different tables. Do I need to handle
> > errors in this with transactions?
>
> Perhaps. Only you can answer this.
>
> > If I tried to delete from a table
> > where there was no record it doesn't throw an error does it?
>
> No
>
> > I have
> > removed a record from one table manually and I can't get the error
> > raised.
> >
>
> That should answer your previous question.
>
> > Should I do anything? I don't think I need to but ..maybe I am
> > missing something here.
>
> I don't know. What do you want it to do? Do you want to roll back if the id
> isn't found in one of the tables? Or only if an error occurred while
> attempting to delete from one of the tables?
>
> You should check @@ERROR after each DELETE statement, not just the last.
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"

Thanks for the reply. I guess it would just be overkill. The users only
get a chance to delete from all three tables if there is something in all
three tables. I guess I am just getting caught up in it!

Mike

Re: Stored procedure transactions on delete??

Mike D wrote:
>
> Thanks for the reply. I guess it would just be overkill. The users
> only get a chance to delete from all three tables if there is
> something in all three tables. I guess I am just getting caught up
> in it!
>
> Mike
OK, but I just want to reiterate:
You should check @@ERROR after each DELETE statement, not just the last. The
transaction will not be rolled back if an error occurs in the first or
second DELETE statements. The way you have it set up, the transaction will
only be rolled back if an error occurs in the third statement.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Stored procedure transactions on delete??

Probably some useful reading for the OP... maybe a bit too much material but
you can definitely pick and choose...

http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

--
http://www.aspfaq.com/
(Reverse address to reply.)




"Bob Barrows [MVP]" wrote in message
news:uCdstlAyEHA.3844@TK2MSFTNGP12.phx.gbl...
> Mike D wrote:
> >
> > Thanks for the reply. I guess it would just be overkill. The users
> > only get a chance to delete from all three tables if there is
> > something in all three tables. I guess I am just getting caught up
> > in it!
> >
> > Mike
> OK, but I just want to reiterate:
> You should check @@ERROR after each DELETE statement, not just the last.
The
> transaction will not be rolled back if an error occurs in the first or
> second DELETE statements. The way you have it set up, the transaction will
> only be rolled back if an error occurs in the third statement.
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>