Discussion :

[olibara]

Bonjour


J'ai une table qui doit enregistrer des relevés de compteur : Maximum une valeur par date (jour)
Mais l’opérateur peut se tromper et faire plusieurs relevé a la même date.
Seul le dernier doit être enregistré
Par contre les mesures consécutives a la même date devrait être enregistrée dans une table de Log

Je voudrais donc définir un Trigger pour contrôler cela mais avant d'inventer n'importe quoi je me demande s'il existe une manière recommandée pour ce type d'opération

Voici la table principale, la table de log s'appelle IndexHistoryLog et a le même design

Merci pour votre aide

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE [dbo].[IndexHistory](
	[pkId] [int] IDENTITY(1,1) NOT NULL,
	[CounterId] [int] NOT NULL,
	[IndexValue] [decimal](18, 4) NOT NULL,
	[Date] [datetime] NOT NULL,
	[OperatorId] [int] NULL,
	[pictureUrl] [varchar](200) NULL,
	[Remark] [varchar](200) NULL,
	[isDisabled] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
	[pkId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

[StringBuilder]

Si la table de "log" a la même structure, alors tu prends le problème à l'envers.

Ne conserve que cette table, et crée une vue dessus pour ramener, pour chaque jour, la première mesure de chaque compteur.

Ceci évitera de passer par un trigger inutile et de dupliquer des données, etc.

[olibara]

Merci Stringbuilder

Oui je connais cette solution mais elle ne me séduit pas vraiment dans ce contexte
Je pourrais faire une vue sur base d'un over partition qui ne rendrait que la dernière mesure par jour

MAIS :
1- Il n'est pas certain que ma table Log aura exactement la même structure
2- La vue aura un impact négatif sur la performance
3- Conceptuellement et pratiquement je préfère pouvoir accéder une table "Propre" ne contenant que l'information pertinente en "prod", la table log n'etant utile que dans un cadre d'audit
4- Donc Mon "à l'Envers" et ton, "a l'Endroit" ne sont que deux manières de voir les choses tout a fait réversibles

Et pour ma part je suis toujours intéressé a voir la manière la plus élégante de résoudre mon idée via un trigger

[StringBuilder]

Une vue devrait avoir un impact pour ainsi dire transparent sur les performances.

A mon avis, c'est pas un élément à prendre en considération, d'autant qu'à l'inverse, un trigger aura, lui, de facto un impacte significativement négatif sur les performances.

D'un point de vue conception, dupliquer des données est une erreur.
Cela se solde par une possibilité d'avoir des données incohérentes : si un trigger va aisément permettre de modifier les données du log, comment gérer les modifications dans la table de log ?

Une vue pouvant être mise à jour (par l'apposition de triggers simples si nécessaires) il est tout à fait possible de travailler "simplement" en PROD sur la vue sans se soucier du log.

Une solution à base de triggers qui vont faires des insert, update, delete, puis recopie dans une autre table, ça sera forcément "sale" car le nombre de cas est important, et vouloir trop simplifier risque de poser problème : que faire si le technicien saisit une donnée, puis une seconde, puis décide de supprimer la ligne ? Doit-on conserver le log ? Doit-on remettre la première donnée ?
=> Il faut stocker les données simplement, et proposer une IHM permettant de choisir l'action à effectuer.

Enfin, à partir de SQL Server 2016, il existe des tables "temporelles" : de telles tables historisent les modifications de données automatiquement :
- https://docs.microsoft.com/en-us/sql...emporal-tables
- https://www.sqlshack.com/track-histo...mporal-tables/
=> Si la solution de la vue n'est pas celle que vous retenez, étudiez au moins celle-là. Attention cependant, elle ne couvrira pas forcément vos besoin en cas de suppression de données...

[olibara]

Merci StringBuilder

Mais je ne suis pas vraiment convaincu du non-impact de performance par l'usage d'une vue
J'avais fait cela pour avoir le dernier prix des items d'un catalogue dont les tarifs évoluaient très souvent

Au bout de deux ans et quelques millions d'entrées ça devenait de plus en plus poussif !
Et j'avais passé pas mal de temps a optimiser les index, le ove partition etc !

J'ai donc basculé en double table avec un Trigger et tout le monde est redevenu heureux

Mais le cas était un peu plus simple car je ne conservait que le dernier update dans la table de reference et TOUTES les modification etaient logué avec un code CRUD dans la table historique
Ici c'est plus subtil car je ne veux loguer QUE les modifications portant sur une meme date
Donc on ajoute un index a une date donnée : pas de log
On Ajoute (insert) ou on modifie (update) ce même index pour la même date : on fait un Update et on logue l'info
La question du delete ne se pose pas : il n'y en a pas
Pour info les opération se font via une application mobile avec un nombre d'opérations limitées.

Donc ma question ne porte pas vraiment sur la pertinence de mettre un Trigger (mon expérience m'a déja démontré l'efficacité de la solution) mais sur la bonne manière ou les erreurs a eviter ppour ce trigger qui doit prendre quelques décisions logique.. Mais son impact sera limité car la frequence des requetes est asser faible

[François DORIN]

Bonjour,

Si la vue a un impact, alors peut-être serait-il bon de tester une vue indexée ! D'autant plus que les accès en écriture sont relativement faibles.

Tu évites ainsi :

• la duplication des données (bon, elles seront dupliquées physiquement, mais c'est SQL Server qui gère cela, pas toi !)
• une vue couteuse

Bonus : tu peux créer des index sur ta vue...

[olibara]

Je ne comprends pas pourquoi vous insister tellement sur

1- La duplication des données
Il n'y a pas de duplication car précisément je veux utiliser ce trigger pour stocker ailleurs dans une table de log, des donnée opérationnelles historiques (majoritairement liée a des erreurs de manipulation ou de mesure) qui ne doivent pas etre exploitées en Prod

2- Le refus d'usage d'un Trigger
Le trigger n'a pas été inventé pour simplement garnir un catalogue de Commandes SQL

Mais bon puisque personne ne semble vraiment vouloir m'aider dans la logique de ce trigger je vais me debrouiller en essai erreut classique !

[François DORIN]

1- La duplication des données
Il n'y a pas de duplication car précisément je veux utiliser ce trigger pour stocker ailleurs dans une table de log, des donnée opérationnelles historiques (majoritairement liée a des erreurs de manipulation ou de mesure) qui ne doivent pas etre exploitées en Prod

Que tu le veuilles ou non, dans ton cas, tu as bien de la duplication de données. Pourquoi on insiste tant de dessus ? Parce que très souvent des problèmes d'exploitation proviennent de cette duplication.

2- Le refus d'usage d'un Trigger
Le trigger n'a pas été inventé pour simplement garnir un catalogue de Commandes SQL

Tout à fait. Maintenant, on est juste en train de te dire que dans le cas présenté, le trigger ne nous parait pas opportun. Grâce aux triggers, on peut faire plein de choses, notamment (liste non exhaustive) :

• autoriser la mise à jour de vue (triggers INSTEAD OF)
• implémenter des règles métiers complexes
• effectuer des dénormalisations contrôlés (avec mesures effetives d'un gain pour pouvoir le justifier)

Ton cas se rapproche du 3ème cas. Mais j'ai quelques doutes sur l'aspect "gain". Tu rencontres actuellement des soucis de performances avec des vues de quelques millions d'enregistrements. Il serait intéressant de voir la table d'origine et la vue pour le comprendre, car quelques millions, ce n'est rien pour un bon SGBD (ce qu'est SQL Server) en terme de volumétrie.

De plus, un trigger,

• ça se désactive. Alors qu'une vue est toujours une représentation réelle des données.
• c'est compliqué à écrire (un trigger est ensembliste ! Souvent ce point est oublié...)
• c'est le type d'objet le plus couteux dans une base de données d'un point de vue des performances (il faut donc être sûr qu'il n'y a que peu d'écriture par rapport aux lectures)
• d'un point de vue maintenance, je trouve pour ma part que c'est une plaie, car un trigger est un objet très "discret" par rapport à une table ou une vue, ou une procédure stockée et qu'on l'oublie bien vite !

Pour ma part, je n'emploi le trigger qu'en dernier recours. J'essai toujours de favoriser des vues ou des procédures stockées avant de les utiliser...

[olibara]

Merci François

Que tu le veuilles ou non, dans ton cas, tu as bien de la duplication de données. Pourquoi on insiste tant de dessus ? Parce que très souvent des problèmes d'exploitation proviennent de cette duplication.

Mais non !
Un enregistrement ne sera jamais dans les deux tables
La table log ne sert qu' a enregistrer les updates consecutifs sur une meme date (cas d'exception) et dont seul le dernier doit se trouver dans la table Prod Si un seul enregistrement est fait pour un compteur sur une date, il ne sera pas dans les Log
... D'ou effectivement le coté délicat de la logique du Trigger et je convient qu'une procédure stockée pourrait aussi faire avantageusement l'affaire mais nécessite des modification plus lourde du coté serveur d'application

ça se désactive. Alors qu'une vue est toujours une représentation réelle des données.

Ben oui et un Truncate c'est aussi parfois possible !
C'est pas parce que c'est possible qu'on va betement jouer avec !

c'est compliqué à écrire (un trigger est ensembliste ! Souvent ce point est oublié...)

Ben oui .... écrire une application c'est pas non plus faire sa liste de course (parfois compliqué aussi)

c'est le type d'objet le plus couteux dans une base de données d'un point de vue des performances (il faut donc être sûr qu'il n'y a que peu d'écriture par rapport aux lectures)

D'accord avec toi mais dans mon cas je n'attends que quelque centaines d'insert par jours mais beaucoup de reporting

d'un point de vue maintenance, je trouve pour ma part que c'est une plaie, car un trigger est un objet très "discret" par rapport à une table ou une vue, ou une procédure stockée et qu'on l'oublie bien vite !

Sur ce point là je suis tout a fait d'accord mais ca a ses avantages et ses inconvénients

[François DORIN]

Mais non !
Un enregistrement ne sera jamais dans les deux tables

Désolé, ma boule de cristal est en panne ! Comprends bien qu'à moins d'être dit explicitement, c'est un élément que nous ne pouvons pas deviner. Et nous sommes deux à avoir interprété ton besoin comme :

• une table avec juste le dernier enregistrement
• une table historique avec l'ensemble des enregistrements.

La table log ne sert qu' a enregistrer les updates consecutifs sur une meme date (cas d'exception) et dont seul le dernier doit se trouver dans la table Prod Si un seul enregistrement est fait pour un compteur sur une date, il ne sera pas dans les Log
... D'ou effectivement le coté délicat de la logique du Trigger et je convient qu'une procédure stockée pourrait aussi faire avantageusement l'affaire mais nécessite des modification plus lourde du coté serveur d'application

