14

USE master;
GO

IF db_id (N'lab141') IS NOT NULL
	DROP DATABASE lab141;
GO

CREATE DATABASE lab141 
	ON (NAME = dat_lab141, FILENAME = 'C:\data\datlab141.mdf',
	SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
	LOG ON (NAME = log_lab141, FILENAME = 'C:\data\loglab141.ldf',
	SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO

IF db_id (N'lab142') IS NOT NULL
	DROP DATABASE lab142;
GO

CREATE DATABASE lab142 
	ON (NAME = dat_lab142, FILENAME = 'C:\data\datlab142.mdf',
	SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
	LOG ON (NAME = log_lab142, FILENAME = 'C:\data\loglab142.ldf',
	SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO

USE lab141;
GO

CREATE TABLE Person (idofperson int PRIMARY KEY,
					firstname char(50) NOT NULL);
GO

USE lab142;
GO
CREATE TABLE Person (idofperson int PRIMARY KEY,
					secondname char(50) NOT NULL);
GO

USE master;
GO

CREATE VIEW view_141_142 
	AS
	SELECT a.idofperson, a.firstname, b.secondname FROM lab141.dbo.Person a,
	lab142.dbo.Person b WHERE a.idofperson = b.idofperson;
GO

CREATE TRIGGER insert_in_view ON view_141_142 INSTEAD OF INSERT 
	AS
	INSERT lab141.dbo.Person (idofperson, firstname) 
		SELECT idofperson, firstname FROM inserted;
	INSERT lab142.dbo.Person (idofperson, secondname) 
		SELECT idofperson, secondname FROM inserted;
GO

INSERT view_141_142 (idofperson, firstname, secondname) VALUES (1, 'Regina', 'Frank'),
							(2, 'Lola', 'Moon'),
							(3, 'James', 'Cost'),
							(200, 'James', 'Cost');
GO

CREATE TRIGGER delete_in_view ON view_141_142 INSTEAD OF DELETE
	AS
	DELETE lab141.dbo.Person WHERE idofperson IN (SELECT idofperson FROM deleted);
	DELETE lab142.dbo.Person WHERE idofperson IN (SELECT idofperson FROM deleted);
GO

DELETE view_141_142 WHERE idofperson = 3;
GO

CREATE TRIGGER update_on_view ON view_141_142 INSTEAD OF UPDATE 
	AS 
	BEGIN
		IF (UPDATE(idofperson)) 
		BEGIN
			IF NOT EXISTS (SELECT * FROM lab141.dbo.Person WHERE lab141.dbo.Person.idofperson IN (SELECT idofperson FROM inserted))
			BEGIN
				UPDATE lab141.dbo.Person SET idofperson = (SELECT idofperson FROM inserted) WHERE idofperson IN (SELECT idofperson FROM deleted);
				UPDATE lab142.dbo.Person SET idofperson = (SELECT idofperson FROM inserted) WHERE idofperson IN (SELECT idofperson FROM deleted);
			END;
		END;
		IF (UPDATE(firstname))
		BEGIN
			UPDATE lab141.dbo.Person SET lab141.dbo.Person.firstname = inserted.firstname
			FROM inserted WHERE inserted.idofperson = lab141.dbo.Person.idofperson;
		END;
		IF (UPDATE(secondname))
		BEGIN
			UPDATE lab142.dbo.Person SET lab142.dbo.Person.secondname = inserted.secondname
			FROM inserted WHERE inserted.idofperson = lab142.dbo.Person.idofperson;
		END; 
	END;
GO

UPDATE view_141_142 SET idofperson = 202 WHERE idofperson = 1;
UPDATE view_141_142 SET firstname = 'Ion' WHERE firstname = 'Regina';
UPDATE view_141_142 SET Secondname = 'Wonder' WHERE secondname = 'Frank';
GO

SELECT * FROM view_141_142;
GO
SELECT * FROM lab141.dbo.Person;
GO
SELECT * FROM lab142.dbo.Person;
GO

DROP VIEW view_141_142;
GO
DROP TABLE lab141.dbo.Person;
GO
DROP TABLE lab142.dbo.Person;
GO