[MSSQL 2000]Un petit trigger avec test de chevauchement de périodes

**m1ckros** · 29/01/2009, 23h13

Bonjour à tous,

Je galère depuis un petit moment sur un trigger qui devrait empêcher qu'un individu puisse exercer deux emplois en même temps. Ça se passe donc sur une table exercer_travail qui, sur un MCD, ferait association entre une table employeur et une table individu.

Je vous montre où est-ce que j'ai commencé à m'embarquer. Ça ressemble à un trigger avec une petite tendance curseur. Je ne savais pas si on pouvais faire ce genre d'association, mais à priori rien ne s'y opposait. Après il y a le fait qu'il n'y ait pas d'OVERLAPS avec MSSQL2000 donc j'ai dû improviser (à partir de ce message très utile de SQLpro). Enfin, pour ce qui est des CONVERT j'ai de sérieux doutes sur leur bonne utilisation.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
create trigger contrainte_pas_2_emplois_en_meme_temps
on exercer_travail
for insert
BEGIN
	declare @dateDbtIns int;
	declare @dateFinIns int;
	declare @curseur cursor;
	declare @date_debut int;
	declare @date_fin int;
	declare @msg char(110);
	declare @errno int;
 
	BEGIN
		select @dateDbtIns=CONVERT(INTEGER, inserted.code_date, 112) from inserted;
		select @dateFinIns=CONVERT(INTEGER, inserted.date_fin, 112) from inserted;
 
		set @curseur = cursor for (select CONVERT(INTEGER, exercer_travail.code_date, 112), CONVERT(INTEGER, exercer_travail.date_fin, 112) from exercer_travail, inserted where exercer_travail.code_individu = inserted.code_individu);
		open @curseur
		fetch next from @curseur into @date_debut, @date_fin
		while @@FETCH_STATUS = 0
		BEGIN
			/*Un gros if bien lourd parce qu'apparemment OVERLAPS n'existe pas sur MSSQL 2000 */
			if((@dateDbtIns > @date_debut AND (@dateDbtIns < @date_fin OR @dateFinIns < @date_fin))
				OR (@date_debut > @dateDbtIns AND (@date_debut < @dateFinIns OR @date_fin < @dateFinIns))
				OR (@dateDbtIns = @date_debut AND (@dateFinIns IS NOT NULL AND @date_fin IS NOT NULL)))
			BEGIN
				set @msg = 'Cette personne exerce déjà un emploi dans cette période. Une personne ne peut exercer 2 emplois en même temps.';
				set @errno = 30003;
				goto erreur;
			END
			else
			BEGIN
				commit
			END
			fetch next from @curseur into @date_debut, @date_fin
		END
		return
		erreur:
			raiserror @errno @msg;
			rollback transaction;
	END
END

En bref, je n'arrive à rien. Ça ne marche pas du tout. Je n'ai pas de message d'erreur à la création du trigger mais je n'en ai pas non plus lors des insertions avec des dates chevauchantes.

Je m'en remet à vous pour savoir ce qui ne va pas.

**hazamor** · 30/01/2009, 12h21

else
BEGIN
commit
END

D'abord cette commit est fausse, parceque il suffit que la première ligne testée pose le FAUX pour la condition (çàd ne chevauche pas avec les dates inseré) alors la ligne inserée sera commité et tu ne peut pas le retourner (Rollback).

En suite, Essayons de citer les cas possibles de relations entre deux couples de dates, pour savoir s'il ya un erreur logique de teste:

Soit A,B deux dates
et C,D de même

1) ABCD,CDAB: aucune relation

2) ACBD : AB chevauche CD (Déjà fait)

(@dateDbtIns > @date_debut AND (@dateDbtIns < @date_fin OR @dateFinIns < @date_fin)

3) CADB: CD chevauche AB (Déjà fait)

(@date_debut > @dateDbtIns AND (@date_debut < @dateFinIns OR @date_fin < @dateFinIns)

4) CABD: CD contient AB (Déjà fait)

(@date_debut > @dateDbtIns AND (@date_debut < @dateFinIns OR @date_fin < @dateFinIns)

5) ACDB: AB contient CD (Déjà fait)
(@dateDbtIns > @date_debut AND (@dateDbtIns < @date_fin OR @dateFinIns < @date_fin)

6) ACD, CAD ??: ç'est à dire si B est null (dans notre exemple @dateFinIns est nulle)

Encore, pourquoi tu converte les dates en entiers? L'operateur ">" fonctionne sur les dates!

**hazamor** · 30/01/2009, 12h22

L'essentiel corriger le "COMMIT"
mettre le dans la place qui convient ,çàd hors de la boucle.

