USE Master
GO
IF DB_ID ('TransakciaDB') IS NOT NULL DROP DATABASE TransakciaDB
GO 

CREATE DATABASE TransakciaDB ON PRIMARY(	
	NAME = TransakciaData,
	FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TransakciaDB.mdf',
	--SIZE = 3MB,
	MAXSIZE = 10MB,
	FILEGROWTH = 20%
)
LOG ON(
	NAME = TransakciaLog,
	FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TransakciaDB.ldf',
	SIZE = 1MB,
	MAXSIZE = 5MB,
	FILEGROWTH = 1MB
)
GO

USE TransakciaDB
GO
create table Osoba(
	idOsoba int primary key not null,
	meno nvarchar(10) not null,
	firma nvarchar(15)
)
create table OsobaUdaje(
	idOsoba int foreign key references dbo.Osoba(idOsoba),
	adresa nvarchar(30)
)
Insert into Osoba values
(1,'Bobo Bibi', 'IBM ABC'), 
(2,'Tata Tutu', 'Micro XYZ')

Insert into OsobaUdaje values
(1, 'Roznava'),
(2, 'Kosice')
GO

IF OBJECT_ID ( 'sp_Transact_Test_InsertDelete', 'P' ) IS NOT NULL 
    DROP PROCEDURE sp_InsertDelete;
GO

---------------------------------------------
create procedure sp_Transact_Test_InsertDelete
      @idOs int,
      @meno nvarchar(10),
      @firma nvarchar(15),
      @idOsOld int
as
      declare @erIns int
      declare @erDel int
      declare @er int

      --set @er = 0

      begin transaction

      -- Pridaj osobu
      insert into Osoba (idOsoba, meno, firma) values(@idOs, @meno, @firma)

      -- Error po Insert
      set @erIns = @@error
      --if @erIns > @er set  @er  = @erIns
      if @erIns > 0 set  @er  = @erIns

      -- Maz osobu
      delete from Osoba where idOsoba = @idOsOld

      -- Error po Delete
      set @erDel = @@error
      if @erDel > 0 set  @er  = @erDel

-- If error, roll back
if @er <> 0
      begin
        rollback
        print  'Transakcia odvolana'
      end
else
      begin
        commit
        print  'Transakcia vykonana'
      end
print 'INSERT error number:' + cast(@erIns as nvarchar(8))
print 'DELETE error number:' + cast(@erDel as nvarchar(8))

return @er
GO
--------------------------------------------

---- OK:
-- Insert 2x osobu with new id + delete osobu with existing, but not PK id. 
-- DELETE returns error number 0 even though it has not deleted any rows
exec sp_Transact_Test_InsertDelete 3, 'Fero', null, 33
exec sp_Transact_Test_InsertDelete 4, 'Jano', null, 33

---- 2x single and 1x double ERROR:
-- Insert osobu with existing id + delete osobu with existing id but not existing FK. 
exec sp_Transact_Test_InsertDelete 4, 'Stevo', null, 3 -- Err Primary Key

-- Insert osobu with new id + delete osobu with existing id and existing FK. 
exec sp_Transact_Test_InsertDelete 5, 'Stevo', null, 2  -- Err Delete Reference error

-- Insert osobu with existing id + delete osobu with existing id and existing FK. 
exec sp_Transact_Test_InsertDelete 4, 'Stevo', null, 2 -- Err Primary Key + Err Delete Reference error