Discussion :

[guidav]

Bonjour

J'ai une table d'historisation de données de ce type :
PRIMARY : clé primaire autoincrement
DATE_ARCHIVE : date d'archivage (indexée)
[champs de données]

Est-il possible avec un trigger de noter dans une table les lignes ajoutées, supprimées ou modifiées, dont la date d'archivage est inférieure à (par exemple) max(DATE_ARCHIVE) - 7 jours ?
Je n'ai pas vu de telle clause dans le tuto de sqlpro.

Merci d'avance

[elsuket]

Bonjour,

Votre question n'est pas claire. Cherchez-vous :
- à n'archiver au plus que 7 jours de modifications ?
- à conserver toutes les modifications mais à ne montrer dans votre application que les modifications des 7 derniers jours ?

Prenons pour exemple les tables suivantes :

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
CREATE TABLE Source
(
	IDSource INT IDENTITY NOT NULL CONSTRAINT PK_Source PRIMARY KEY,
	nomSource VARCHAR(10) NOT NULL CONSTRAINT UQ_Source_nomSource UNIQUE
)
GO
 
CREATE TABLE TypeArchive
(
	IDTypeArchive TINYINT IDENTITY NOT NULL CONSTRAINT PK_TypeArchive PRIMARY KEY,
	nomTypeArchive VARCHAR(12) NOT NULL CONSTRAINT UQ_TypeArchive_nomTypeArchive UNIQUE
)
GO
 
CREATE TABLE SourceArchive
(
	IDSourceArchive INT IDENTITY NOT NULL CONSTRAINT PK_SourceArchive PRIMARY KEY,
	IDSource INT NOT NULL,
	nomSource VARCHAR(10) NOT NULL,
	dateArchivage DATETIME NOT NULL CONSTRAINT DF_SourceArchive_dateArchivage DEFAULT (GETDATE()),
	IDTypeArchive TINYINT NOT NULL CONSTRAINT FK_SourceArchive_IDTypeArchive FOREIGN KEY (IDTypeArchive) REFERENCES dbo.TypeArchive
)
GO
 
INSERT INTO dbo.TypeArchive (nomTypeArchive) VALUES ('INSERTION')
INSERT INTO dbo.TypeArchive (nomTypeArchive) VALUES ('SUPPRESSION')
INSERT INTO dbo.TypeArchive (nomTypeArchive) VALUES ('MODIFICATION')
GO

Dans le premier cas, nous aurons les trois déclencheurs suivants :

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
CREATE TRIGGER TR_A_I_Source
	ON dbo.Source
	AFTER INSERT
AS
BEGIN
	DECLARE @dateNow DATETIME SET @dateNow = GETDATE() - 7
 
	INSERT INTO dbo.SourceArchive
	(
		IDSource,
		nomSource,
		IDTypeArchive
	)
	SELECT IDSource,
			nomSource
			1 -- IDTypeArchive : INSERTION
	FROM INSERTED
 
	DELETE FROM dbo.SourceArchive
	WHERE dateArchivage < @dateNow
END
GO
 
CREATE TRIGGER TR_A_D_Source
	ON dbo.Source
	AFTER INSERT
AS
BEGIN
	DECLARE @dateNow DATETIME SET @dateNow = GETDATE() - 7
 
	INSERT INTO dbo.SourceArchive
	(
		IDSource,
		nomSource,
		IDTypeArchive
	)
	SELECT IDSource,
			nomSource
			2 -- IDTypeArchive : SUPPRESSION
	FROM DELETED
 
	DELETE FROM dbo.SourceArchive
	WHERE dateArchivage < @dateNow
END
GO
 
CREATE TRIGGER TR_A_D_Source
	ON dbo.Source
	AFTER UPDATE
AS
BEGIN
	DECLARE @dateNow DATETIME SET @dateNow = GETDATE() - 7
 
	INSERT INTO dbo.SourceArchive
	(
		IDSource,
		nomSource,
		IDTypeArchive
	)
	SELECT IDSource,
			nomSource
			3 -- IDTypeArchive : MODIFICATION
	FROM INSERTED
 
	DELETE FROM dbo.SourceArchive
	WHERE dateArchivage < @dateNow
END
GO

Dans le second cas il vous faut écrire une procédure stockée :

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
CREATE PROCEDURE PsGetArchives
AS
BEGIN
	DECLARE @date DATETIME
	SELECT @date = CAST(CONVERT(CHAR(8), GETDATE() - 7, 112) AS DATETIME)
 
	SELECT SA.IDSource,
			SA.nomSource,
			SA.dateArchivage,
			TA.nomTypeArchive
	FROM dbo.SourceArchive AS SA
	JOIN dbo.TypeArchive AS TA
		ON SA.IDTypeArchive = TA.IDTypeArchive
	WHERE SA.dateArchive > @date
END

@++

[guidav]

En effet, je n'ai pas été assez clair. J'ai une application qui bascule des tables d'archivage MSSQL dans une base MySQL. Toutes les nuits, elle efface puis envoie les 7 derniers jours. Tous les week-ends, elle bascule toute la table sans notion de date.
Le souci est que la bascule du week-end prend de plus en plus de temps, alors que les données antérieures à 7 jours n'ont souvent pas bougé.
L'idée est donc de détecter si des données de plus de 7 jours ont été modifiées avant de basculer les tables entières, et sinon de n'envoyer que les 7 derniers jours comme d'habitude.

Avec un trigger, est-ce possible de savoir si dans une table, ont été modifiées des données dont le champ indiquant la date d'archive a plus de 7 jours ?

Merci beaucoup

[elsuket]

Donc à priori vous n'avez pas besoin de trigger, il suffit d'écrire une procédure stockée pour ce faire et de la fairé exécuter à l'heure que vous voulez par un job de l'Agent SQL Server.

L'idée est donc de détecter si des données de plus de 7 jours ont été modifiées avant de basculer les tables entières, et sinon de n'envoyer que les 7 derniers jours comme d'habitude.

Si vous utilisez SQL Server 2008, vous pouvez utiliser l'ordre MERGE pour ce faire. Sinon UPDATE + INSERT + DELETE.

Dites-nous quelle version vous utilisez, et nous pourrons vous aider plus précisément

@++