Du coup, ta table historique n'en ai pas vraiment une. Un historique permet de suivre... l'historique (merci M. LAPALISSE). Or ici, avec juste la table de log telle que tu la conçois, il ne sera pas possible d'avoir cette information.

Cela peut sembler un détail sémantique, mais d'un point de vue maintenance, cela à son importance !!! Imagine quelqu'un qui passe derrière toi ? Le temps qu'il comprenne comment est organisé l'information...

Mais du coup, question : au moment où tu insères une donnée, es-tu sûr de sa destination finale ? Ce que je veux dire, c'est qu'au moment où tu insères un enregistrement, es-tu sûr d'avoir les enregistrements de la la journée ? Car sinon un enregistrement qui est le dernier de la journée à un instant t peut ne plus l'être à l'instant t+1h. Et là, il faut que tu migres un enregistrement en plus d'en insérer un...

Pour ma part, j'utiliserais non plus 1 vue, mais 2 vues :

• une table qui contient TOUS les enregistrements ;
• une vue qui ne sélectionne que le dernier enregistrement pour chaque jour
• une vue qui sélectionne tous les enregistrements sauf le dernier pour chaque jour

Mais bon, là c'est un avis personnel...

D'accord avec toi mais dans mon cas je n'attends que quelque centaines d'insert par jours mais beaucoup de reporting

