lab15

use master;
go
if DB_ID (N'lab15_1') is not null
drop database lab15_1;
go
create database lab15_1
on (
NAME = lab151dat,
FILENAME = 'C:\DB\lab15\lab151dat.mdf',
SIZE = 10,
MAXSIZE = 25,
FILEGROWTH = 5
)
log on (
NAME = lab151log,
FILENAME = 'C:\DB\lab15\lab151log.ldf',
SIZE = 5,
MAXSIZE = 20,
FILEGROWTH = 5
);
go

use master;
go
if DB_ID (N'lab15_2') is not null
drop database lab15_2;
go
create database lab15_2
on (
NAME = lab152dat,
FILENAME = 'C:\DB\lab15\lab152dat.mdf',
SIZE = 10,
MAXSIZE = 25,
FILEGROWTH = 5
)
log on (
NAME = lab152log,
FILENAME = 'C:\DB\lab15\lab152log.ldf',
SIZE = 5,
MAXSIZE = 20,
FILEGROWTH = 5
);
go

use lab15_1;
go

if OBJECT_ID(N'Squad',N'U') is NOT NULL
	DROP TABLE Squad;
go

CREATE TABLE Squad (
	Squad_Id int PRIMARY KEY NOT NULL,
	Surname char(30) NOT NULL,
	Name char(30) NOT NULL,
	Number int NOT NULL,
	Clubs_Id int NOT NULL
);
go

use lab15_2;
go

if OBJECT_ID(N'Clubs',N'U') is NOT NULL
	DROP TABLE Clubs;
go

CREATE TABLE Clubs(
	Clubs_Id int PRIMARY KEY NOT NULL,
	Title nchar(30) NOT NULL,
	Year_of_foundation numeric(4) NOT NULL
);
go

if OBJECT_ID(N'SquadClubsView',N'V') is NOT NULL
	DROP VIEW SquadClubsView;
go

CREATE VIEW SquadClubsView AS
	SELECT A.Squad_Id as Squad_Id, A.Surname as Surname, A.Name as Name, A.number as Number, B.*
	FROM lab15_1.dbo.Squad A, lab15_2.dbo.Clubs B
	WHERE A.Clubs_Id = B.Clubs_Id
go

IF OBJECT_ID(N'InsertClubs',N'TR') IS NOT NULL
	DROP TRIGGER InsertClubs
go

CREATE TRIGGER InsertClubs
	ON Clubs
	INSTEAD OF INSERT
AS
	BEGIN
	IF EXISTS(SELECT A.Clubs_Id FROM lab15_2.dbo.Clubs AS A,
									 inserted as I
									 WHERE A.Clubs_Id = I.Clubs_Id)
			  BEGIN
					EXEC sp_addmessage 50002, 15,N'ID занят!',@lang='us_english',@replace='REPLACE';
					RAISERROR(50002,15,-1)
			  END
	ELSE
		INSERT INTO lab15_2.dbo.Clubs(Clubs_Id, Title, Year_of_foundation)
		SELECT Clubs_Id, Title, Year_of_foundation FROM inserted
	IF (SELECT COUNT(*) FROM inserted) > 1
			PRINT 'Добавлены новые клубы'
		ELSE
			PRINT 'Добавлен новый клуб'
	END
go


IF OBJECT_ID(N'DeleteClubs',N'TR') IS NOT NULL
	DROP TRIGGER DeleteClubs
go

CREATE TRIGGER DeleteClubs
	ON Clubs
	INSTEAD OF DELETE
AS
	BEGIN
		DELETE B FROM lab15_1.dbo.Squad AS B INNER JOIN deleted AS d ON B.Clubs_Id = d.Clubs_Id
		DELETE A FROM lab15_2.dbo.Clubs AS A INNER JOIN deleted AS d ON A.Clubs_Id = d.Clubs_Id
	END
go

