Database

create table ic_DocumentFolderAccess
(
  DocumentFolderAccessKey int            NOT NULL IDENTITY,
  DocumentFolderKey       int            NOT NULL,
  CompanyKey              int            NULL,
  CompanyUserKey          int            NULL,
  RoleKey                 int            NULL
)
GO
create unique clustered index ic_DocumentFolderAccessFolder_idx
  on ic_DocumentFolderAccess (DocumentFolderKey, DocumentFolderAccessKey)
create unique index ic_DocumentFolderAccess_idx
  on ic_DocumentFolderAccess (DocumentFolderAccessKey)
GO



create table ic_DocumentFolderFile
(
  DocumentFolderKey       int            NOT NULL,
  AttachmentKey           int            NOT NULL,
  CompanyUserKey         int            NOT NULL
)
GO
create unique clustered index ic_DocumentFolderFile_idx
  on ic_DocumentFolderFile (AttachmentKey, DocumentFolderKey, CompanyUserKey)
create unique index ic_DocumentFolderFileFolderAttach_idx
  on ic_DocumentFolderFile (DocumentFolderKey, AttachmentKey, CompanyUserKey)
create unique index ic_DocumentFolderFileUserFolderFile_idx
  on ic_DocumentFolderFile (CompanyUserKey, AttachmentKey, DocumentFolderKey)
GO

BEGIN TRAN T1

Insert into table2
if @@error != 0
  BEGIN
    ROLLBACK TRAN T1
    raiserror 50000 'Insert into table2 failed'
    return
  END

exec sp_2
if @@error != 0
  BEGIN
    ROLLBACK TRAN T1
    raiserror 50000 'exec of sp2 failed'
    return
  END

exec sp_3
if @@error != 0
  BEGIN
    ROLLBACK TRAN T1
    raiserror 50000 'exec of sp3 failed'
    return
  END

Insert into table3
if @@error != 0
  BEGIN
    ROLLBACK TRAN T1
    raiserror 50000 'Insert into table3 failed'
    return
  END

Delete from table1 (data collected and now inserted into the new tables)

if @@error != 0
  BEGIN
    ROLLBACK TRAN T1
    raiserror 50000 'Delete from table1 failed'
    return
  END

COMMIT TRANSACTION