Dans ce cas, pas de véritable contre indication d'un point de vue performance quant à l'utilisation de trigger.

[StringBuilder]

Mais du coup, question : au moment où tu insères une donnée, es-tu sûr de sa destination finale ? Ce que je veux dire, c'est qu'au moment où tu insères un enregistrement, es-tu sûr d'avoir les enregistrements de la la journée ? Car sinon un enregistrement qui est le dernier de la journée à un instant t peut ne plus l'être à l'instant t+1h. Et là, il faut que tu migres un enregistrement en plus d'en insérer un...

En fait, ce qu'il veut, c'est un trigger qui fait ça :

INSTEAD OF INSERT
-> Si pas de ligne du jour, alimente la table de relevé
-> Si déjà une ligne, alors recopie de la ligne dans le log, puis mise à jour de la ligne existante

Sauf que :
- Que faire en cas d'UPDATE ? La même chose ?
- Que faire en cas de DELETE ? On reprend la dernière ligne de log ? On historise la ligne supprimée ? On purge aussi l'historique ?
- Peut-on mettre à jour la table de log ? Pour faire quelles opérations ?

[SQLpro]

Le plus simple et le plus performant serait d'utiliser une table temporelle (à ne pas confondre avec une table temporaire) qui permet d'historiser automatiquement les données.
Lisez la présentation que j'ai fait cet hiver pour Microsoft France :
https://blog.developpez.com/sqlpro/p...r-presentation

