Trigger pour accepter ou orienter un Insert

**elsuket** · 22/05/2017, 23h56

Bonsoir,

Pour montrer la complexité d'une implémentation avec des triggers, je me suis amusé à implémenter une table partitionnée sur l'état d'un enregistrement pour un compteur et un jour donné.
La colonne is_last est donc à 1 si c'est la dernière écriture du jour pour le compteur considéré, et à zéro à l'inverse.
Ainsi, on n'a qu'une seule table, et on peut avoir une vue pour récupérer la valeur courante des compteurs, et une autre pour voir tout l'historique des valeurs des compteurs.

Voici donc la table des compteurs, très banale :

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE meter
(
	meter_id smallint IDENTITY NOT NULL
		CONSTRAINT PK_meter PRIMARY KEY
	, meter_name varchar(64) NOT NULL
		CONSTRAINT UQ_meter__meter_name UNIQUE
	, is_active bit NOT NULL
		CONSTRAINT DF_meter__is_active DEFAULT 1
)
GO

Nous créons maintenant les objets de partitionnement : on souhaite donc n'avoir que deux partitions; on stockera donc les lignes qui sont à 1 à droite de la césure, et les lignes à 0 à gauche :

Code :

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

1
2
3
4
5
6
7
CREATE PARTITION FUNCTION pf_meter_history(bit)
AS RANGE RIGHT FOR VALUES (1)
GO
 
CREATE PARTITION SCHEME ps_meter_history
AS PARTITION pf_meter_history ALL TO ([PRIMARY])
GO

La table voit donc sa définition être :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE meter_log
(
	meter_log_id int IDENTITY(-2147483648, 1) NOT NULL
	, meter_id smallint NOT NULL
		CONSTRAINT FK_meter_log__meter_id FOREIGN KEY(meter_id) REFERENCES dbo.meter
	, log_date_time datetime2(0) NOT NULL
	, meter_value int NOT NULL
		CONSTRAINT CHK_meter_log__meter_value CHECK (meter_value >= 0)
	, is_last bit NOT NULL
		CONSTRAINT DF_meter_log__is_last DEFAULT (1)
	, CONSTRAINT PK_meter_log_ PRIMARY KEY (is_last, meter_log_id)
) ON ps_meter_history(is_last)
GO

Il faut que nous nous assurions que pour un compteur et une journée, on n'a qu'une seule ligne qui est la "dernière".
On doit donc ajouter une contrainte de domaine supportée par une fonction scalaire.
En effet, on ne peut pas poser de contrainte d'unicité portant sur le tuple {meter_id, log_date_time}, puisque pour tout compteur, on peut avoir plusieurs relevés dans la même journée.

On a donc la fonction scalaire suivante :

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
CREATE FUNCTION fs_check_meter_log_last_count
	(
		@_meter_id smallint
		, @_log_date_time datetime2(0)
	)
RETURNS bit
AS
BEGIN
	DECLARE @ok bit = 0
 
	SELECT	@ok = CASE COUNT(*)
			WHEN 0 THEN 1
			WHEN 1 THEN 1
			ELSE 0
		END
	FROM	dbo.meter_log
	WHERE	meter_id = @_meter_id
	AND	log_date_time = @_log_date_time
	AND	is_last = 1
 
	RETURN @ok
END
GO

Et la contrainte correspondante est donc :

Code :

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

1
2
3
ALTER TABLE dbo.meter_log
ADD CONSTRAINT CHK_meter_log__meter_id__is_last CHECK(dbo.fs_check_meter_log_last_count(meter_id, log_date_time) = 1)
GO