**elsuket** · 31/01/2009, 23h56

Bonjour,

Il faut en tout premier lieu commencer par supprimer le curseur.
Un curseur est itératif, donc lent dans une base de données, qui est conçue pour traiter des ensembles et pas pour effectuer des traitements ligne à ligne comment dans un langage par objet, par exemple.
Pour vous en dégoûter, voici un bon article de SQLPro (encore !

) à ce sujet (COMMANDEMENT N°4, mais les autres sont bons aussi

)

Votre trigger ne produit pas le traitement recherché parce que vous comparez le contenu de la table virtuelle INSERTED à elle-même, ce qui ne vous retourne donc aucune différence :
- @dateDbtIns et @dateFinIns sont valuées à partir de INSERTED
- @date_debut et @date_fin sont valuées par le FETCH du curseur sur la même INSERTED.

Pour résumer, lors de l'exécution d'un déclencheur :

- les tables virtuelles INSERTED et DELETED ont strictement la même structure de données que la table cible, à laquelle est attachée le déclencheur,
- La table virtuelle INSERTED contient les nouvelles valeurs appliquées à la table cible lors d'un INSERT ou d'un UPDATE,
- La table virtuelle DELETED contient les anciennes valeurs contenues dans la table cible lors d'un DELETE ou d'un UPDATE.

Comme les tables virtuelles et la table cible ont la même structure, nous pouvons, pour effectuer la recherche de la requête, effectuer une auto-jointure sur la table exercer_travail.

Comme vous ne fournissez pas le DDL (et un jeu d'essai aurait été encore mieux !

), je me suis muni de ceux-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE exercer_travail
(
	IDEmploye INT,
	code_date DATETIME,
	date_fin DATETIME
)
GO
 
INSERT INTO exercer_travail VALUES (1, '01/01/2009 08:00:00.000', '03/01/2009 08:00:00.000')
INSERT INTO exercer_travail VALUES (1, '02/01/2009 08:00:00.000', '04/01/2009 08:00:00.000')
INSERT INTO exercer_travail VALUES (1, '06/01/2009 08:00:00.000', '08/01/2009 08:00:00.000')
INSERT INTO exercer_travail VALUES (1, '06/01/2009 08:00:00.000', '07/01/2009 08:00:00.000')
INSERT INTO exercer_travail VALUES (2, '01/01/2009 08:00:00.000', '03/01/2009 08:00:00.000')
INSERT INTO exercer_travail VALUES (2, '02/01/2009 08:00:00.000', '04/01/2009 08:00:00.000')
INSERT INTO exercer_travail VALUES (2, '06/01/2009 08:00:00.000', '08/01/2009 08:00:00.000')
INSERT INTO exercer_travail VALUES (2, '06/01/2009 08:00:00.000', '07/01/2009 08:00:00.000')

Dans la requête suivante, je considère les tables dérivées PERIODE_1 et PERIODE_2, comme, respectivement, A et B d'une part, et exercer_travail et INSERTED d'autre part :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
SELECT PERIODE_1.IDEmploye,
		PERIODE_1.DateDeb,
		PERIODE_1.DateFin,
		PERIODE_2.DateDeb,
		PERIODE_2.DateFin
FROM (
		SELECT A.IDEmploye,
				A.code_date,
				A.date_fin,
				COUNT(*)
		FROM dbo.exercer_travail A
		JOIN dbo.exercer_travail B
			ON A.IDEmploye = B.IDEmploye
			AND A.code_date <= B.code_date
			AND A.date_fin <= B.date_fin
		GROUP BY A.IDEmploye,
				A.code_date,
				A.date_fin
	) PERIODE_1 (IDEmploye, DateDeb, DateFin, Rang)
JOIN(
		SELECT A.IDEmploye,
				A.code_date,
				A.date_fin,
				COUNT(*)
		FROM dbo.exercer_travail A
		JOIN dbo.exercer_travail B
			ON A.IDEmploye = B.IDEmploye
			AND A.code_date <= B.code_date
			AND A.date_fin <= B.date_fin
		GROUP BY A.IDEmploye,
				A.code_date,
				A.date_fin
	) PERIODE_2 (IDEmploye, DateDeb, DateFin, Rang)
