Trigger calcul date

[chadoum]

Bonjour,
J'essaye de faire un trigger pour calculer une durée entre une date de début et une date de fin:

Code :

1
2
3
4
5
6
7
 
CREATE TRIGGER [dbo].[Duree] 
   ON  [MaTable]
   AFTER INSERT,DELETE,UPDATE
   AS UPDATE MaTable
 
SET DureeTravail = DateFin-DateDebut

Mais je reçois ce genre de résultat:
1900-01-02 15:33:00.000
J'aimerais avoir une durée avec Xjours ou Xheures.

Je pense qu'il faut utiliser un DATEDIFF mais je ne m'en sors pas trop avec la syntaxe DECLARE SET...

Merci d'avance

[SQLpro]

Utilisez un
DATEDIFF(HOUR, DateDebut, DateFin) / 24.0 => ceci vous donnera un nombre de jour décimal (par exemple 2,5 jours, soit 2 jours 12 h).

A +

[chadoum]

Merci

[aieeeuuuuu]

Bonjour

Cela dit, si j'ai bien compris, votre table contient :
- une colonne DateDebut
- une colonne DateFin
- une colonne Duree

Il serait sans doute préférable de définir cette dernière colonne comme colonne calculée, éventuellement persistante. Vous pouvez ainsi vous passer de votre trigger qui, soit dit en passant, recalcule la durée pour toutes les lignes de votre table à chaque insertion.

[chadoum]

Oui c'est juste. Mais comment définit-on une colonne calculée?

[aieeeuuuuu]

Dans votre cas, se serait :

Code SQL :

1
2
3
4
5
6
 
CREATE TABLE MaTable(
    DateDebut DATETIME,
    DateFin DATETIME,
    Duree AS (DATEDIFF(HOUR, DateDebut, DateFin) / 24.0) PERSISTED
)

Le PERSISTED est optionel. S'il est spécifié, le résultat du calcul est stocké au même titre qu'une autre colonne au lieu d’être calculé à la volée lors des requêtes. Cela permet de créer des index portant sur cette colonne.

[chadoum]

Merci pour l'info!

[elsuket]

Bonjour,

Effectivement plusieurs erreurs dans ce trigger, notamment celles qu'a relevé aieeeuuuuu.

En outre recalculer la durée de travail lors d'une suppression de ligne n'a plus d'intérêt puisque la ligne n'est plus là.
Cela donnerait dont le trigger suivant :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TRIGGER TR_A_IU_MaTable
	ON dbo.MaTable
	AFTER INSERT, UPDATE
AS 
BEGIN
	SET NOCOUNT ON
 
	IF 
	(
		UPDATE(DateDebut) 
		OR UPDATE(DateFin)
	)
	BEGIN
		UPDATE		dbo.MaTable
		SET		DureeTravail = DATEDIFF(hour, I.DateDebut, I.DateFin) / 24.0
		FROM		dbo.MaTable AS T
		INNER JOIN	inserted AS I ON T.primary_key = I.primary_key
	END
END

Un trigger DML (sur INSERT/UPDATE/DELETE) expose 2 tables virtuelles : inserted et deleted, qui ont strictement la même structure que la table à laquelle est attachée le trigger, et qui, dans le cas :

- d'un INSERT, n'est accessible que la table virtuelle inserted qui expose les valeurs des lignes qui viennent d'être ajoutées
- d'un UPDATE, sont accessibles les deux tables virtuelles inserted et deleted, qui respectivement exposent les anciennes et nouvelles valeurs pour toutes les lignes affectées par l'UPDATE
- d'un DELETE, n'est accessible que la table virtuelle deleted qui expose les valeurs des lignes qui viennent d'être supprimées.

La fonction UPDATE() permet de vérifier si une colonne a été mise à jour.
Dans le cas d'un UPDATE d'autres colonnes de la table MaTable que DateDebut et DateFin, il n'y a pas besoin de recalculer l'intervalle.

Mais comme l'a suggéré aieeeuuuuu, le mieux est d'utiliser une colonne calculée, ou encore de faire une vue.
Tout dépend si vous avez des requêtes qui vont filtrer sur la colonne DureeTravail.

Si c'est le cas, le mieux est de faire une colonne calculée :

Code :

1
2
3
4
5
6
ALTER TABLE dbo.MaTable
DROP COLUMN DureeTravail
GO
 
ALTER TABLE dbo.MaTable
ADD DureeTravail AS (DATEDIFF(hour, DateDebut, DateFin) / 24.0)

Puis de créer un index dessus pour faciliter les recherches (il n'y a pas besoin de la persister si vous ne la rendez pas visible à l'utilisateur)

Code :

1
2
CREATE INDEX IXNCMaTable
ON dbo.MaTable(DureeTravail)

Si vous la rendez pas visible à l'utilisateur, il suffit de la persister :

Code :

1
2
ALTER TABLE dbo.MaTable
ADD DureeTravail AS (DATEDIFF(hour, DateDebut, DateFin) / 24.0) PERSISTED

L'ordre de création de l'index est le même.

Maintenant s'il s'agit simplement d'afficher la durée de travail, et que vous ne filtrez pas dessus :

- soit vous faites la colonne calculée (1er script), et vous interrogez la table
- soit vous implémentez la vue suivante, qui s'interroge comme une table :

Code :

1
2
3
4
5
CREATE VIEW VDureeTravail
AS
	SELECT	desColonnes
		, DATEDIFF(hour, DateDebut, DateFin) / 24.0 AS DureeTravail
	FROM	dbo.MaTable

Puis :

Code :

1
2
3
SELECT	desColonnes
FROM	dbo.VDureeTravail
WHERE	[... ] -- Mais pas WHERE DureeTravail ...

@++

[chadoum]

Merci beaucoup pour les conseils elsuket