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