CREATE TABLE Osoba
(
    IDOsoba int PRIMARY KEY IDENTITY,
    Ime nvarchar(50),
    Prezime nvarchar(50)
)
GO

-- 1.
BEGIN TRAN

INSERT INTO Osoba VALUES ('Miro', 'Mirić')
INSERT INTO Osoba VALUES ('Ana', 'Anić')
INSERT INTO Osoba VALUES ('Iva', 'Ivić')

SELECT * FROM Osoba

ROLLBACK TRAN

SELECT * FROM Osoba
GO

-- 2.
BEGIN TRAN

INSERT INTO Osoba VALUES ('Miro', 'Mirić')
INSERT INTO Osoba VALUES ('Ana', 'Anić')
INSERT INTO Osoba VALUES ('Iva', 'Ivić')
SELECT * FROM Osoba

COMMIT TRAN

SELECT * FROM Osoba
GO

-- 3.
DELETE FROM Osoba

BEGIN TRAN

INSERT INTO Osoba VALUES ('Miro', 'Mirić')
SAVE TRAN cp1

INSERT INTO Osoba VALUES ('Ana', 'Anić')

SELECT * FROM Osoba

ROLLBACK TRAN

SELECT * FROM Osoba
GO

-- 4.
DELETE FROM Osoba

BEGIN TRAN

INSERT INTO Osoba VALUES ('Miro', 'Mirić')
SAVE TRAN cp1

INSERT INTO Osoba VALUES ('Ana', 'Anić')

SELECT * FROM Osoba

COMMIT TRAN

SELECT * FROM Osoba
GO

-- 5.
DELETE FROM Osoba

BEGIN TRAN

INSERT INTO Osoba VALUES ('Miro', 'Mirić')
SAVE TRAN cp1

INSERT INTO Osoba VALUES ('Ana', 'Anić')
SAVE TRAN cp2

SELECT * FROM Osoba

ROLLBACK TRAN

SELECT * FROM Osoba
GO

-- 6.
DELETE FROM Osoba

BEGIN TRAN

INSERT INTO Osoba VALUES ('Miro', 'Mirić')
SAVE TRAN cp1

INSERT INTO Osoba VALUES ('Ana', 'Anić')
SAVE TRAN cp2

SELECT * FROM Osoba

COMMIT TRAN

SELECT * FROM Osoba
GO

-- 7.
DELETE FROM Osoba

BEGIN TRAN

INSERT INTO Osoba VALUES ('Miro', 'Mirić')
SAVE TRAN cp1

INSERT INTO Osoba VALUES ('Ana', 'Anić')

SELECT * FROM Osoba

ROLLBACK TRAN cp1

SELECT * FROM Osoba

ROLLBACK TRAN

SELECT * FROM Osoba
GO

-- 8.
DELETE FROM Osoba

BEGIN TRAN

INSERT INTO Osoba VALUES ('Miro', 'Mirić')
SAVE TRAN cp1

INSERT INTO Osoba VALUES ('Ana', 'Anić')

SELECT * FROM Osoba

ROLLBACK TRAN cp1

SELECT * FROM Osoba

COMMIT TRAN

SELECT * FROM Osoba
GO

-- 9.
CREATE PROC p9
	@IDProizvod int
AS
DELETE FROM Proizvod WHERE IDProizvod = @IDProizvod
GO

BEGIN TRY
	BEGIN TRAN

	EXEC p9 1001
	EXEC p9 1002
	EXEC p9 1003

	COMMIT TRAN
	PRINT 'Brisanje uspješno'
END TRY
BEGIN CATCH
	ROLLBACK TRAN
	PRINT 'Greška pri brisanju'
END CATCH
GO

BEGIN TRY
	BEGIN TRAN

	EXEC p9 1001
	EXEC p9 1002
	EXEC p9 777

	COMMIT TRAN
	PRINT 'Brisanje uspješno'
END TRY
BEGIN CATCH
	ROLLBACK TRAN
	PRINT 'Greška pri brisanju'
END CATCH
GO

-- 10.
ALTER TABLE Drzava ADD CONSTRAINT UQ_Drzava_Naziv UNIQUE (Naziv)
GO

CREATE PROC p10
	@Podaci xml
AS
BEGIN TRY
	BEGIN TRAN
		
		-- Umećem državu.
		INSERT INTO Drzava (Naziv)
		SELECT
			T.S.value('@Naziv', 'nvarchar(50)')
		FROM @Podaci.nodes('/ZaUmetnuti/Drzava') as T(S)
		PRINT 'Upisao državu'
		
		-- Uzimam ID.
		DECLARE @IDDrzava int
		SET @IDDrzava = SCOPE_IDENTITY()
		
		-- Umećem gradove.
		INSERT INTO Grad (DrzavaID, Naziv)
		SELECT
			@IDDrzava,
			T.S.value('.', 'nvarchar(50)')
		FROM @Podaci.nodes('/ZaUmetnuti/Drzava/Gradovi/Grad') as T(S)
		PRINT 'Upisao gradove'
		
	COMMIT TRAN
	PRINT 'Umetanje uspješno!'
END TRY
BEGIN CATCH
	ROLLBACK TRAN
	PRINT 'Desila se greška: ' + ERROR_MESSAGE()
END CATCH
GO

DECLARE @Podaci xml = '
	<ZaUmetnuti>
		<Drzava Naziv="Bjelorusija">
			<Gradovi>
				<Grad>Minsk</Grad>
				<Grad>Borisov</Grad>
				<Grad>Soligorsk</Grad>
			</Gradovi>
		</Drzava>
	</ZaUmetnuti>'
	
EXEC p10 @Podaci
GO

SELECT * FROM Drzava
SELECT * FROM Grad
GO


-- 11.
BEGIN TRY
	BEGIN TRAN
		DECLARE @Podaci xml = '
			<ZaUmetnuti>
				<Drzava Naziv="Kenija">
					<Gradovi>
						<Grad>Baragoi</Grad>
						<Grad>Bungoma</Grad>
						<Grad>Kericho</Grad>
					</Gradovi>
				</Drzava>
			</ZaUmetnuti>'
			
		EXEC p10 @Podaci
		
	ROLLBACK TRAN
	PRINT 'Sve OK, ali odustajem od transakcije'
END TRY
BEGIN CATCH
	ROLLBACK TRAN
	PRINT 'Desila se greška pa odustajem od transakcije'
END CATCH 
GO

SELECT * FROM Drzava
SELECT * FROM Grad
GO

-- 12.
SET IMPLICIT_TRANSACTIONS ON

INSERT INTO Osoba VALUES ('Miro', 'Mirić')

SET IMPLICIT_TRANSACTIONS OFF
GO