ON PERIODE_1.IDEmploye = PERIODE_2.IDEmploye
AND
(
	(
		PERIODE_1.DateDeb > PERIODE_2.DateDeb
		AND PERIODE_1.DateDeb < PERIODE_2.DateFin
		AND PERIODE_2.DateFin > PERIODE_1.DateDeb
		AND PERIODE_2.DateFin < PERIODE_1.DateFin
	)
	OR
	(
		PERIODE_2.DateDeb > PERIODE_1.DateDeb
		AND PERIODE_2.DateDeb < PERIODE_1.DateFin
		AND PERIODE_2.DateFin > PERIODE_1.DateFin
	)
	OR
	(
		PERIODE_2.DateDeb >= PERIODE_1.DateDeb
		AND PERIODE_2.DateFin <= PERIODE_1.DateFin
	)
)
AND PERIODE_1.Rang < PERIODE_2.Rang

La même requête pourrait être simplifiée sous SQL Server 2005 en se servant de la fonction de fenêtrage ROW_NUMBER() OVER(), et d'une CTE:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
WITH
	CTE_PERIODE AS
	(
		SELECT IDEmploye,
				code_date AS DateDeb,
				date_fin AS DateFin,
				ROW_NUMBER() OVER(PARTITION BY IDEmploye ORDER BY code_date) Rang
		FROM dbo.exercer_travail
	)
SELECT PERIODE_1.IDEmploye,
		PERIODE_1.DateDeb AS DateDeb1,
		PERIODE_1.DateFin AS DateFin1,
		PERIODE_2.DateDeb AS DateDeb2,
		PERIODE_2.DateFin AS DateFin2
FROM CTE_PERIODE AS PERIODE_1
JOIN CTE_PERIODE AS PERIODE_2
	ON PERIODE_1.IDEmploye = PERIODE_2.IDEmploye
	AND
	(
		(
			PERIODE_1.DateDeb > PERIODE_2.DateDeb
			AND PERIODE_1.DateDeb < PERIODE_2.DateFin
			AND PERIODE_2.DateFin > PERIODE_1.DateDeb
			AND PERIODE_2.DateFin < PERIODE_1.DateFin
		)
		OR
		(
			PERIODE_2.DateDeb > PERIODE_1.DateDeb
			AND PERIODE_2.DateDeb < PERIODE_1.DateFin
			AND PERIODE_2.DateFin > PERIODE_1.DateFin
		)
		OR
		(
			PERIODE_2.DateDeb >= PERIODE_1.DateDeb
			AND PERIODE_2.DateFin <= PERIODE_1.DateFin
		)
	)
	AND PERIODE_1.Rang < PERIODE_2.Rang

En remplaçant l'alias B de la table exercer_travail par la table virutelle INSERTED, le code de votre trigger devient :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE TRIGGER contrainte_pas_2_emplois_en_meme_temps
ON exercer_travail
FOR INSERT
AS
BEGIN
	DECLARE @szErreur VARCHAR(256)
 
	-- Collecte dans @szErreur de la liste des IDEmploye, séparés par
	-- des virgules, qui ont des périodes de travail qui se chevauchent
	SELECT @szErreur = ISNULL(@szErreur, '') + CAST(IDEmploye AS VARCHAR(10)) + ', '
	FROM
	(
		SELECT PERIODE_1.IDEmploye,
					PERIODE_1.DateDeb1,
					PERIODE_1.DateFin1,
					PERIODE_2.DateDeb2,
					PERIODE_2.DateFin2
		FROM (
				SELECT A.IDEmploye,
						A.code_date,
						A.date_fin,
						COUNT(*)
				FROM dbo.exercer_travail A
				JOIN INSERTED B
					ON A.IDEmploye = B.IDEmploye
					AND A.code_date <= B.code_date
					AND A.date_fin <= B.date_fin
				GROUP BY A.IDEmploye,
						A.code_date,
						A.date_fin
			) PERIODE_1 (IDEmploye, DateDeb1, DateFin1, Rang)
		JOIN(
				SELECT A.IDEmploye,
						A.code_date,
						A.date_fin,
						COUNT(*)
				FROM dbo.exercer_travail A
				JOIN INSERTED B
					ON A.IDEmploye = B.IDEmploye
					AND A.code_date <= B.code_date
					AND A.date_fin <= B.date_fin
				GROUP BY A.IDEmploye,
						A.code_date,
						A.date_fin
			) PERIODE_2 (IDEmploye, DateDeb2, DateFin2, Rang)
		ON PERIODE_1.IDEmploye = PERIODE_2.IDEmploye
		AND
		(
			(
				PERIODE_1.DateDeb1 > PERIODE_2.DateDeb2
				AND PERIODE_1.DateDeb1 < PERIODE_2.DateFin2
				AND PERIODE_2.DateFin2 > PERIODE_1.DateDeb1
				AND PERIODE_2.DateFin2 < PERIODE_1.DateFin1
			)
			OR
			(
				PERIODE_2.DateDeb2 > PERIODE_1.DateDeb1
				AND PERIODE_2.DateDeb2 < PERIODE_1.DateFin1
				AND PERIODE_2.DateFin2 > PERIODE_1.DateFin1
			)
			OR
			(
				PERIODE_2.DateDeb2 >= PERIODE_1.DateDeb1
				AND PERIODE_2.DateFin2 <= PERIODE_1.DateFin1
			)
		)
		AND PERIODE_1.Rang < PERIODE_2.Rang
	) TMP (IDEmploye, DateDeb1, DateFin1, DateDeb2, DateFin2)
 
	-- Si la chaîne est valuée (<=> IS NOT NULL), alors on lève un exception
	-- et on défait la transaction
	IF @szErreur IS NOT NULL
	BEGIN
		SET @szErreur = 'Les employés d''identifiant %s ne peuvent avoir des périodes d''emploi se chevauchant.', 16, 1, @szErreur)
		RAISERROR(@szErreur, 16, 1)
		ROLLBACK TRANSACTION
	END
	-- Sinon, il n'y a pas d'employés pour lesquels on a tenté d'ajouter des emplois dont les périodes
	-- chevauchent des périodes durant lesquelles l'employé est déjà affecté à une mission
	-- La transaction sera donc validée automatiquement
