--2 IF OBJECT_ID insert_IndexView TR IS NOT NULL DROP TRIGGER insert_I

 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
--2.1
IF OBJECT_ID ('insert_IndexView','TR') IS NOT NULL
DROP TRIGGER insert_IndexView;
GO
CREATE TRIGGER insert_IndexView ON IndexView INSTEAD OF INSERT
AS
BEGIN
INSERT Operation(idoper, dt) SELECT idoper, dt FROM inserted where idoper NOT IN (select idoper from Operation);
INSERT Credit(ido, sumofmoney) SELECT idoper, sumofmoney FROM inserted;
END;
GO
--2.2
IF OBJECT_ID ('delete_IndexView','TR') IS NOT NULL
DROP TRIGGER delete_IndexView;
GO
CREATE TRIGGER delete_IndexView ON IndexView INSTEAD OF DELETE
AS DELETE FROM Operation WHERE idoper IN (SELECT idoper FROM deleted);
DELETE FROM Credit WHERE ido IN (SELECT idoper FROM deleted);
GO
--2.3
IF OBJECT_ID ('update_IndexView','TR') IS NOT NULL
DROP TRIGGER update_IndexView;
GO
CREATE TRIGGER update_IndexView ON IndexView INSTEAD OF UPDATE
AS
IF (UPDATE(idoper))
BEGIN
/*IF EXISTS (SELECT * FROM Operation JOIN inserted ON inserted.idoper = Operation.idoper)
BEGIN
RAISERROR('PK exists', 16, 1);
END
ELSE*/
BEGIN
CREATE TABLE curtab1 (id1 int IDENTITY PRIMARY KEY, idnew int);
CREATE TABLE curtab2 (id2 int IDENTITY PRIMARY KEY, idold int);
INSERT curtab1 (idnew) SELECT idoper FROM inserted;
INSERT curtab2 (idold) SELECT idoper FROM deleted;
UPDATE Operation SET idoper = idnew
FROM curtab1 INNER JOIN curtab2 ON curtab1.id1 = curtab2.id2
WHERE Operation.idoper = curtab2.idold;
UPDATE Credit SET ido = idnew
FROM curtab1 INNER JOIN curtab2 ON curtab1.id1 = curtab2.id2
WHERE Credit.ido = curtab2.idold;
DROP TABLE curtab1;
DROP TABLE curtab2;
END;
END;
GO
INSERT IndexView(idoper, dt, sumofmoney) VALUES (144, '20130907', $2000);
INSERT IndexView(idoper, dt, sumofmoney) VALUES (144, '20130908', $4000);
GO
SELECT * FROM Operation;
GO
SELECT * FROM Credit;
GO
DELETE FROM IndexView WHERE idoper = 144;
GO
SELECT * FROM Operation;
GO
SELECT * FROM Credit;
GO
UPDATE IndexView SET idoper = 10 WHERE idoper = 2;
GO
SELECT * FROM Operation;
GO
SELECT * FROM Credit;
GO
IF OBJECT_ID ('IndexView', 'view') IS NOT NULL
DROP VIEW IndexView;
GO
CREATE VIEW IndexView WITH SCHEMABINDING AS
SELECT a.idoper, a.dt, b.sumofmoney FROM
dbo.Operation a INNER JOIN dbo.Credit b ON a.idoper = b.ido;
GO
SELECT * FROM IndexView;
GO