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
| CREATE DATABASE DB_TEST_HERITAGE
GO
USE DB_TEST_HERITAGE
GO
create table PERSONNE_PRS
(
PRS_ID int IDENTITY NOT NULL PRIMARY KEY,
PRS_NOM char(32) NOT NULL,
PRS_PRENOM VARCHAR(16)
)
GO
create table EMPLOYES_EMP
(
PRS_ID int NOT NULL PRIMARY KEY REFERENCES PERSONNE_PRS(PRS_ID),
EMP_MAT VARCHAR(8)
)
GO
--La vue suivante:
CREATE VIEW V_EMP
AS
SELECT P.PRS_ID, P.PRS_NOM, P.PRS_PRENOM, E.EMP_MAT
FROM PERSONNE_PRS P
INNER JOIN EMPLOYES_EMP E
ON P.PRS_ID=E.PRS_ID
GO
--Et le trigger
CREATE TRIGGER TGR_INS_EMP
ON V_EMP
INSTEAD OF INSERT
AS
BEGIN
DECLARE @PRS_NOM char(32), @PRS_PRENOM VARCHAR(16),
@EMP_MAT VARCHAR(8), @NEWID INT
DECLARE C INSENSITIVE CURSOR
FOR
SELECT PRS_NOM, PRS_PRENOM, EMP_MAT
FROM inserted
FOR READ ONLY
OPEN C
FETCH C INTO @PRS_NOM, @PRS_PRENOM, @EMP_MAT
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO dbo.PERSONNE_PRS (PRS_NOM,PRS_PRENOM)
VALUES (@PRS_NOM, @PRS_PRENOM);
SET @NEWID = SCOPE_IDENTITY();
INSERT INTO dbo.EMPLOYES_EMP (PRS_ID, EMP_MAT)
VALUES (@NEWID, @EMP_MAT);
FETCH C INTO @PRS_NOM, @PRS_PRENOM, @EMP_MAT
END
CLOSE C;
DEALLOCATE C;
END
GO
-- test :
INSERT INTO V_EMP
SELECT 0, 'DUPONT', 'Claude', '0987'
UNION ALL
SELECT 0, 'DUVAL', 'Pierre', '1234'
UNION ALL
SELECT 0, 'DUPOND', 'Marcel', '007'
SELECT * FROM V_EMP
PRS_ID PRS_NOM PRS_PRENOM EMP_MAT
----------- -------------------------------- ---------------- --------
1 DUPONT Claude 0987
2 DUVAL Pierre 1234
3 DUPOND Marcel 007 |