A +

[olibara]

Bonjour à tous

@Stringbuilder

C'est tout a fait ça
Et l'update doit s'apparenter a un Insert dans le cas présent
Il n'y aura jamais de Delete (Par design de l'application )

@SqlPro
Je vais jeter un Oeil sur les tables temporelles (c'est en 2016 si je ne me trompe)
Mais ici je suis sur une DB Azure donc 2016 d'office à mon avis

[olibara]

Bonjour SQL Pro

J'ai parcouru en vitesse ta présentation des tables Temporelles
Et si j'ai bien compris c'est parfait par exemple si l'on veut historiser l'evolution d'un Catalogue de prix
On garde d'un coté la situation actuelle
Et de l'autre l'évolution Historique

Mais dans mon cas et comme l'a bien exprimé StringBuilder c'est un peu l'inverse
La table principale contient l'Historique par jour et par compteur
Si par Exception plusieurs mesures sont faite le même jour pour le même compteur seule la dernière mesure doit être connue dans la table principale mais dans ce cas je souhaite logguer ces événements dans une table de log

[escartefigue]

Je souscris à l'ensemble des arguments développés par François Dorin, et j'y ajoute que comme votre table contient plusieurs colonnes varchar, les mises à jour risquent de provoquer des déplacements physiques des lignes dans les pages de données, ce qui est très couteux en performances.
Je ne vois décidément aucun argument qui plaide en faveur d'un trigger.

[StringBuilder]

Voici un exemple d'usine à gaz possible :

Code sql :

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
 
drop table historique;
drop table releve;
drop table compteur;
go
 
create table compteur
(
	id int not null primary key identity,
	nom varchar(30) not null
);
 
create table releve
(
	compteur_id int not null references compteur(id),
	instant datetime2 not null,
	valeur float not null,
	primary key (compteur_id, instant)
);
 
create table historique
(
	compteur_id int not null references compteur(id),
	instant datetime2 not null,
	valeur float not null
);
go
 
create index idx_historique on historique (compteur_id, instant);
go
 
create trigger trg_releve_histo
on releve
instead of insert, update
as
begin
	-- On insère toutes les lignes qui existent déjà dans la table d'historique
	insert into historique (compteur_id, instant, valeur)
	-- Les lignes mises à jour par un UPDATE
	select d.compteur_id, d.instant, d.valeur
	from deleted d
	union all
	-- Les lignes insérée qui existent déjà
	select r.compteur_id, r.instant, r.valeur
	from inserted i
	inner join releve r on r.compteur_id = i.compteur_id
	where (select count(*) from deleted) = 0;
 
	-- On supprime toutes les lignes qui existent déjà
	delete releve
	where compteur_id in (select i.compteur_id from inserted i inner join releve r on r.compteur_id = i.compteur_id);
 
	-- On insère les nouvelles valeurs
	insert into releve (compteur_id, instant, valeur)
	select i.compteur_id, i.instant, i.valeur
	from inserted i;
end;
go
 
insert into compteur (nom) values ('Compteur 1'), ('Compteur 2');
insert into releve (compteur_id, instant, valeur) values (1, GetDate(), 1.2), (2, GetDate(), 1.5);
insert into releve (compteur_id, instant, valeur) values (1, GetDate(), 1.4);
insert into releve (compteur_id, instant, valeur) values (1, GetDate(), 1.1);
 
select * from releve;
select * from historique;

[agahmichel]

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
CREATE TABLE [dbo].[IndexHistory](
	[pkId] [int] NOT NULL,
	[CounterId] [int] NOT NULL,
	[IndexValue] [decimal](18, 4) NOT NULL,
	[Date] [datetime] NOT NULL,
	[OperatorId] [int] NULL,
	[pictureUrl] [varchar](200) NULL,
	[Remark] [varchar](200) NULL,
	[isDisabled] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
	[pkId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
 
 
GO
 
CREATE TABLE [dbo].[IndexHistoryLog](
	[pkId] [int] NOT NULL,
	[CounterId] [int] NOT NULL,
	[IndexValue] [decimal](18, 4) NOT NULL,
	[Date] [datetime] NOT NULL,
	[OperatorId] [int] NULL,
	[pictureUrl] [varchar](200) NULL,
	[Remark] [varchar](200) NULL,
	[isDisabled] [bit] NULL,
)
 
GO
 
IF OBJECT_ID(N'IO_Trig_INS_IndexHistory',N'TR') IS NOT NULL
	DROP TRIGGER IO_Trig_INS_IndexHistory;
GO
CREATE TRIGGER IO_Trig_INS_IndexHistory ON IndexHistory
INSTEAD OF INSERT
AS
BEGIN
DECLARE @Date Datetime,
		@CounterId int
		SELECT	@CounterId = inserted.CounterId,
				@Date = inserted.Date
		FROM inserted
 
 
IF (EXISTS (SELECT P.pkid
      FROM IndexHistory P
      WHERE CONVERT(Date,P.Date) = CONVERT(Date,@Date) and P.CounterId=@CounterId))
   BEGIN
	   Insert into IndexHistoryLog
		SELECT * From IndexHistory WHERE CONVERT(Date,IndexHistory.Date) = CONVERT(Date,@Date) and IndexHistory.CounterId=@CounterId
 
	   UPDATE P
	   SET	CounterId=I.CounterId,
			IndexValue=I.IndexValue,
			date=getdate(),
			OperatorId=I.OperatorId,
			pictureUrl=I.pictureUrl,
			Remark=I.Remark,
			isDisabled=I.isDisabled
	   FROM IndexHistory P
	   INNER JOIN Inserted I on CONVERT(Date,P.Date) = CONVERT(Date,I.date) and P.CounterId=I.CounterId
	END
ELSE
	Insert into IndexHistory
	SELECT * From inserted
END
GO
 
 
Insert into IndexHistory Values (1,125,15263,'2017-05-22 23:11:38.240',123,'http://','N/A',0)
Insert into IndexHistory Values (2,126,11235,'2017-05-22 23:11:39.240',123,'http://','N/A',0)
Insert into IndexHistory Values (3,125,15852,'2017-05-22 23:11:40.240',123,'http://','New entry',1)
Insert into IndexHistory Values (4,127,17562,'2017-05-22 23:11:41.240',123,'http://','N/A',0)
Insert into IndexHistory Values (5,127,17562,'2017-05-22 23:11:42.240',123,'http://','New entry',0)
 
Select * from IndexHistory
Select * from IndexHistoryLog

[olibara]

Merci Stringbuilder

Effectivement ta solution fait un peu "Usine a Gaz"
Je pense pouvoir trouver une méthode plus Simple
Je m'y met et vous ferai part de mes résultats

[StringBuilder]

Attention, ta solution n'est pas ensembliste : si tu insères ou met à jour plusieurs lignes d'un coup, proutch

[SQLpro]

Bonjour SQL Pro

J'ai parcouru en vitesse ta présentation des tables Temporelles
Et si j'ai bien compris c'est parfait par exemple si l'on veut historiser l'evolution d'un Catalogue de prix
On garde d'un coté la situation actuelle
Et de l'autre l'évolution Historique

Mais dans mon cas et comme l'a bien exprimé StringBuilder c'est un peu l'inverse
La table principale contient l'Historique par jour et par compteur
Si par Exception plusieurs mesures sont faite le même jour pour le même compteur seule la dernière mesure doit être connue dans la table principale mais dans ce cas je souhaite logguer ces événements dans une table de log

Il suffit tout simplement d'inverser votre logique à l'aide d'un MERGE !

A +