Trigger calcul date

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

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 +

Merci

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.

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

Dans votre cas, se serait :

Code:

---

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.

Merci pour l'info! :ccool:

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 ...

---

@++ ;)

Merci beaucoup pour les conseils elsuket