USE MASTER GO IF db_id lab9 IS NOT NULL DROP DATABASE lab9 go CREATE D

USE MASTER;
GO

IF db_id (N'lab9') IS NOT NULL
DROP DATABASE lab9;
go

CREATE DATABASE lab9 
	ON (NAME = lab9_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab9.mdf',
		SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
	LOG ON (NAME = lab9_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab.ldf',
		SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO

USE lab9;
GO

CREATE TABLE country (
    countryid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
	namec char(30));
GO

CREATE TABLE ruler (
	rulerid int IDENTITY(1, 1) PRIMARY KEY NOT NULL, 
	name char(30),
	ruler_country int FOREIGN KEY REFERENCES country(countryid) 
	ON DELETE CASCADE
	ON UPDATE CASCADE
	);
GO

INSERT country (namec) VALUES ('Rome');
INSERT country (namec) VALUES ('Greece');
INSERT ruler (ruler_country, name) VALUES (1, 'Caesar');
INSERT ruler (ruler_country, name) VALUES (1, 'Octavian');
INSERT ruler (ruler_country, name) VALUES (2, 'Pericles');
GO

CREATE VIEW C_lView WITH SCHEMABINDING AS
	SELECT a.namec, b.name FROM
	dbo.country a INNER JOIN dbo.ruler b ON a.countryid = b.ruler_country
GO
-- вставка
CREATE TRIGGER insert_country ON country FOR INSERT 
	AS PRINT 'insert';
	SELECT * FROM country;
GO
-- пример
/*INSERT country (countryid, nameс) VALUES (5, 'China');
GO*/
-- изменение
CREATE TRIGGER update_country ON country AFTER UPDATE
	AS PRINT 'update'; 
	SELECT * FROM country;
GO
-- пример
/*UPDATE country SET countryid = 10 WHERE countryid = 5;
GO*/
-- удаление
CREATE TRIGGER delete_country ON country INSTEAD OF DELETE
	AS THROW 51000, 'Cant delete', 1;
GO
-- пример 
/*DELETE FROM country WHERE countryid = 1;
GO*/

DROP TRIGGER insert_country;
DROP TRIGGER update_country;
DROP TRIGGER delete_country;
GO
-- вставка через представление  
CREATE TRIGGER insert_C_lView ON C_lView INSTEAD OF INSERT 
	AS 
		BEGIN
			INSERT country(namec) SELECT namec FROM inserted WHERE namec NOT IN (SELECT namec FROM country);
			CREATE TABLE table1 (ins_namec char(30), ins_name char(30),);
			INSERT table1 (ins_namec, ins_name) SELECT namec, name FROM inserted;
			INSERT ruler(ruler_country, name) SELECT DISTINCT a.countryid, b.ins_name FROM dbo.country a INNER JOIN dbo.table1 b ON a.namec = b.ins_namec WHERE b.ins_name NOT IN (SELECT name FROM ruler);
			DROP TABLE table1;
		END;
GO

-- пример
INSERT C_lView(namec, name) VALUES ('Egypt', 'Ramses');
INSERT C_lView(namec, name) VALUES ('Egypt', 'Ramses II');
INSERT C_lView(namec, name) VALUES ('Egypt', 'Ramses III'), ('Laplandia', 'Santa'), ('Egypt', 'Ramses V'), ('Egypt', 'Ramses V');
GO
SELECT * FROM country;
GO
SELECT * FROM ruler;
GO
SELECT * FROM C_lView;
GO
-- удаление через представление 
CREATE TRIGGER delete_C_lView ON C_lView INSTEAD OF DELETE
	AS 
	DELETE FROM ruler WHERE name IN (SELECT name FROM deleted);
	DELETE FROM country WHERE countryid NOT IN (SELECT ruler_country FROM ruler);
GO
-- пример
DELETE FROM C_lView WHERE name = 'Caesar';
DELETE FROM C_lView WHERE namec = 'Egypt';
GO
SELECT * FROM country;
GO
SELECT * FROM ruler;
GO
SELECT * FROM C_lView;
GO
-- изменение через представление 
CREATE TRIGGER update_C_lView ON C_lView INSTEAD OF UPDATE
	AS 	IF (UPDATE(namec))
		BEGIN
			IF EXISTS (SELECT * FROM country JOIN inserted ON inserted.namec = country.namec)
			BEGIN
				THROW 51000, 'Cant update', 1;
			END
			ELSE
			BEGIN
				CREATE TABLE table1 (id1 int IDENTITY PRIMARY KEY, new_namec char(30)); 
				CREATE TABLE table2 (id2 int IDENTITY PRIMARY KEY, old_namec char(30));				
				INSERT table1 (new_namec) SELECT namec FROM inserted;
				INSERT table2 (old_namec) SELECT namec FROM deleted;
				UPDATE country SET namec = new_namec 
				FROM table1 INNER JOIN table2 ON table1.id1 = table2.id2
				WHERE country.namec = table2.old_namec;
				DROP TABLE table1;
				DROP TABLE table2;
			END;
		END;
		IF (UPDATE(name))
		BEGIN
			IF EXISTS (SELECT * FROM ruler JOIN inserted ON inserted.name = ruler.name)
			BEGIN
				THROW 51000, 'Cant update', 1;
			END
			ELSE
			BEGIN
				CREATE TABLE table1 (id1 int IDENTITY PRIMARY KEY, new_name char(30)); 
				CREATE TABLE table2 (id2 int IDENTITY PRIMARY KEY, old_name char(30));				
				INSERT table1 (new_name) SELECT name FROM inserted;
				INSERT table2 (old_name) SELECT name FROM deleted;
				UPDATE ruler SET name = new_name 
				FROM table1 INNER JOIN table2 ON table1.id1 = table2.id2
				WHERE ruler.name = table2.old_name;
				DROP TABLE table1;
				DROP TABLE table2;
			END;
		END;	
GO
-- пример
UPDATE C_lView SET namec = 'Rome II' WHERE namec = 'Rome';
UPDATE C_lView SET name = 'Caesar II' WHERE name = 'Santa';
GO
SELECT * FROM country;
GO
SELECT * FROM ruler;
GO
SELECT * FROM C_lView;
GO

DROP TRIGGER insert_C_lView;
DROP TRIGGER delete_C_lView;
DROP TRIGGER update_C_lView;
GO