SQL 交易機制範例

範例一：用於批次
DECLARE @ReturnCode INT
SET                  @ReturnCode = 0
BEGIN TRANSACTION
BEGIN
                          EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule
                           @job_id = @JobID, @name = N'Schedule1', @enabled = 1, @freq_type = 1,
                          @active_start_date = 20110312,
END
IF (@@ERROR <> 0 OR @ReturnCode <> 0)  GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

範例二：
BEGIN TRAN

Delete  from AddressNumber where PostCode not in (Select ZIP_ID  From vZip_Master)  and PostCode is not null
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRAN
  RETURN
 END

Delete  from Alley where PostCode not in (Select ZIP_ID  From vZip_Master)  and PostCode is not null
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRAN
  RETURN
 END

Delete  from Road where PostCode not in (Select ZIP_ID  From vZip_Master)  and PostCode is not null
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRAN
  RETURN
 END
Delete  from Section where PostCode not in (Select ZIP_ID  From vZip_Master)  and PostCode is not null
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRAN
  RETURN
 END
Delete  from SpecialZip where zip not in (Select ZIP_ID  From vZip_Master)  and zip is not null
 IF @@ERROR <> 0
 BEGIN
  ROLLBACK TRAN
  RETURN
 END

COMMIT TRAN