Là où ça devient rigolo, c'est lorsqu'on passe à l'écriture des triggers : voici pour les INSERT :

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
CREATE TRIGGER TR_IO_I_meter_log
	ON dbo.meter_log
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON
 
	BEGIN TRY
		-- Copying the previous rows, and marking them as history, if they exist
		INSERT INTO dbo.meter_log
		(
			meter_id
			, meter_value
			, log_date_time
			, is_last
		)
		SELECT		L.meter_id
				, L.meter_value
				, L.log_date_time
				, 0
		FROM		INSERTED AS D
		INNER JOIN	dbo.meter_log AS L
					ON D.meter_id = L.meter_id
					AND CAST(D.log_date_time AS date) = CAST(L.log_date_time AS date)
		WHERE		L.is_last = 1
 
		-- Adding new meters
		INSERT INTO dbo.meter_log
		(
			meter_id
			, meter_value
			, log_date_time
		)
		SELECT		I.meter_id
				, I.meter_value
				, I.log_date_time
		FROM		INSERTED AS I
		LEFT JOIN	dbo.meter_log AS L
					ON I.meter_id = L.meter_id
					AND CAST(I.log_date_time AS date) = CAST(L.log_date_time AS date)
		WHERE		L.meter_id IS NULL
 
		-- Updating the "last" row for existing meters, so that it reflects the newest values
		UPDATE		dbo.meter_log
		SET		meter_value = I.meter_value
				, log_date_time = SYSDATETIME()
		FROM		dbo.meter_log AS L
		INNER JOIN	INSERTED AS I
					ON L.meter_id = I.meter_id
					AND CAST(L.log_date_time AS date) = CAST(I.log_date_time AS date)
		WHERE		L.is_last = 1
	END TRY
	BEGIN CATCH
		DECLARE @err_msg nvarchar(4000) = 'Line ' + CAST(ERROR_LINE() AS varchar(10))
			+ ' - Error Number : ' + CAST(ERROR_NUMBER() AS varchar(10))
			+ ' - ' + ERROR_MESSAGE()
			, @err_svt int = ERROR_SEVERITY()
			, @err_stt int = ERROR_STATE()
 
		IF XACT_STATE() <> 0
		BEGIN
			ROLLBACK TRANSACTION;
		END
 
		RAISERROR(@err_msg, @err_svt, @err_stt);
		RETURN;
	END CATCH
END
GO

Et pour les UPDATE :

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
CREATE TRIGGER TR_IO_U_meter_log
	ON dbo.meter_log
INSTEAD OF UPDATE
AS
BEGIN
	SET NOCOUNT ON
 
	BEGIN TRY
		-- Copying the previous rows, and marking them as history, if they exist
		INSERT INTO dbo.meter_log
		(
			meter_id
			, meter_value
			, log_date_time
			, is_last
		)
		SELECT		L.meter_id
				, L.meter_value
				, L.log_date_time
				, 0
		FROM		DELETED AS D
		INNER JOIN	dbo.meter_log AS L
					ON D.meter_id = L.meter_id
					AND CAST(D.log_date_time AS date) = CAST(L.log_date_time AS date)
		WHERE		L.is_last = 1
 
		-- Updating the "last" row, so that it reflects the newest values
		UPDATE		dbo.meter_log
		SET		meter_value = I.meter_value
				, log_date_time = SYSDATETIME()
		FROM		dbo.meter_log AS L
		INNER JOIN	INSERTED AS I
					ON L.meter_id = I.meter_id
					AND CAST(L.log_date_time AS date) = CAST(I.log_date_time AS date)
		WHERE		L.is_last = 1
	END TRY
	BEGIN CATCH
		DECLARE @err_msg nvarchar(4000) = 'Line ' + CAST(ERROR_LINE() AS varchar(10))
			+ ' - Error Number : ' + CAST(ERROR_NUMBER() AS varchar(10))
			+ ' - ' + ERROR_MESSAGE()
			, @err_svt int = ERROR_SEVERITY()
			, @err_stt int = ERROR_STATE()
 
		IF XACT_STATE() <> 0
		BEGIN
			ROLLBACK TRANSACTION;
		END
 
		RAISERROR(@err_msg, @err_svt, @err_stt);
		RETURN;
	END CATCH
END
GO

Si l'on devait choisir cette solution (i.e. on n'est pas sous SQL Server 2014 ou suivant, et on n'est pas dans une édition Enterprise, donc on ne peut pas utiliser la fonctionnalité Change Data Capture), on veillera à prévenir les DELETE par une gestion adéquate des droits sur la table dbo.meter_log (seuls l'INSERT et l'UPDATE seront autorisés à l'utilisateur applicatif).

On peuple la table des compteurs très simplement :

Code :

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

1
2
3
INSERT INTO dbo.meter(meter_name)
VALUES ('compteur 1'), ('compteur 2'), ('compteur 3')
GO

La vue qui permet de retrouver les dernières écritures pour un compteur est la suivante :

Code :

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

