tracer un trigger et une table

**SILO** · 19/03/2009, 15h32

Bonjour,
sur SQL Server 2000, j'ai un trigger insert sur une table, qui semble ne pas se déclancher à tous les coups (en production), comment puis je verifier que le trigger se declanche bien à tous les coups?

Nous avons posé une trace profiler en utilisant le filtre sur l'D du trigger obtenu avec : object_id('NOM_TRIGGER'), j'ai fais pareil avec la table en question mais j'ai pas obtenu de resultats filtré pour la able en question.

j'ai placé un filtre sur la table '%insert% MaTable%' mais ça donne rien, aucune ligne sur le profiler

pouvez vous m'aider SVP

**SQLpro** · 19/03/2009, 17h42

Postez le code du trigger.

Vérifiez si le trigger est bien actif.

A +

**SILO** · 20/03/2009, 11h43

Voici le code :

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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
 
CREATE TRIGGER ti_CMD_BO  ON CMD FOR INSERT AS 
 
DECLARE @guill varchar(100)
 
SET @guill = ''''
 
declare @CMD_EAN128 T_D_EAN18
declare @PSV_CODE char(6)
declare @DEV_CODE char(3)
declare @TPA_CODE char(2)
declare @STC_CODE T_D_CODE
declare @CLI_ID T_D_EAN18
declare @PAY_CODE char(2)
declare @CMD_TRANSID int
declare @CMD_FACTID int
declare @CMD_ADRESSE_IP char(15)
declare @CMD_DT_CMD datetime
declare @CMD_VALIDATION bit
declare @CMD_DT_IMMEDIAT datetime
declare @CMD_DEB_IMMEDIAT datetime
declare @CMD_FIN_IMMEDIAT datetime
declare @CMD_DT_DIFFEREE datetime
declare @CMD_DEB_DIFFEREE datetime
declare @CMD_FIN_DIFFEREE datetime
declare @CMD_TOTAL_ARTICLES money
declare @CMD_TOTAL_DEVBO money
declare @CMD_DELTA_DEV char(10)
declare @CMD_TOTAL_TVA money
declare @CMD_TOTAL_PORT money
declare @CMD_TAUX_TVA int
declare @CMD_TOTAL_BA money
declare @CMD_REMISE money
declare @CMD_COMMENTAIRE varchar(8000)
declare @CMD_ADR1_LIV T_D_ADRESSE
declare @CMD_ADR2_LIV T_D_ADRESSE
declare @CMD_ADR3_LIV T_D_ADRESSE
declare @CMD_CP_LIV T_D_CODE_POSTAL
declare @CMD_VILLE_LIV T_D_ADRESSE
declare @CMD_ETAT_LIV T_D_ETAT
declare @CMD_DT_IMMEDIAT_LIV datetime
declare @CMD_DEB_IMMEDIAT_LIV datetime
declare @CMD_TOTAL_ARTICLES_LIV money
declare @CMD_TOTAL_TVA_LIV money
declare @CMD_TOTAL_BA_LIV money
declare @CMD_DT_CRE T_D_DT_CRE_MAJ
declare @CMD_DT_MAJ T_D_DT_CRE_MAJ
declare @CMD_DT_FACT T_D_DT_CRE_MAJ
declare @CMD_TEL_LIV varchar(20)
declare @CMD_BAT_LIV varchar(20)
declare @CMD_ESCALIER_LIV varchar(10)
declare @CMD_ETAGE_LIV int
declare @CMD_DIGICODE_LIV varchar(10)
declare @CMD_INTERPHONE_LIV bit
declare @CMD_REM_ADR_LIV varchar(60)
declare @CMD_COMMENTAIRE_LIV varchar(8000)
declare @CMD_COLIS_FRAIS int
declare @CMD_COLIS_EPICERIE int
declare @CMD_COLIS_SURGELE int
declare @rowguid varchar(50)
declare @CMD_NUM_VOIE_LIV varchar(10)
declare @CMD_TYPE_VOIE_LIV varchar(22)
declare @CMD_NOM_VOIE_LIV varchar(64)
declare @CMD_ASCENCEUR_LIV bit
declare @CMD_LATITUDE_LIV varchar(10)
declare @CMD_LONGITUDE_LIV varchar(10)
declare @CMD_DUREE_LIV int
declare @CMD_LAD_CODE char(6)
declare @CMD_ZONE_MARKETING int
declare @CMD_ZONE_LIV int
declare @CMD_ADR_VALIDE int
declare @CIV_ID int
declare @CMD_NOM varchar(25)
declare @CMD_PRENOM varchar(25)
declare @CMD_EMAIL varchar(50)
declare @CMD_LAD_PONDERATION int
declare @AVT_CODE varchar(10)
 
 
declare @ordre_insert varchar(8000)
 
DECLARE CURS_INS CURSOR FOR 
SELECT CMD_EAN128, PSV_CODE, DEV_CODE, TPA_CODE, STC_CODE, CLI_ID, PAY_CODE, CMD_TRANSID, CMD_FACTID, CMD_ADRESSE_IP, CMD_DT_CMD, CMD_VALIDATION, CMD_DT_IMMEDIAT, CMD_DEB_IMMEDIAT, CMD_FIN_IMMEDIAT, CMD_DT_DIFFEREE, CMD_DEB_DIFFEREE, CMD_FIN_DIFFEREE, CMD_TOTAL_ARTICLES, CMD_TOTAL_DEVBO, CMD_DELTA_DEV, CMD_TOTAL_TVA, CMD_TOTAL_PORT, CMD_TAUX_TVA, CMD_TOTAL_BA, CMD_REMISE, CMD_ADR1_LIV, CMD_ADR2_LIV, CMD_ADR3_LIV, CMD_CP_LIV, CMD_VILLE_LIV, CMD_ETAT_LIV, CMD_DT_IMMEDIAT_LIV, CMD_DEB_IMMEDIAT_LIV, CMD_TOTAL_ARTICLES_LIV, CMD_TOTAL_TVA_LIV, CMD_TOTAL_BA_LIV, CMD_DT_CRE, CMD_DT_MAJ, CMD_DT_FACT, CMD_TEL_LIV, CMD_BAT_LIV, CMD_ESCALIER_LIV, CMD_ETAGE_LIV, CMD_DIGICODE_LIV, CMD_INTERPHONE_LIV, CMD_REM_ADR_LIV, CMD_COLIS_FRAIS, CMD_COLIS_EPICERIE, CMD_COLIS_SURGELE, rowguid, CMD_NUM_VOIE_LIV, CMD_TYPE_VOIE_LIV, CMD_NOM_VOIE_LIV, CMD_ASCENCEUR_LIV, CMD_LATITUDE_LIV, CMD_LONGITUDE_LIV, CMD_DUREE_LIV, CMD_LAD_CODE, CMD_ZONE_MARKETING, CMD_ZONE_LIV, CMD_ADR_VALIDE, CIV_ID, CMD_NOM, CMD_PRENOM, CMD_EMAIL, CMD_LAD_PONDERATION, AVT_CODE FROM INSERTED
 
open curs_ins
 
FETCH NEXT FROM CURS_INS INTO @CMD_EAN128, @PSV_CODE, @DEV_CODE, @TPA_CODE, @STC_CODE, @CLI_ID, @PAY_CODE, @CMD_TRANSID, @CMD_FACTID, @CMD_ADRESSE_IP, @CMD_DT_CMD, @CMD_VALIDATION, @CMD_DT_IMMEDIAT, @CMD_DEB_IMMEDIAT, @CMD_FIN_IMMEDIAT, @CMD_DT_DIFFEREE, @CMD_DEB_DIFFEREE, @CMD_FIN_DIFFEREE, @CMD_TOTAL_ARTICLES, @CMD_TOTAL_DEVBO, @CMD_DELTA_DEV, @CMD_TOTAL_TVA, @CMD_TOTAL_PORT, @CMD_TAUX_TVA, @CMD_TOTAL_BA, @CMD_REMISE, @CMD_ADR1_LIV, @CMD_ADR2_LIV, @CMD_ADR3_LIV, @CMD_CP_LIV, @CMD_VILLE_LIV, @CMD_ETAT_LIV, @CMD_DT_IMMEDIAT_LIV, @CMD_DEB_IMMEDIAT_LIV, @CMD_TOTAL_ARTICLES_LIV, @CMD_TOTAL_TVA_LIV, @CMD_TOTAL_BA_LIV, @CMD_DT_CRE, @CMD_DT_MAJ, @CMD_DT_FACT, @CMD_TEL_LIV, @CMD_BAT_LIV, @CMD_ESCALIER_LIV, @CMD_ETAGE_LIV, @CMD_DIGICODE_LIV, @CMD_INTERPHONE_LIV, @CMD_REM_ADR_LIV, @CMD_COLIS_FRAIS, @CMD_COLIS_EPICERIE, @CMD_COLIS_SURGELE, @rowguid, @CMD_NUM_VOIE_LIV, @CMD_TYPE_VOIE_LIV, @CMD_NOM_VOIE_LIV, @CMD_ASCENCEUR_LIV, @CMD_LATITUDE_LIV, @CMD_LONGITUDE_LIV, @CMD_DUREE_LIV, @CMD_LAD_CODE, @CMD_ZONE_MARKETING, @CMD_ZONE_LIV, @CMD_ADR_VALIDE, @CIV_ID, @CMD_NOM, @CMD_PRENOM, @CMD_EMAIL, @CMD_LAD_PONDERATION, @AVT_CODE
 
WHILE @@FETCH_STATUS = 0
 
BEGIN 
 
set @ordre_insert = 'insert into CMD(CMD_EAN128,PSV_CODE,DEV_CODE,TPA_CODE,STC_CODE,CLI_ID,PAY_CODE,CMD_TRANSID,CMD_FACTID,CMD_ADRESSE_IP,CMD_DT_CMD,CMD_VALIDATION,CMD_DT_IMMEDIAT,CMD_DEB_IMMEDIAT,CMD_FIN_IMMEDIAT,CMD_DT_DIFFEREE,CMD_DEB_DIFFEREE,CMD_FIN_DIFFEREE,CMD_TOTAL_ARTICLES,CMD_TOTAL_DEVBO,CMD_DELTA_DEV,CMD_TOTAL_TVA,CMD_TOTAL_PORT,CMD_TAUX_TVA,CMD_TOTAL_BA,CMD_REMISE,CMD_ADR1_LIV,CMD_ADR2_LIV,CMD_ADR3_LIV,CMD_CP_LIV,CMD_VILLE_LIV,CMD_ETAT_LIV,CMD_DT_IMMEDIAT_LIV,CMD_DEB_IMMEDIAT_LIV,CMD_TOTAL_ARTICLES_LIV,CMD_TOTAL_TVA_LIV,CMD_TOTAL_BA_LIV,CMD_DT_CRE,CMD_DT_MAJ,CMD_DT_FACT,CMD_TEL_LIV,CMD_BAT_LIV,CMD_ESCALIER_LIV,CMD_ETAGE_LIV,CMD_DIGICODE_LIV,CMD_INTERPHONE_LIV,CMD_REM_ADR_LIV,CMD_COLIS_FRAIS,CMD_COLIS_EPICERIE,CMD_COLIS_SURGELE,rowguid,CMD_NUM_VOIE_LIV,CMD_TYPE_VOIE_LIV,CMD_NOM_VOIE_LIV,CMD_ASCENCEUR_LIV,CMD_LATITUDE_LIV,CMD_LONGITUDE_LIV,CMD_DUREE_LIV,CMD_LAD_CODE,CMD_ZONE_MARKETING,CMD_ZONE_LIV,CMD_ADR_VALIDE,CIV_ID,CMD_NOM,CMD_PRENOM,CMD_EMAIL,CMD_LAD_PONDERATION,AVT_CODE)'
 
SET @ordre_insert = @ordre_insert +' values ('+isnull( @guill+rtrim(replace(@CMD_EAN128,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@PSV_CODE,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@DEV_CODE,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@TPA_CODE,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull(convert(varchar(1000),@STC_CODE),'null') + ', '+ isnull( @guill+rtrim(replace(@CLI_ID,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@PAY_CODE,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull(convert(varchar(1000),@CMD_TRANSID),'null') + ', '+ isnull(convert(varchar(1000),@CMD_FACTID),'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_ADRESSE_IP,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull(@guill +convert(varchar(20),@CMD_DT_CMD, 120)+@guill ,'null') + ', '+ isnull(convert(varchar(1000),@CMD_VALIDATION),'null') + ', '+ isnull(@guill +convert(varchar(20),@CMD_DT_IMMEDIAT, 120)+@guill ,'null') + ', '+ isnull(@guill +convert(varchar(20),@CMD_DEB_IMMEDIAT, 120)+@guill ,'null') + ', '+ isnull(@guill +convert(varchar(20),@CMD_FIN_IMMEDIAT, 120)+@guill ,'null') + ', '+ isnull(@guill +convert(varchar(20),@CMD_DT_DIFFEREE, 120)+@guill ,'null') + ', '+ isnull(@guill +convert(varchar(20),@CMD_DEB_DIFFEREE, 120)+@guill ,'null') + ', '+ isnull(@guill +convert(varchar(20),@CMD_FIN_DIFFEREE, 120)+@guill ,'null') + ', '+ isnull(convert(varchar(1000),@CMD_TOTAL_ARTICLES),'null') + ', '+ isnull(convert(varchar(1000),@CMD_TOTAL_DEVBO),'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_DELTA_DEV,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull(convert(varchar(1000),@CMD_TOTAL_TVA),'null') + ', '+ isnull(convert(varchar(1000),@CMD_TOTAL_PORT),'null') + ', '+ isnull(convert(varchar(1000),@CMD_TAUX_TVA),'null') + ', '+ isnull(convert(varchar(1000),@CMD_TOTAL_BA),'null') + ', '+ isnull(convert(varchar(1000),@CMD_REMISE),'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_ADR1_LIV,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_ADR2_LIV,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_ADR3_LIV,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_CP_LIV,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_VILLE_LIV,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_ETAT_LIV,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull(@guill +convert(varchar(20),@CMD_DT_IMMEDIAT_LIV, 120)+@guill ,'null') + ', '+ isnull(@guill +convert(varchar(20),@CMD_DEB_IMMEDIAT_LIV, 120)+@guill ,'null') + ', '+ isnull(convert(varchar(1000),@CMD_TOTAL_ARTICLES_LIV),'null') + ', '+ isnull(convert(varchar(1000),@CMD_TOTAL_TVA_LIV),'null') + ', '+ isnull(convert(varchar(1000),@CMD_TOTAL_BA_LIV),'null') + ', '+ isnull(@guill +convert(varchar(20),@CMD_DT_CRE, 120)+@guill ,'null') + ', '+ isnull(@guill +convert(varchar(20),@CMD_DT_MAJ, 120)+@guill ,'null') + ', '+ isnull(@guill +convert(varchar(20),@CMD_DT_FACT, 120)+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_TEL_LIV,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_BAT_LIV,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_ESCALIER_LIV,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull(convert(varchar(1000),@CMD_ETAGE_LIV),'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_DIGICODE_LIV,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull(convert(varchar(1000),@CMD_INTERPHONE_LIV),'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_REM_ADR_LIV,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull(convert(varchar(1000),@CMD_COLIS_FRAIS),'null') + ', '+ isnull(convert(varchar(1000),@CMD_COLIS_EPICERIE),'null') + ', '+ isnull(convert(varchar(1000),@CMD_COLIS_SURGELE),'null') + ', '+ isnull( @guill+rtrim(replace(@rowguid,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_NUM_VOIE_LIV,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_TYPE_VOIE_LIV,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_NOM_VOIE_LIV,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull(convert(varchar(1000),@CMD_ASCENCEUR_LIV),'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_LATITUDE_LIV,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_LONGITUDE_LIV,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull(convert(varchar(1000),@CMD_DUREE_LIV),'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_LAD_CODE,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull(convert(varchar(1000),@CMD_ZONE_MARKETING),'null') + ', '+ isnull(convert(varchar(1000),@CMD_ZONE_LIV),'null') + ', '+ isnull(convert(varchar(1000),@CMD_ADR_VALIDE),'null') + ', '+ isnull(convert(varchar(1000),@CIV_ID),'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_NOM,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_PRENOM,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull( @guill+rtrim(replace(@CMD_EMAIL,@guill ,@guill+ @guill))+@guill ,'null') + ', '+ isnull(convert(varchar(1000),@CMD_LAD_PONDERATION),'null') + ', '+ isnull( @guill+rtrim(replace(@AVT_CODE,@guill ,@guill+ @guill))+@guill ,'null') +')'
 
insert into LOG_CMD_TRIGGER(LOCS_CMDSQL) values (  @ordre_insert )
 
FETCH NEXT FROM CURS_INS INTO @CMD_EAN128, @PSV_CODE, @DEV_CODE, @TPA_CODE, @STC_CODE, @CLI_ID, @PAY_CODE, @CMD_TRANSID, @CMD_FACTID, @CMD_ADRESSE_IP, @CMD_DT_CMD, @CMD_VALIDATION, @CMD_DT_IMMEDIAT, @CMD_DEB_IMMEDIAT, @CMD_FIN_IMMEDIAT, @CMD_DT_DIFFEREE, @CMD_DEB_DIFFEREE, @CMD_FIN_DIFFEREE, @CMD_TOTAL_ARTICLES, @CMD_TOTAL_DEVBO, @CMD_DELTA_DEV, @CMD_TOTAL_TVA, @CMD_TOTAL_PORT, @CMD_TAUX_TVA, @CMD_TOTAL_BA, @CMD_REMISE, @CMD_ADR1_LIV, @CMD_ADR2_LIV, @CMD_ADR3_LIV, @CMD_CP_LIV, @CMD_VILLE_LIV, @CMD_ETAT_LIV, @CMD_DT_IMMEDIAT_LIV, @CMD_DEB_IMMEDIAT_LIV, @CMD_TOTAL_ARTICLES_LIV, @CMD_TOTAL_TVA_LIV, @CMD_TOTAL_BA_LIV, @CMD_DT_CRE, @CMD_DT_MAJ, @CMD_DT_FACT, @CMD_TEL_LIV, @CMD_BAT_LIV, @CMD_ESCALIER_LIV, @CMD_ETAGE_LIV, @CMD_DIGICODE_LIV, @CMD_INTERPHONE_LIV, @CMD_REM_ADR_LIV, @CMD_COLIS_FRAIS, @CMD_COLIS_EPICERIE, @CMD_COLIS_SURGELE, @rowguid, @CMD_NUM_VOIE_LIV, @CMD_TYPE_VOIE_LIV, @CMD_NOM_VOIE_LIV, @CMD_ASCENCEUR_LIV, @CMD_LATITUDE_LIV, @CMD_LONGITUDE_LIV, @CMD_DUREE_LIV, @CMD_LAD_CODE, @CMD_ZONE_MARKETING, @CMD_ZONE_LIV, @CMD_ADR_VALIDE, @CIV_ID, @CMD_NOM, @CMD_PRENOM, @CMD_EMAIL, @CMD_LAD_PONDERATION, @AVT_CODE
 
END
 
CLOSE CURS_INS
 
deallocate curs_ins

On a constaté que le trigger se déclanche (profiler sur l'object_id du trigger). Par contre notre table LOG_CMD_TRIGGER ne contient pas de chaine de caractère contenant "insert into CMD"...

La structure de la table LOG_CMD_TRIGGER est la suivante :

Code :

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

1
2
3
4
5
6
CREATE TABLE [dbo].[LOG_CMD_TRIGGER] (
	[LOCS_ORDRE_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[LOCS_CMDSQL] [varchar] (5000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LOCS_DT_CRE] [datetime] NOT NULL 
) ON [PRIMARY]
GO

tracer un trigger et une table

MS SQL Server

Discussions similaires

Partager

Partager