IF OBJECT_ID(N'UpdateClubs',N'TR') IS NOT NULL
	DROP TRIGGER UpdateClubs
go

CREATE TRIGGER UpdateClubs
	ON Clubs
	AFTER UPDATE
AS
	BEGIN
		BEGIN;
		DECLARE @temp_table TABLE(
			Clubs_Id int PRIMARY KEY,
			add_Title char(30), add_Year_of_foundation numeric(4),
			delete_Title char(30), delete_Year_of_foundation numeric(4)
		);

		INSERT INTO @temp_table(Clubs_Id, add_Title, add_Year_of_foundation, delete_Title, delete_Year_of_foundation)
		SELECT A.Clubs_Id, A.Title, A.Year_of_foundation,
				B.Title, B.Year_of_foundation
		FROM inserted A
		INNER JOIN deleted B ON A.Clubs_Id = B.Clubs_Id
	END;
	END
go

use lab15_1;
go

IF OBJECT_ID(N'InsertSquad',N'TR') IS NOT NULL
	DROP TRIGGER InsertSquad
go

CREATE TRIGGER InsertSquad
	ON Squad
	INSTEAD OF INSERT
AS
	BEGIN
		IF EXISTS (SELECT B.Squad_Id FROM lab15_1.dbo.Squad AS B,inserted AS I
							 WHERE B.Squad_Id = I.Squad_Id)
			BEGIN
				EXEC sp_addmessage 50002, 15,N'ID занят! Попробуйте другой',@lang='us_english',@replace='REPLACE';
					RAISERROR(50002,15,-1)
				END
		ELSE
			INSERT INTO Squad(Squad_Id,Surname,Name, Number,Clubs_Id)
			SELECT Squad_Id, Surname, Name, Number, Clubs_Id FROM inserted
	END
go

IF OBJECT_ID(N'DeleteSquad',N'TR') IS NOT NULL
	DROP TRIGGER DeleteSquad
go

CREATE TRIGGER DeleteSquad
	ON Squad
	INSTEAD OF DELETE
AS
	BEGIN
		DELETE B FROM lab15_1.dbo.Squad AS B INNER JOIN deleted AS d ON b.Squad_Id = d.Squad_Id
	END
go


IF OBJECT_ID(N'UpdateSquad',N'TR') IS NOT NULL
	DROP TRIGGER UpdateSquad
go

CREATE TRIGGER UpdateSquad
	ON Squad
	AFTER UPDATE
AS
	BEGIN
		 DECLARE @temp_table TABLE(
			Clubs_Id int PRIMARY KEY,
			add_Number int,
			delete_Number int
		 );
		 INSERT INTO @temp_table(Clubs_Id, add_Number, delete_Number)
		 SELECT A.Clubs_Id, A.Number, A.Number
		 FROM inserted A
		 INNER JOIN deleted B ON A.Clubs_Id = B.Clubs_Id
	END
go

INSERT INTO lab15_2.dbo.Clubs(Clubs_Id, Title, Year_of_foundation)
VALUES (1, N'1 Команда', 1337),
	   (2, N'2 Команда', 1597),
	   (3, N'3 Команда', 1889),
	   (4, N'4 Команда', 1943)
go

UPDATE lab15_2.dbo.Clubs SET Year_of_foundation = 9999 WHERE Clubs_Id = 2
go

INSERT INTO lab15_1.dbo.Squad(Squad_Id,Surname,Name,Number,Clubs_Id)
 VALUES
	   (1,N'Аррисабалага',N'Кепа',23,1),
	   (2,N'Алонсо',N'Маркос',32,2),
	   (3,N'Рюдигер',N'Антонио',5,3),
	   (4,N'Томори',N'Фикайо',6,4)
go

UPDATE lab15_1.dbo.Squad SET Number = 9999 WHERE Squad_Id = 1
go

SELECT * FROM lab15_2.dbo.Clubs;
SELECT * FROM lab15_1.dbo.Squad;