1
2
3
4
5
6
7
8
9
CREATE VIEW v_meter_log_current
AS
SELECT		M.meter_name
		, L.log_date_time
		, L.meter_value
FROM		dbo.meter_log AS L
INNER JOIN	dbo.meter AS M
			ON L.meter_id = M.meter_id
WHERE		L.is_last = 1

Et les tests suivants permettent de valider le bon fonctionnement des triggers :

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
INSERT INTO dbo.meter_log (meter_id, meter_value, log_date_time) VALUES (1, 5, SYSDATETIME())
INSERT INTO dbo.meter_log (meter_id, meter_value, log_date_time) VALUES (1, 5, SYSDATETIME())
INSERT INTO dbo.meter_log (meter_id, meter_value, log_date_time) VALUES (1, 7, SYSDATETIME())
INSERT INTO dbo.meter_log (meter_id, meter_value, log_date_time) VALUES (1, 8, SYSDATETIME()), (2, 3, SYSDATETIME())
INSERT INTO dbo.meter_log (meter_id, meter_value, log_date_time) VALUES (1, 9, SYSDATETIME()), (2, 12, SYSDATETIME())
 
SELECT	*
FROM	dbo.meter_log
 
UPDATE	dbo.meter_log
SET	meter_value = 2 * meter_value
WHERE	is_last = 1
 
SELECT	*
FROM	dbo.meter_log

Il manque deux choses :

L'index sur la colonne log_date_time
Dans le code des triggers, un filtre sur les compteurs actifs

Donc de toute évidence :

La maintenance/evolutivité de cette solution est nulle par rapport aux tables "temporalisées" : il suffit de considérer l'ajout d'une colonne à la table pour que la différence saute aux yeux
La durée des transactions sera nécessairement plus élevée qu'avec une table temporalisée du fait des triggers : l’implémentation de la "temporalisation" d'une table est native au moteur, et donc forcément plus rapide à l'exécution.

@++ ;)

**olibara** · 23/05/2017, 06h15

Merci Elsuket

Belle demonstration !
Et bravo pour ton effort
Et merci à tous

Je pense que je vais m'orienter vers une table temporalisée

**SQLpro** · 23/05/2017, 09h33

Envoyé par olibara

Je pense que je vais m'orienter vers une table temporalisée

Il serait temps !!!!

En plus les données d'historisation des tables temporelles sont naturellement compressées !

A +

**aieeeuuuuu** · 23/05/2017, 14h17

Bonjour, et bravo a ElSuket !

Envoyé par elsuket

Il faut que nous nous assurions que pour un compteur et une journée, on n'a qu'une seule ligne qui est la "dernière".
On doit donc ajouter une contrainte de domaine supportée par une fonction scalaire.
En effet, on ne peut pas poser de contrainte d'unicité portant sur le tuple {meter_id, log_date_time}, puisque pour tout compteur, on peut avoir plusieurs relevés dans la même journée.

Sur ce point, j'ai une remarque :
Il me semble que la contrainte telle que tu l'a posée ne fonctionne pas, car tu ne supprimes pas la composante horaire dans la fonction scalaire.
Et sans les trigger pour faire le job, il devient possible d'insérer plusieurs ligne is_last = 1 pour un compteur pour une journée données (avec des heures différentes).

On doit pouvoir remplacer tout ça en ajoutant une colonne calculée :

Code :

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

1
2
ALTER TABLE dbo.meter_log
	ADD Jour AS (CAST(log_date_time AS DATE))

Et en posant un index unique filtré :

Code :

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

1
2
3
CREATE UNIQUE INDEX UIX_METER_LOG_METER_DATE 
	ON dbo.meter_log(meter_id, Jour) 
	WHERE is_last = 1

En incluant au besoin les autres colonnes nécessaires, on peut faire un index couvrant qui devrait permettre de bonnes performances.

**elsuket** · 24/05/2017, 14h07

Il me semble que la contrainte telle que tu l'a posée ne fonctionne pas, car tu ne supprimes pas la composante horaire dans la fonction scalaire.

Exact, bien vu et merci !

Je me rappelle avoir pensé à l'index unique filtré, mais je ne l'ai pas ajouté ... en ne pensant pas qu'il fallait que j'élimine l'heure !

@++

Trigger pour accepter ou orienter un Insert

Développement SQL Server

Discussions similaires

Partager

Partager