use lab4;

create table team (
	id uniqueidentifier default newid() primary key,
	city nvarchar(1000) not null,
	team_name nvarchar(max) not null
);

create table match (
    id uniqueidentifier default newid() primary key,
    name nvarchar(1000) not null,
	city nvarchar(1000) not null,
	team uniqueidentifier not null,
	constraint fk_match_team foreign key (team) references team(id)
		on update no action
		on delete no action
);

create table player (
	id uniqueidentifier default newid() primary key,
	name nvarchar(max) not null,
	team uniqueidentifier not null,
	constraint fk_player_team foreign key (team) references team(id)
		on update no action
		on delete no action
);

go

set implicit_transactions off;
set implicit_transactions on;
select @@TRANCOUNT;

insert into team(city, team_name) values ('Moscow', 'Team1');
insert into team(city, team_name) values ('Paris', 'Team2');
insert into team(city, team_name) values ('New York', 'Team3');
delete from team;
select * from team;
BEGIN TRAN;
COMMIT;
ROLLBACK;

go

SELECT @@TRANCOUNT;
BEGIN TRAN;
	SELECT @@TRANCOUNT;
	INSERT INTO team(city, team_name) VALUES('Moscow', 'Team4');
	SELECT @@TRANCOUNT;
	BEGIN TRAN;
	SELECT @@TRANCOUNT; -- = 2
		-- Просмотрите добавленную строку
		SELECT * FROM team;
	COMMIT
	SELECT @@TRANCOUNT; -- = 1
	-- ВЫВЕДИТЕ СПИСОК АКТИВНЫХ ТРАНЗАКЦИЙ
COMMIT TRAN;
-- Удалите добавленную строку
DELETE FROM team;
select * from team;

SELECT * FROM sys.dm_tran_active_transactions

go

SELECT @@TRANCOUNT;
BEGIN TRAN;
	SELECT @@TRANCOUNT;
	INSERT INTO team(city, team_name) VALUES('Moscow', 'Team5');
	BEGIN TRAN;
		SELECT @@TRANCOUNT;
		INSERT INTO team(city, team_name) VALUES('Paris', 'Team6');
ROLLBACK;
SELECT @@TRANCOUNT;
-- Просмотрите добавленные строки
SELECT * FROM team;
delete from team;