14

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
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