la transaction implicite d'un trigger

Version imprimable

29/04/2010, 11h44
brinx

la transaction implicite d'un trigger

Bonjour tout le monde,

Je vous demande SVP, s'il y qlqu'un qui pourra m'aider sur un point trs important, voici le problème  :

J'ai créé un trigger, pour insertion, sur une table (originale) et lorsque j'y insère des données, je fais une historisation sur une autre table (supplémentaire).
Le problème existe dans la façon avec laquelle le moteur SQL Server interprète les triggers, CAD, s'il y en a une erreur d'exécution dans le trigger, SQL Server annule toutes les opérations (l'insertion dans la table originale et l'historisation des données).

Mon besoin c'est que je veux, mm avec erreur d'exécution dans le trigger, garder l'insertion dans la table originale !!

Merci bcp pour votre aide 
29/04/2010, 13h25
elsuket

Bonjour,

Effectivement l'exécution du trigger fait partie de la transaction qui a déclenché son exécution.
Donc cela s'exécute comme une seule instruction, qui s'exécute en "tout ou rien" :

- Soit elle s'exécute sans erreur, et le tout est validé.
- Soit il y a une erreur, et pour replacer la base de données dans un état consistant, le moteur annule le tout.

Citation:

s'il y en a une erreur d'exécution dans le trigger, SQL Server annule toutes les opérations (l'insertion dans la table originale et l'historisation des données).

Mon besoin c'est que je veux, mm avec erreur d'exécution dans le trigger, garder l'insertion dans la table originale !!

Dans ce cas corrigez le problème à l'insertion dans votre table d'historisation.
Celle-ci doit fonctionner ;)

@++ ;)
29/04/2010, 16h42
brinx

Merci elsuket pour votre réponse.

Oui je suis d'accord avec toi que le tout doit marcher, mais si je ne connais pas l'erreur que je vais corriger, CAD l'erreur s'est produit lors de l'exécution de l'historisation des données !! Est ce qu'il n'y pas de moyen pour gérer ce cas.

Merci

Dès SQL Server 2005, vous pouvez gérer les cas d'erreur à l'aide du contrôle TRY...CATCH.
Voici un exemple de test :

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
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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
-- Table journal des erreurs
CREATE TABLE log_erreurs
(
	id_erreur int NOT NULL IDENTITY CONSTRAINT PK_log_erreurs PRIMARY KEY
	, module sysname
	, error_msg nvarchar(2048) NOT NULL
	, date_erreur datetime NOT NULL CONSTRAINT DF_log_erreurs__source_date DEFAULT (GETDATE())
)
GO
 
-- Table dans laquelle vous faites votre INSERT
CREATE TABLE table_source
(
	source_id int NOT NULL IDENTITY CONSTRAINT PK_table_source PRIMARY KEY
	, source_valeur int NOT NULL
	, source_date datetime NOT NULL CONSTRAINT DF_table_source__source_date DEFAULT (GETDATE())
)
GO
 
-- Table dans laquelle vous historisez l'INSERT dans table_source
CREATE TABLE table_historisation
(
	table_historisation_id int NOT NULL CONSTRAINT PK_table_historisation PRIMARY KEY
	, table_historisation_valeur int NOT NULL
	, table_historisation_date datetime NOT NULL
)
GO
 
-- Trigger d'historisation de l'INSERT dans table_source
CREATE TRIGGER TR_AI_table_source
	ON dbo.table_source
	AFTER INSERT
AS
BEGIN
	SET NOCOUNT ON
 
	INSERT INTO table_historisation
	(
		table_historisation_id
		, table_historisation_valeur
		, table_historisation_date
	)
	SELECT	source_id
		, source_valeur
		, source_date
	FROM	INSERTED
END
GO
 
-- Procédure d'insertion dans table_source
-- provoque une erreur lorsque @_source_valeur n'est pas valué (la colonne source_valeur est NOT NULL)
ALTER PROCEDURE ps_source_ajoute
	@_source_valeur int = NULL
AS
BEGIN
	SET NOCOUNT ON
 
	-- Tentative d'insertion
	BEGIN TRY
		INSERT INTO dbo.table_source
		(
			source_valeur
		)
		VALUES
		(
			@_source_valeur
		)
	END TRY
	-- En cas d'erreur, on la stocke dans log_erreurs
	BEGIN CATCH
		DECLARE @error_msg nvarchar(2048)
			, @error_procedure sysname
			, @error_state int
			, @error_severity int
 
		SELECT	@error_msg = ERROR_MESSAGE()
			, @error_procedure = ERROR_PROCEDURE()
			, @error_state = ERROR_STATE()
			, @error_severity = ERROR_SEVERITY()
 
		INSERT INTO dbo.log_erreurs
		(
			module
			, error_msg
		)
		VALUES
		(
			@error_procedure
			, @error_msg
		)
 
		RAISERROR(@error_msg, @error_severity, @error_state)
	END CATCH
END
GO
 
