Création d'un trigger pour l'insertion et la MAJ à la fois

Version imprimable

15/01/2013, 16h17
haylox

Création d'un trigger pour l'insertion et la MAJ à la fois

Salut,
est ce qu'on peut créer un trigger After insert,update à la fois ? c'est ce que je crois on ma demandé dans l'exercice suivant.
Voici la partie de l'exercice ou j'ai pas compris:

On considère le shéma relationnel suivant:

Ordinateur(ID_Ordinateur,Nom_Ordinateur,Date_Mode,Num_serie,Contact,Commentaire,Nom_modele,#ID_utilisateur,#ID_lieu)
Utilisateur(Id_utilisateur,Nom_Utilisateur,MDP,Email,Téléphone,Type)
LIEU(ID_lieu,Nom)
System(ID_os,Nom_System,Prix_Systeme)

La question est : Créer un trigger sur la table Utilisateur qui réalise les contrôles suivants,pendant l'insertion ou la modification:
-L'utilisateur n'existe pas déjà.
-Le mot de passe contient au minimum 7 caractères.
-Le numéro de téléphone commence soit par 06 ou 05.
-L'E-mail contient le caractère @.

J'ai cherché les syntaxes des triggers et j'ai trouvé qu'on peut créer des trigger qui se réalise non pas aprés l'ajout , l'insertion ou la MAJ mais aussi on peut combiner entre eux.alors pour cet exercice j'aurai faire le trigger de cette façon là:

Create trigger Trigger_INS_UPD on Utilisateur
instead of Insert,update
as
/*là je suis bloqué je ne sais pas si je vais écrire le code concernant l'ajout ou update:*/

est ce qu'il ya un instruction pour distinguer l'opération qui a appellé le trigger ?
Remarque: l'insertion ou la mise à jour se font ligne par ligne
16/01/2013, 11h12
aieeeuuuuu

Bonjour,

L'auteur de l'execrice manque cruellement d'imagination pour n'avoir pas pu trouver un trigger utile. En effet, les règles que vous ennoncez peuvent etre vérifiées directement par des contraintes...

Mais comme il s'agit d'un exercice, j'imagine que cette réponse ne conviendra pas... on va donc tenter de vous aider à y voir plus clair.

Vous pouvez en effet créer un même trigger qui sera déclenché à l'insert et à l'update comme vous l'avez commencé.
Vous faites un trigger "INSTEAD OF ", vous devrez donc -si les données sont valides- les insérer ou les modifier dans votre trigger (ce type de trigger remplace la commande qui l'a declenché). Alternativement, vous pouvez créer un trigger AFTER, et annuler la transaction si une règle n'est pas respectée.

Pour ce qui est de différencier les insertions des mises à jour, je vous conseille de créer d'abord un trigger qui vérifie juste les insertion, vous y verrez plus clair, comprendrez mieux le fonctionnement et l'utilisation des triggers (et l'utilisation des tables INSERTED et DELETED). Vous pourrez ensuite facilement modifier votre trigger pour qu'il gère les insertions et les mises à jour.

Enfin pour votre dernière remarque, il serait quand même souhaitable de faire votre trigger de manière à ce qu'il gère les cas de mises à jour multiples.

Bon courage, et revenez nous voir si vous rencontrez des difficultés.

Salut,
j'aurai faire le trigger de l'ajout de cette façon là:

Code:

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
Create trigger Check_Insert on Utilisateur
after insert
as
declare @Nom_Utilisateur nvarchar(10),@MDP nvarchar(20),@Email nvarchar(50),@Telephone nvarchar(15),@Nbr_Utilisateur int
 
declare Cur Cursor
for 
select @ID_Utilisateur ,@Nom_Utilisateur ,@MDP,@Email,@Telephone,@Type from inserted
open cur
 
fetch next from cur into @ID_Utilisateur ,@Nom_Utilisateur ,@MDP,@Email,@Telephone,@Type 
 
while(@@Fetch_status=0)
begin
select @Nbr_Utilisateur=count(Telephone) from Utilisateur where Nom_Utilisateur=@Nom_Utilisateur and Téléphone=@Telephone 
 
if @Nbr_Utilisateur>=2
begin
raiserror('Cet utilisayeur existe déjà.',11,10)
ROLLBACK
end
 
if len(@MDP<7)
begin
raiserror('Le mot de passe doit contenir au moins 7 caractère.',11,10)
ROLLBACK
end
 
if @Telephone not in ('06%','05%')
begin
raiserror('Le numero de telephone doit commencer soit par 06 ou 05.',11,10)
ROLLBACK
end
 
if @Email not like '%@%'
begin
raiserror('L'Email doit contenir un @.',11,10)
ROLLBACK
end
fetch next from cur into @ID_Utilisateur ,@Nom_Utilisateur ,@MDP,@Email,@Telephone,@Type 
end 
close cur
deallocate cur

comment je pourrai le modifier pour contenir les instructions du UPDATE too?
Merci!

17/01/2013, 13h31
aieeeuuuuu

Bonjour,

C'est un bon début...
Vous pouvez déjà vous passer du curseur qui n'est pas utile, et travailler directement sur la table INSERTED. Tel qu'est fait votre déclencheur, il annulera la transaction à la première erreur trouvées. Avez vous des consignes particulières concernant son fonctionnement en cas de données non valides ?

au passage, remplacez ceci

Code:

raiserror('L'Email doit contenir un @.',11,10)

par cela

Code:

raiserror('L''Email doit contenir un @.',11,10)

Il faut doubler la quote à l’intérieur d'une chaine, sinon ce sera considéré comme une fin de chaine...

PS : dans votre prochain post, merci de mettre votre code entre balises code, c'est beaucoup plus lisible

Bonjour,

Comme l'a écrit aieeeuuuuu, il est tout à fait possible de vérifier la qualité des données à l'aide de contraintes de domaine ou d'unicité dans le cas de l'existence de l'utilisateur.
D'autre part les vérifications (sauf celle de l'existence de l'utilisateur peut-être) peuvent être effectuées dans l'application.

Si l'une d'entre elles n'est pas vérifiée, alors toute la transaction est annulée, alors que l'on peut vouloir les enregistrer dans une table réservée à cet effet.
Cependant, le déclencheur est déconseillé pour deux raisons :

- Il rallonge la durée de la transaction qui l'a déclenché (il fait partie de celle-ci), diminuant ainsi la concurrence d'accès
- En cas d'annulation de la transaction, il est nécessaire des parcourir le fichier du journal des transactions dans le sens inverse de sa séquence, ce qui est coûteux en ressources. L'avantage des contraintes est qu'elles sont vérifiées en amont, ce qui évité ce possible travail.

Mais comme il s'agit d'un exercice, voici une proposition.
Je vous donne la définition de la table avec les contraintes qu'il convient normalement de mettre, pour référence ;)

Code:

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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
CREATE TABLE Utilisateur
(
	Id_Utilisateur int NOT NULL IDENTITY(-2147483648, 1)
		CONSTRAINT PK_Utilisateur PRIMARY KEY
	, Nom_Utilisateur varchar(16) NOT NULL
		-- CONSTRAINT UQ_Utilisateur UNIQUE
	, mdp varchar(32) NOT NULL
		-- CONSTRAINT CHK_Utilisateur__mdp CHECK(LEN(mdp) >= 7)
	, email varchar(256) NOT NULL
		-- CONSTRAINT CHK_Utilisateur__email CHECK (email LIKE '%@%.%')
	, telephone char(10) NOT NULL -- Numérotation Française seulement ?
		-- CONSTRAINT CHK_Utilisateur_telephone CHECK (telephone LIKE '0[5-6]%')
)
GO
 
-- Création d'une table de journalisation des erreurs
SELECT	TOP 0 *
INTO	dbo.Utilisateur_error_log
FROM	dbo.Utilisateur
GO
 
-- Ajout d'une colonne d'hotodatage
ALTER TABLE dbo.Utilisateur_error_log
ADD log_time datetime NOT NULL
	CONSTRAINT DF_Utilisateur_error_log__log_time DEFAULT (GETDATE())
GO
 
-- Ajout d'une colonne de type de modification
ALTER TABLE dbo.Utilisateur_error_log
ADD type_modification char(1) NOT NULL
	CONSTRAINT CHK_Utilisateur_error_log__type_modification CHECK(type_modification IN ('I', 'U'))
GO
 
-- Et voilà le trigger
CREATE TRIGGER TR_IOF_IU_Utilisateur
	ON dbo.Utilisateur
AFTER INSERT, UPDATE
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE @is_update bit = 0
 
	-- S'il existe des lignes dans la table virtuelle DELETED,
	-- nous sommes dans le cas d'un UPDATE.
	IF EXISTS
	(
		SELECT	*
		FROM	deleted
	)
	BEGIN
		SET @is_update = 1
	END
 
	-- Cas de l'INSERT
	IF @is_update = 0
	BEGIN
		-- On ajoute à la table seulement les
		-- utilisateurs dont les données sont valides
		INSERT	INTO dbo.Utilisateur
		(
			Nom_Utilisateur
			, mdp
			, email
			, telephone
		)
		SELECT	Nom_Utilisateur
			, mdp
			, email
			, telephone
		FROM	INSERTED AS I
		WHERE	NOT EXISTS
			(
				SELECT	*
				FROM	dbo.Utilisateur AS U
				WHERE	I.Nom_Utilisateur = U.Nom_Utilisateur
			)
		AND	LEN(I.mdp) >= 7 -- La longueur du mot de passe est d'au moins 7 caractères
		AND	I.email LIKE '%@%.%' -- l'email contient un arobase, mais aussi un point après cet arobase
		AND	I.telephone LIKE '0[5-6]%' -- le numéro de téléphone commence par 05 ou 06
 
		-- On enregistre dans la table des rejets
		-- les données collectées pour les utilisateurs dont celles-ci sont incorrectes
		INSERT	INTO dbo.Utilisateur_error_log
		(
			Nom_Utilisateur
			, mdp
			, email
			, telephone
			, type_modification
		)
		SELECT	Nom_Utilisateur
			, mdp
			, email
			, telephone
			, 'I'
		FROM	INSERTED AS I
		WHERE	EXISTS
			(
				SELECT	*
				FROM	dbo.Utilisateur AS U
				WHERE	I.Nom_Utilisateur = U.Nom_Utilisateur
			)
		OR	LEN(I.mdp) < 7 -- La longueur du mot de passe est d'au moins 7 caractères
		OR	I.email NOT LIKE '%@%.%' -- l'email contient un arobase, mais aussi un point après cet arobase
		OR	I.telephone NOT LIKE '0[5-6]%' -- le numéro de téléphone commence par 05 ou 06
 
		IF @@ROWCOUNT > 0
		BEGIN
			RAISERROR('Certains utilisateurs n''ont pas été enregistrés car les données sont incorrectes', 10, 1)
		END
	END
	ELSE
	BEGIN
		UPDATE		dbo.Utilisateur
		SET		Nom_Utilisateur = I.Nom_Utilisateur
				, mdp = I.mdp
				, email = I.email
				, telephone = I.telephone
		FROM		dbo.Utilisateur AS U
		INNER JOIN	INSERTED AS I
					ON I.Id_Utilisateur = U.Id_Utilisateur
		WHERE		LEN(I.mdp) >= 7 -- La longueur du mot de passe est d'au moins 7 caractères
		AND		I.email LIKE '%@%.%' -- l'email contient un arobase, mais aussi un point après cet arobase
		AND		I.telephone LIKE '0[5-6]%' -- le numéro de téléphone commence par 05 ou 06
 
		-- On enregistre dans la table des rejets
		-- les données collectées pour les utilisateurs dont celles-ci sont incorrectes
		INSERT	INTO dbo.Utilisateur_error_log
		(
			Nom_Utilisateur
			, mdp
			, email
			, telephone
			, type_modification
		)
		SELECT	Nom_Utilisateur
			, mdp
			, email
			, telephone
			, 'U'
		FROM	INSERTED AS I
		WHERE	LEN(I.mdp) < 7 -- La longueur du mot de passe est d'au moins 7 caractères
		OR	I.email NOT LIKE '%@%.%' -- l'email contient un arobase, mais aussi un point après cet arobase
		OR	I.telephone NOT LIKE '0[5-6]%' -- le numéro de téléphone commence par 05 ou 06
 
		IF @@ROWCOUNT > 0
		BEGIN
			RAISERROR('Certains utilisateurs n''ont pas été modifiés car les données sont incorrectes', 10, 1)
		END
	END
END

@++ ;)

Citation:

Envoyé par elsuket

Code:

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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
CREATE TABLE Utilisateur
(
	Id_Utilisateur int NOT NULL IDENTITY(-2147483648, 1)
		CONSTRAINT PK_Utilisateur PRIMARY KEY
	, Nom_Utilisateur varchar(16) NOT NULL
		-- CONSTRAINT UQ_Utilisateur UNIQUE
	, mdp varchar(32) NOT NULL
		-- CONSTRAINT CHK_Utilisateur__mdp CHECK(LEN(mdp) >= 7)
	, email varchar(256) NOT NULL
		-- CONSTRAINT CHK_Utilisateur__email CHECK (email LIKE '%@%.%')
	, telephone char(10) NOT NULL -- Numérotation Française seulement ?
		-- CONSTRAINT CHK_Utilisateur_telephone CHECK (telephone LIKE '0[5-6]%')
)
GO
 
-- Création d'une table de journalisation des erreurs
SELECT	TOP 0 *
INTO	dbo.Utilisateur_error_log
FROM	dbo.Utilisateur
GO
 
-- Ajout d'une colonne d'hotodatage
ALTER TABLE dbo.Utilisateur_error_log
ADD log_time datetime NOT NULL
	CONSTRAINT DF_Utilisateur_error_log__log_time DEFAULT (GETDATE())
GO
 
-- Ajout d'une colonne de type de modification
ALTER TABLE dbo.Utilisateur_error_log
ADD type_modification char(1) NOT NULL
	CONSTRAINT CHK_Utilisateur_error_log__type_modification CHECK(type_modification IN ('I', 'U'))
GO
 
-- Et voilà le trigger
CREATE TRIGGER TR_IOF_IU_Utilisateur
	ON dbo.Utilisateur
AFTER INSERT, UPDATE
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE @is_update bit = 0
 
	-- S'il existe des lignes dans la table virtuelle DELETED,
	-- nous sommes dans le cas d'un UPDATE.
	IF EXISTS
	(
		SELECT	*
		FROM	deleted
	)
	BEGIN
		SET @is_update = 1
	END
 
	-- Cas de l'INSERT
	IF @is_update = 0
	BEGIN
		-- On ajoute à la table seulement les
		-- utilisateurs dont les données sont valides
		INSERT	INTO dbo.Utilisateur
		(
			Nom_Utilisateur
			, mdp
			, email
			, telephone
		)
		SELECT	Nom_Utilisateur
			, mdp
			, email
			, telephone
		FROM	INSERTED AS I
		WHERE	NOT EXISTS
			(
				SELECT	*
				FROM	dbo.Utilisateur AS U
				WHERE	I.Nom_Utilisateur = U.Nom_Utilisateur
			)
		AND	LEN(I.mdp) >= 7 -- La longueur du mot de passe est d'au moins 7 caractères
		AND	I.email LIKE '%@%.%' -- l'email contient un arobase, mais aussi un point après cet arobase
		AND	I.telephone LIKE '0[5-6]%' -- le numéro de téléphone commence par 05 ou 06
 
		-- On enregistre dans la table des rejets
		-- les données collectées pour les utilisateurs dont celles-ci sont incorrectes
		INSERT	INTO dbo.Utilisateur_error_log
		(
			Nom_Utilisateur
			, mdp
			, email
			, telephone
			, type_modification
		)
		SELECT	Nom_Utilisateur
			, mdp
			, email
			, telephone
			, 'I'
		FROM	INSERTED AS I
		WHERE	EXISTS
			(
				SELECT	*
				FROM	dbo.Utilisateur AS U
				WHERE	I.Nom_Utilisateur = U.Nom_Utilisateur
			)
		OR	LEN(I.mdp) < 7 -- La longueur du mot de passe est d'au moins 7 caractères
		OR	I.email NOT LIKE '%@%.%' -- l'email contient un arobase, mais aussi un point après cet arobase
		OR	I.telephone NOT LIKE '0[5-6]%' -- le numéro de téléphone commence par 05 ou 06
 
		IF @@ROWCOUNT > 0
		BEGIN
			RAISERROR('Certains utilisateurs n''ont pas été enregistrés car les données sont incorrectes', 10, 1)
		END
	END
	ELSE
	BEGIN
		UPDATE		dbo.Utilisateur
		SET		Nom_Utilisateur = I.Nom_Utilisateur
				, mdp = I.mdp
				, email = I.email
				, telephone = I.telephone
		FROM		dbo.Utilisateur AS U
		INNER JOIN	INSERTED AS I
					ON I.Id_Utilisateur = U.Id_Utilisateur
		WHERE		LEN(I.mdp) >= 7 -- La longueur du mot de passe est d'au moins 7 caractères
		AND		I.email LIKE '%@%.%' -- l'email contient un arobase, mais aussi un point après cet arobase
		AND		I.telephone LIKE '0[5-6]%' -- le numéro de téléphone commence par 05 ou 06
 
		-- On enregistre dans la table des rejets
		-- les données collectées pour les utilisateurs dont celles-ci sont incorrectes
		INSERT	INTO dbo.Utilisateur_error_log
		(
			Nom_Utilisateur
			, mdp
			, email
			, telephone
			, type_modification
		)
		SELECT	Nom_Utilisateur
			, mdp
			, email
			, telephone
			, 'U'
		FROM	INSERTED AS I
		WHERE	LEN(I.mdp) < 7 -- La longueur du mot de passe est d'au moins 7 caractères
		OR	I.email NOT LIKE '%@%.%' -- l'email contient un arobase, mais aussi un point après cet arobase
		OR	I.telephone NOT LIKE '0[5-6]%' -- le numéro de téléphone commence par 05 ou 06
 
		IF @@ROWCOUNT > 0
		BEGIN
			RAISERROR('Certains utilisateurs n''ont pas été modifiés car les données sont incorrectes', 10, 1)
		END
	END
END

@++ ;)

Merci pour la réponse!