15

  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
USE master;
GO
IF db_id (N'lab151') IS NOT NULL
DROP DATABASE lab151;
GO
CREATE DATABASE lab151
ON (NAME = dat_lab151, FILENAME = 'C:\data\datlab151.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = log_lab151, FILENAME = 'C:\data\loglab151.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
IF db_id (N'lab152') IS NOT NULL
DROP DATABASE lab152;
GO
CREATE DATABASE lab152
ON (NAME = dat_lab152, FILENAME = 'C:\data\datlab152.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = log_lab152, FILENAME = 'C:\data\loglab152.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
USE lab151;
GO
CREATE TABLE Employee (idofperson int IDENTITY (1, 1) PRIMARY KEY,
firstname char(50) NOT NULL,
secondname char(50) NOT NULL,
numofpass int);
GO
CREATE TRIGGER deleteemp ON lab151.dbo.Employee AFTER DELETE
AS
DELETE lab152.dbo.Operation WHERE idofemployee IN (SELECT idofperson FROM deleted);
GO
USE lab152;
GO
CREATE TABLE Operation (idoper int IDENTITY (1000, 1) PRIMARY KEY,
dt datetime,
idofemployee int);
GO
CREATE TRIGGER insertoper ON lab152.dbo.Operation INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (SELECT idofemployee FROM inserted WHERE idofemployee IN (SELECT lab151.dbo.Employee.idofperson FROM lab151.dbo.Employee))
BEGIN
INSERT lab152.dbo.Operation (dt, idofemployee) SELECT dt, idofemployee FROM inserted;
END
ELSE
RAISERROR ('idofemployee not exist', 16, 1);
END;
GO
CREATE TRIGGER updateoper ON lab152.dbo.Operation INSTEAD OF UPDATE
AS
BEGIN
IF EXISTS (SELECT idofemployee FROM inserted WHERE idofemployee IN (SELECT lab151.dbo.Employee.idofperson FROM lab151.dbo.Employee))
BEGIN
UPDATE lab152.dbo.Operation SET idofemployee = (SELECT idofemployee FROM inserted);
END
ELSE
RAISERROR ('idofemployee not exist', 16, 1);
END;
GO
INSERT lab151.dbo.Employee (firstname, secondname, numofpass)
VALUES ('James', 'Ion', 1111),
('Jane', 'Ostin', 2222),
('July', 'Henderson', 3333),
('Carl', 'Joseph', 4444)
GO
USE master;
GO
INSERT lab152.dbo.Operation (dt, idofemployee) VALUES ('20130405', 1);
GO
--INSERT lab152.dbo.Operation (dt, idofemployee) VALUES ('20130405', 55);
--GO
--DELETE lab152.dbo.Operation WHERE dt = '20130405';
--GO
--DELETE lab151.dbo.Employee WHERE idofperson = 1;
--GO
UPDATE lab152.dbo.Operation SET idofemployee = 50 WHERE idofemployee = 1;
GO
SELECT * FROM lab151.dbo.Employee;
GO
SELECT * FROM lab152.dbo.Operation;
GO
DROP TABLE lab152.dbo.Operation;
GO
DROP TABLE lab151.dbo.Employee;
GO