-- Appel provoquant l'erreur
EXEC ps_source_ajoute
 
-- Visualisation de l'erreur
SELECT	*
FROM	dbo.log_erreurs

Mais normalement ce genre d'erreurs devrait être géré par votre application ... :)

@++ ;)

30/04/2010, 11h19
brinx

Bonjour,

Merci bcp elsuket pour ta réponse.

Tout à fait d'accord avec la solution postée, mais ce que je veux c'est dans le trigger qu'on déclenche l'erreur. Voici la structure de mon code :

Pour le test :

J'ai créé une table client_source(ID,Nom) et client_hist(ID_clt_hist,Nom_clt_hist).

J'ai créé un trigger after insert sur la table client_source.

J'ai essayé de déclencher dans le script du trigger une erreur par exemple select 1/0.

Quant j'exécute mes requêtes insert sur la table client_source l'insertion dedans ne passe pas, sachant que l'instruction de l'insert est bien formée, mais le problème existe au niveau du trigger (celui de select 1/0) qui annule toutes les opérations insert sur la table client_source, parce que l'exécution du trigger n'a pas réussi.

Est-ce qu'il y une solution qui permet de garder l'insertion sur la table client_source même si le trigger n'a pas réussi en exécution ??

Merci

Oui, en reprenant le code de gestion d'erreur que je vous ai donné dans la procédure stockée :

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
-- Trigger d'historisation de l'INSERT dans table_source
CREATE TRIGGER TR_AI_table_source
	ON dbo.table_source
	AFTER INSERT
AS
BEGIN
	SET NOCOUNT ON
 
	BEGIN TRY 
		INSERT INTO table_historisation
		(
			table_historisation_id
			, table_historisation_valeur
			, table_historisation_date
		)
		SELECT	source_id
			, source_valeur
			, source_date
		FROM	INSERTED
	END TRY
	BEGIN CATCH
		DECLARE @error_msg nvarchar(2048)
			, @error_procedure sysname
			, @error_state int
			, @error_severity int
 
		SELECT	@error_msg = ERROR_MESSAGE()
			, @error_procedure = ERROR_PROCEDURE()
			, @error_state = ERROR_STATE()
			, @error_severity = ERROR_SEVERITY()
 
		INSERT INTO dbo.log_erreurs
		(
			module
			, error_msg
		)
		VALUES
		(
			@error_procedure
			, @error_msg
		)
	END CATCH
END
GO

Ici on gère l'erreur en l'enregistrant dans la table log_erreurs, mais on ne relève pas l'erreur.
Donc le trigger s'exécutera bien dans tous les cas (très dangereux à mon goût), et l'INSERT dans la table source va fonctionner ...

@++ ;)

je pense que c normale avec ce script que l'insertion dans la table source passe bien dans >> le code dans le bloc catch ne sera jamais exécuté s'il ya pas d'erreur dans le bloc Try >>

si tu essaye de déclencher une erreur dans la bloc TRY qui se va passer ???

mon cas

req insertion :

Code:

insert into client values(10,'toto')

trigger :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 CreateTRIGGER [dbo].[HistorationClient] ON [dbo].[Client] AFTER insert AS BEGIN begin try --juste pour déclencher une erreur empechant le bon exécution du trigger select 1/0 end try begin catch --peut importe le script end catch END

01/05/2010, 06h12
elsuket

Citation:

si tu essaye de déclencher une erreur dans la bloc TRY qui se va passer ???

L'erreur déclenchée dans le TRY est "attrapée" dans le CATCH, et comme vous ne relancez pas l'erreur dans celui ci, le trigger s'exécutera "bien"... même si la table table_historisation n'existe plus ...

@++ ;)
03/05/2010, 11h12
brinx

Bonjour,

Oui tout à fait d'accord, mais dans mon cas je dois relancer les erreurs dans le bloc TRY (s'il y en a bien sur), alors mon trigger va exécuter les instructions du bloc catch sans oublier que le l'exécution du trigger va s'arrêter !!! et automatiquement l'annulation des insert sur la table source (transaction implicite par le trigger), mon besoin c'est que je veux que mon trigger n'annule pas les insert sur la table source même s'il y en a une erreur relancé dans le bloc try, c'est pour cela que j'ai fais les tests par select 1/0 ;-)

Merci
04/05/2010, 09h52
elsuket

Le second trigger insère dans les deux tables quoi qu'il se passe.
Si une erreur se produit, elle sera enregistrée dans la table log_erreurs.

De là vous avez trois possibilités :
- soit vous laissez tel quel, et vous traitez les messages qui seront dans cette table
- soit vous enlevez le trigger et vous remplacez par une procédure stockée
- soit vous faites le traitement de copie des données dans la table d'historique en asynchrone, à l'aide d'un job de l'Agent SQL Server

@++ ;)
04/05/2010, 10h02
brinx

bonjour,

ok je pense que je vais remplacer le trigger par une procédure stockée :)

Merci pour tes réponses !