On Error, Roll Back

[webmigit]

Is there a way, in a stored procedure that if an insert/update fails that all previous transactions in that stored procedure are rolled back? (Removed?).

And if so, does this apply be default to sps called from inside the sp? Or is there a way to make it apply?

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[LloydDobler]

Here's an example of a procedure we have that rollsback a transaction if there was an invalid part number entered. It's based off this trigger, with the sp following. hope it helps.
CREATE TRIGGER VALIDATION ON dbo.tbl_production
FOR INSERT
AS
declare @mach char(10),
@part char(30),
@div char(10)
select @machine = machinenumber from inserted
select @part = partnumber from inserted
select @div= division from inserted
begin

if
@mach not in (select machinenumber from tbl_machinenumbers)
EXECUTE sp_validatemachine
else
if @div = 2 and
@part not in (select partnumber from tbl_partnumbers)
EXECUTE sp_validatepart

end

and the stored procedure is....


CREATE PROCEDURE [dbo].[sp_validatepart] AS
raiserror ('ERROR-INVALID PART NUMBER', 16, 127) with nowait
print 'PART NUMBER INVALID'
rollback tran
GO


"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."