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\lab9.ldf',
		SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO

USE lab9;
GO

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

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

DECLARE @id  varchar(30);
INSERT country (namec) VALUES ('Rome');
SET @id = SCOPE_IDENTITY();
INSERT ruler (ruler_country, name) VALUES (@id, 'Caesar');
INSERT ruler (ruler_country, name) VALUES (@id, 'Octavian');
INSERT country (namec) VALUES ('Greece');
SET @id = SCOPE_IDENTITY();
INSERT ruler (ruler_country, name) VALUES (@id, '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 (namec) VALUES ('China');
GO

CREATE TRIGGER update_country ON country AFTER UPDATE
	AS PRINT 'update'; 
	SELECT * FROM country;
GO

UPDATE country SET namec = 'India' WHERE  namec = 'China';
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 DISTINCT namec FROM inserted WHERE namec NOT IN (SELECT namec FROM country);
			INSERT ruler(ruler_country, name) SELECT DISTINCT a.countryid, b.name FROM dbo.country a INNER JOIN inserted b ON a.namec = b.namec WHERE b.name NOT IN (SELECT name FROM ruler);
		END;
GO


INSERT C_lView(namec, name) VALUES ('Egypt', 'Ramses'), ('Egypt', 'Ramses II');
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 	
		BEGIN		
		CREATE TABLE #table1 (id1 int IDENTITY PRIMARY KEY, new_name nvarchar(30));
		CREATE TABLE #table2 (id2 int IDENTITY PRIMARY KEY, old_name nvarchar(30));	
		INSERT #table1 (new_name) SELECT name FROM inserted;
		INSERT #table2 (old_name) SELECT name FROM deleted; 
		INSERT country(namec) SELECT DISTINCT namec FROM inserted WHERE namec NOT IN (SELECT namec FROM country);
		UPDATE ruler SET ruler_country  = countryid FROM country a INNER JOIN inserted b ON a.namec =b.namec WHERE ruler.name = b.name;
		UPDATE ruler SET name = new_name FROM #table1 INNER JOIN #table2 ON #table1.id1 = #table2.id2 WHERE ruler.name = #table2.old_name; 
		DELETE FROM country WHERE countryid NOT IN (SELECT ruler_country FROM ruler); 
		DROP TABLE #table1;
		DROP TABLE #table2; 
		END;

GO

INSERT C_lView(namec, name) VALUES ('Greece', 'Caligula');
SELECT * FROM country;
GO
SELECT * FROM ruler;
GO
SELECT * FROM C_lView;
GO
 
UPDATE C_lView SET namec = 'Rome II' WHERE namec = 'Rome';
UPDATE C_lView SET name = 'Caesar II' WHERE name = 'Santa';
UPDATE C_lView SET namec = 'Honduras' WHERE name like 'Ca%'

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