Déclencheur qui insert des valeur nul
Bonjour a tous
j'ai un déclencheur nommé "tg_insertMvtTest" qui a comme Action c'est de copier le contenu d'une table nommé "PickLists"vers un deuxième table nommé "MvtTest"
ci joint le script de ce déclencheur
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| CREATE TRIGGER [dbo].[tg_insertMvtTest]
ON [dbo].[PickLists]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
declare
@numserie nvarchar(50),
@idTransaction int,
@idligne int,
@idStatut int
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select @numserie=Num_PickList ,@idStatut=id_Status ,@idligne=id_LigneProduction from inserted
select @idTransaction = id_Transaction from Transactions where CodeTransaction in (select Code from Status where id_Status =@idStatut )
-- Insert statements for trigger here
insert into MvtTest (NumSerie,Type_NumSerie,id_Transaction,Ligne ) values (@numserie,'PICKLIST',@idTransaction ,@idligne )
END
GO |
le problème que ces déclencheur est entrain d'insérer des valeurs nul dans le table Mvttests dans le colonne NumSerie, id_Transaction,Ligne alors que le colonne Type_NumSerie recoit ses propres valeur
aussi le table source "PickLists" ne contiens pas des valeur nuls
ci joint le script de création du table source piklists
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
| USE [SuiviMatiere]
GO
/****** Object: Table [dbo].[PickLists] Script Date: 09/16/2014 11:01:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PickLists](
[id_PickList] [int] IDENTITY(1,1) NOT NULL,
[Num_PickList] [nvarchar](50) NULL,
[id_LigneProduction] [int] NULL,
[id_Status] [int] NULL,
[Magasin] [nvarchar](200) NULL,
[DateCreation] [datetime] NULL,
[DateMAj] [datetime] NULL,
[TypePickList] [nvarchar](50) NULL,
[CodeProduit] [nvarchar](50) NULL,
[DateLivraison] [datetime] NULL,
[DateServi] [datetime] NULL,
[NbUSServi] [int] NULL,
[NbUSRecept] [int] NULL,
[Hostame] [nvarchar](50) NULL,
[Observation] [nvarchar](250) NULL,
[id_CauseServi] [int] NULL,
CONSTRAINT [PK_PickLists] PRIMARY KEY CLUSTERED
(
[id_PickList] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 4) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PickLists] WITH CHECK ADD CONSTRAINT [FK_PickLists_CauseServi] FOREIGN KEY([id_CauseServi])
REFERENCES [dbo].[CauseServi] ([id_CauseServi])
GO
ALTER TABLE [dbo].[PickLists] CHECK CONSTRAINT [FK_PickLists_CauseServi]
GO
ALTER TABLE [dbo].[PickLists] WITH CHECK ADD CONSTRAINT [FK_PickLists_LigneProduction] FOREIGN KEY([id_LigneProduction])
REFERENCES [dbo].[LigneProduction] ([id_LigneProduction])
GO
ALTER TABLE [dbo].[PickLists] CHECK CONSTRAINT [FK_PickLists_LigneProduction]
GO
ALTER TABLE [dbo].[PickLists] WITH CHECK ADD CONSTRAINT [FK_PickLists_Status] FOREIGN KEY([id_Status])
REFERENCES [dbo].[Status] ([id_Status])
GO
ALTER TABLE [dbo].[PickLists] CHECK CONSTRAINT [FK_PickLists_Status]
GO
ALTER TABLE [dbo].[PickLists] ADD CONSTRAINT [DF_PickLists_DateCreation] DEFAULT (getdate()) FOR [DateCreation]
GO
ALTER TABLE [dbo].[PickLists] ADD CONSTRAINT [DF_PickLists_DateMAj] DEFAULT (getdate()) FOR [DateMAj]
GO
ALTER TABLE [dbo].[PickLists] ADD CONSTRAINT [DF_PickLists_NbUSServi] DEFAULT ((0)) FOR [NbUSServi]
GO
ALTER TABLE [dbo].[PickLists] ADD CONSTRAINT [DF_PickLists_NbUSRecept] DEFAULT ((0)) FOR [NbUSRecept]
GO
ALTER TABLE [dbo].[PickLists] ADD CONSTRAINT [DF_PickLists_Hostame] DEFAULT (host_name()) FOR [Hostame]
GO |
ainsi que la table destination MVtest
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
| USE [SuiviMatiere]
GO
/****** Object: Table [dbo].[MvtTest] Script Date: 09/16/2014 11:02:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MvtTest](
[id_MvtTest] [int] IDENTITY(1,1) NOT NULL,
[NumSerie] [nvarchar](50) NULL,
[Type_NumSerie] [nvarchar](50) NULL,
[id_Transaction] [int] NULL,
[DatePassage] [datetime] NULL,
[UF] [nvarchar](50) NULL,
[Ligne] [nvarchar](50) NULL,
[DateCreation] [datetime] NULL,
[DateMaj] [datetime] NULL,
[Observation] [nvarchar](250) NULL,
[Matricule] [nvarchar](10) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MvtTest] ADD CONSTRAINT [DF_MvtTest_DatePassage] DEFAULT (getdate()) FOR [DatePassage]
GO
ALTER TABLE [dbo].[MvtTest] ADD CONSTRAINT [DF_MvtTest_DateCreation] DEFAULT (getdate()) FOR [DateCreation]
GO
ALTER TABLE [dbo].[MvtTest] ADD CONSTRAINT [DF_MvtTest_DateMaj] DEFAULT (getdate()) FOR [DateMaj]
GO |
ce qui m’inquiète que ce phénomène est aléatoire et parfoix il est entrain d'insérer des valeur qui sont NOT NULL
donc comment je peux contrôler que mon déclencheur est entrain d’être exécuter de façon correcte et pourquoi ces valeur null ???
