Problème de trigger [Résolu]

[Kropernic]

Je rencontre quelques difficultés à mettre au point les triggers sur mes tables.

Je rentre dans le vif du sujet avec la déclaration d'une table (qui servira d'exemple)

Code :

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
USE [INNO]
GO
/****** Object:  Table [EVOLUTION].[TL_APPRECIATION_APRL]    Script Date: 11/14/2011 10:56:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [EVOLUTION].[TL_APPRECIATION_APRL](
	[APRL_APPRECIATION] [int] NOT NULL,
	[APRL_LANGUE] [smallint] NOT NULL,
	[APRL_VERSION] [int] NOT NULL CONSTRAINT [DF_TL_APPRECIATION_APRL_APRL_VERSION]  DEFAULT ((0)),
	[APRL_LIB] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
	[APRL_UPDATE] [datetime] NULL,
	[APRL_USERNAME] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
	[APRL_DEL] [bit] NOT NULL CONSTRAINT [DF_TL_APPRECIATION_APRL_APRL_DEL]  DEFAULT ((0)),
 CONSTRAINT [PK_TL_APPRECIATION_APRL] PRIMARY KEY CLUSTERED 
(
	[APRL_APPRECIATION] ASC,
	[APRL_LANGUE] ASC,
	[APRL_VERSION] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
USE [INNO]
GO
ALTER TABLE [EVOLUTION].[TL_APPRECIATION_APRL]  WITH CHECK ADD  CONSTRAINT [FK_TL_APPRECIATION_APRL_langue] FOREIGN KEY([APRL_LANGUE])
REFERENCES [dbo].[langue] ([langue])
GO
ALTER TABLE [EVOLUTION].[TL_APPRECIATION_APRL]  WITH CHECK ADD  CONSTRAINT [FK_TL_APPRECIATION_APRL_T_APPRECIATION_APR] FOREIGN KEY([APRL_APPRECIATION])
REFERENCES [EVOLUTION].[T_APPRECIATION_APR] ([APR_ID])
GO
ALTER TABLE [EVOLUTION].[TL_APPRECIATION_APRL]  WITH CHECK ADD  CONSTRAINT [FK_TL_APPRECIATION_APRL_Users] FOREIGN KEY([APRL_USERNAME])
REFERENCES [UserManagement].[Users] ([username])

Voilà donc la table sur laquelle je veux faire mes triggers.

Et voici les triggers que j'ai créé :

Celui-ci sert à mettre la date et l'heure dans la colonne APRL_UPDATE et calcul aussi le bon numéro de version pour le record.

Code :

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
USE [INNO]
GO
/****** Object:  Trigger [TRG_TIMESTAMP_APRL]    Script Date: 11/14/2011 11:02:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
 
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [TRG_TIMESTAMP_APRL]
   ON  [EVOLUTION].[TL_APPRECIATION_APRL]
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for trigger here
	DECLARE @DATETIME AS DATETIME;
	DECLARE @APR AS INT;
	DECLARE @LNG AS INT;
	DECLARE @VERSION AS INT;
 
	SET @DATETIME = GETDATE();
	SET @APR = (SELECT APRL_APPRECIATION FROM INSERTED);
	SET @LNG = (SELECT APRL_LANGUE FROM INSERTED);
	SET @VERSION = (SELECT ISNULL(MAX(APRL_VERSION),0) FROM EVOLUTION.TL_APPRECIATION_APRL WHERE APRL_APPRECIATION = @APR AND APRL_LANGUE = @LNG)+1;
 
	UPDATE EVOLUTION.TL_APPRECIATION_APRL SET APRL_UPDATE = @DATETIME, APRL_VERSION = @VERSION WHERE APRL_APPRECIATION = @APR AND APRL_LANGUE = @LNG;
END

Ce trigger vérifie si on modifie le champ APRL_LIB. Si oui, on remplace l'update par un insert car c'est une nouvelle version (le champ APRL_LIB ne peut jamais être modifié). Si non, on fait l'update normalement.

Code :

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
USE [INNO]
GO
/****** Object:  Trigger [TRG_UPDATE_APRL]    Script Date: 11/14/2011 11:03:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [TRG_UPDATE_APRL]
   ON  [EVOLUTION].[TL_APPRECIATION_APRL]
   INSTEAD OF UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for trigger here
	DECLARE @USERNAME	AS VARCHAR(50);
	DECLARE @DEL		AS BIT;
	DECLARE @APR		AS INT;
	DECLARE	@LANGUE		AS INT;
	DECLARE @VERSION	AS INT;
	DECLARE @LIB		AS VARCHAR(255);
 
	SET @USERNAME	= (SELECT APRL_USERNAME FROM INSERTED);
	SET @DEL		= (SELECT APRL_DEL FROM INSERTED);
	SET @APR		= (SELECT APRL_APPRECIATION FROM INSERTED);
	SET @LANGUE		= (SELECT APRL_LANGUE FROM INSERTED);
	SET @VERSION	= (SELECT APRL_VERSION FROM INSERTED);
	SET @LIB		= (SELECT APRL_LIB FROM INSERTED);
 
	IF NOT UPDATE(APRL_LIB)
	BEGIN
		UPDATE	EVOLUTION.TL_APPRECIATION_APRL 
			SET APRL_USERNAME = @USERNAME, APRL_DEL = @DEL
		WHERE	APRL_APPRECIATION = @APR
			AND	APRL_LANGUE = @LANGUE
			AND APRL_VERSION = @VERSION;
	END
	ELSE
	BEGIN
		SET @VERSION = @VERSION + 1;
		INSERT INTO EVOLUTION.TL_APPRECIATION_APRL (APRL_APPRECIATION, APRL_LANGUE, APRL_VERSION, APRL_LIB, APRL_USERNAME, APRL_DEL)
		VALUES(@APR, @LANGUE, @VERSION, @LIB, @USERNAME, @DEL);
	END
END

Tout se passe très bien à l'insertion mais à l'update, j'obtiens un message d'erreur qui me dit qu'il ne peut pas faire l'insertion pour cause de duplicate key.

Même si je comprends bien de quelle erreur il s'agit, je ne vois pas où se trouve ma faute.

Quelqu'un pourrait-il y jeter un regard plus avisé que le mien ?

Merci d'avance.

Griftou.

[SQLpro]

Votre code n'est pas un code ensembliste. Ainsi il ne traitera qu'une seule ligne au hasard.
En principe vous ne devriez avoir aucune variable locale mais uniquement des requêtes.
En sus votre table est mal conçue et votre pseudo auto incrément catastrophique en terme de perf...

A +

[Kropernic]

J'ai du mal à comprendre...

Je pense comprendre le pourquoi du "il ne traitera qu'une ligne au hasard".
Si vous faites référence à l'initialisation des variables avec une requête de selection sur la table inserted, l'application qui utilisera la base de données est prévue pour ne mettre à jour qu'une ligne à la fois. C'est pour cela que je fais un select sans condition.

Pour le reste, j'ai fait avec les moyens du bord (comprendre "les connaissances que je possède). S'il existe de meilleure manière de procéder, je suis ouvert aux suggestions.

Cependant, même si la table est mal conçue et le code catastrophique en terme de performance, cela devrait néanmoins fonctionner non ?

[SQLpro]

Voici un exemple de ce que vous devriez faire :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE SCHEMA EVOLUTION
GO
-- votre table corrigée :
CREATE TABLE EVOLUTION.TL_APPRECIATION_APRL
(   APRL_ID   INT NOT NULL IDENTITY PRIMARY KEY,
	APRL_APPRECIATION int NOT NULL,
	APRL_LANGUE smallint NOT NULL,
	APRL_VERSION int NOT NULL CONSTRAINT DF_TL_APPRECIATION_APRL_APRL_VERSION  DEFAULT ((0)),
	APRL_LIB varchar(255) COLLATE Latin1_General_CI_AS NOT NULL,
	APRL_UPDATE datetime NULL,
	APRL_USERNAME varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
	APRL_DEL bit NOT NULL CONSTRAINT DF_TL_APPRECIATION_APRL_APRL_DEL  DEFAULT ((0)),
)
GO

Code :

1
2
3
4
5
6
7
8
9
10
11
12
-- le trigger ensembliste :
CREATE TRIGGER E_U_APRL
ON  EVOLUTION.TL_APPRECIATION_APRL
INSTEAD OF UPDATE
AS
 
INSERT INTO T
SELECT * 
FROM   EVOLUTION.TL_APPRECIATION_APRL AS T
       INNER JOIN INSERTED AS I
             ON T.APRL_ID = I.APRL_ID 
             AND T.APRL_LIB <> I.APRL_LIB;

Code :

1
2
3
4
-- pour visionner vos données avec un n° de version, rien de tel qu'une vue :
SELECT *, 
       ROW_NUMBER() OVER(PARTITION BY APRL_APPRECIATION, APRL_LANGUE ORDER BY APRL_UPDATE) AS NUM_VERSION
FROM   EVOLUTION.TL_APPRECIATION_APRL

A +

[Kropernic]

Bien qu'il ne soit pas bien long, j'ai du mal à comprendre votre trigger... :-/

J'ai donc ajouté un champ APRL_ID de type int avec une auto-incrémentation.
J'ai modifié le trigger mis en place sur l'update avec le votre et après tests, il modifie la version de toutes les lignes pour y mettre le dernier numéro...

P-e ai-je oublié quelque chose mais je vois pas quoi pour le moment.

EDIT : Ca y est, j'ai compris le trigger Maintenant la suite ^^

EDIT 2 : Je pense avoir trouvé... Je fais les modifs et je donne un feedback

EDIT 3 : Ca fonctionne !!!!!! Encore un tout grand merci à SQLpro

[Kropernic]

Bonjour,

Je me permets de rouvrir ce topic car j'ai une question supplémentaire concernant le trigger instead of update.

Pour rappel, voici le code :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
 
 
 
ALTER TRIGGER [TRG_UPDATE_GRPL]
ON  [EVOLUTION].[TL_GROUPE_GRPL]
INSTEAD OF UPDATE
AS
 
INSERT INTO EVOLUTION.TL_GROUPE_GRPL(GRPL_GROUPE,GRPL_LANGUE,GRPL_LIB,GRPL_USERNAME)
SELECT I.GRPL_GROUPE, I.GRPL_LANGUE, I.GRPL_LIB, I.GRPL_USERNAME
FROM   EVOLUTION.TL_GROUPE_GRPL AS T
       INNER JOIN INSERTED AS I
             ON T.GRPL_ID = I.GRPL_ID 
             AND T.GRPL_LIB <> I.GRPL_LIB;

Il fonctionne très bien et insère bien une nouvelle ligne en cas de modification du libellé à un détail près.

Supposons que l'utilisateur crée un groupe, le nomme "premier groupe" et l'enregistre.

Par après, il se rend compte que "Premier groupe" serait plus présentable. Une modification de ce genre n'est pas prise en compte car apparemment, pour sql serveur, "premier groupe" est équivalent à "Premier groupe".

Comment faire pour rendre la condition se trouvant sur la dernière ligne du trigger case sensitive ? Et juste celle-là. Je sais qu'il est possible de configurer le serveur pour qu'il le soit mais ce n'est pas ce que je veux. Il y a plein d'autres moments où je suis bien content qu'il ne soit pas case sensitive ^^.

J'ai un peu cherché (p-e pas assez, je le concède) et je n'ai pas trouvé de réponse satisfaisante à mon problème.

Est-ce seulement possible ?

Merci d'avance,

Griftou.

[Kropernic]

Problème résolu !

J'ignore si c'est la manière la plus propre de le faire mais j'ai pu, dans la définition de la table, indiquer que cette colonne est case sensitive dans la propriété collation.