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 TATBLE 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

SELECT * FROM Osoba;
SELECT * FROM OsobaUdaje;

---------------------------------------------
declare @erIns int
declare @erDel int
declare @er int

begin transaction

-- Pridaj osobu
insert into Osoba (idOsoba, meno, firma) values(3, 'Fero', NULL)
-- insert into Osoba (idOsoba, meno, firma) values(4, 'Jano', NULL)
-- insert into Osoba (idOsoba, meno, firma) values(5, 'Stevo', NULL)

-- 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 = 33
-- delete from Osoba where idOsoba = 2

-- 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
GO