END

@++

**hazamor** · 01/02/2009, 15h17

Envoyé par elsuket

Votre trigger ne produit pas le traitement recherché parce que vous comparez le contenu de la table virtuelle INSERTED à elle-même, ce qui ne vous retourne donc aucune différence :
- @dateDbtIns et @dateFinIns sont valuées à partir de INSERTED
- @date_debut et @date_fin sont valuées par le FETCH du curseur sur la même INSERTED.

Effectivement, si vous voulez sauvegardeez le travaille avec le curseur commencez par supprimer la condition de WHERE:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
SELECT @dateDbtIns=CONVERT(INTEGER, inserted.code_date, 112) FROM inserted;

SELECT @dateFinIns=CONVERT(INTEGER, inserted.date_fin, 112) FROM inserted;

SET @curseur = cursor FOR (SELECT CONVERT(INTEGER, exercer_travail.code_date, 112), CONVERT(INTEGER, exercer_travail.date_fin, 112) FROM exercer_travail, inserted

Pour elsuket vous avez raison d'eviter de travailler avec le curseur, et de travailler avec des requetes.

**elsuket** · 02/02/2009, 09h00

Bonjour,

Effectivement, si vous voulez sauvegardeez le travaille avec le curseur commencez par supprimer la condition de WHERE

Cela revient à effectuer le produit cartésien de la table cible et de la table INSERTED, ce qui n'a pas d'intérêt.
D'autre part une jointure ne se spécifie pas par un WHERE mais par un JOIN.

vous avez raison d'éviter de travailler avec le curseur, et de travailler avec des requetes.

Les curseurs sont à bannir autant que cela est possible, et plus encore des triggers qui doivent s'exécuter le plus rapidement possible

@++

**m1ckros** · 02/02/2009, 19h26

Eh bien, merci à vous trois pour votre aide !

elsuket, je crois avoir à peu près compris l'idée de départ. Mais je dois dire que je me suis noyé dans tout ce code tellement il y a de jointures et de requêtes.

Apparemment, la solution la plus optimisée est celle que vous proposez SQLpro. Je n'avais jamais vu ça auparavant, mais je pense comprendre. Et la méthode me plait beaucoup.

Après, étant donné que je risque de devoir expliquer ça devant un jury, il vaut mieux que je comprenne à 100% ce qui est fait. Donc, dans un premier temps je vais essayer de bien décortiquer le code, le tester, peut-être en parler avec mon prof d'info. Et, si au final ça ne le fait pas, je me rabattrai sur mon trigger avec curseur minable.

Mais, à première vue, ça n'a pas l'air bien sorcier.

Je teste, je décrypte et si besoin est, je repasserai.

**SQLpro** · 02/02/2009, 21h50

Mais non, parles de tes échecs, de tes expériences et de l'aide que tu as eu.
Teste donc toutes ces solutions et essaye par toi même de montrer celle qui est la plus intéressante.
Internet est fait pour cela et on ne t'en voudra pas d'avoir intelligemment essayer de trouver la meilleurs solution sur le web, en doutant de celle que tu avait commencer à élaborer !
Le jury saura aprécier.

(et c'est un vis de juriste... si tu lis ma signature !)

A +

[MSSQL 2000]Un petit trigger avec test de chevauchement de périodes

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager