Trigger / Stored Procedure contenant un SELECT avec JOIN - aucun résultat

Version imprimable

SQL Server 2000 (ou MSDE 2000)
J'ai un trigger sur INSERT d'une table de transaction:

Code:

1
2
3
4
5
6
CREATE TRIGGER TG_EXPORT_TO_MAIT ON [overdrive].[TRANSACTION_TABLE] 
FOR INSERT
AS
DECLARE @TransNo varchar(20)
select @TransNo=TRANS_NO FROM INSERTED
EXEC sp_ExportTransInfo @TransNo

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE PROCEDURE sp_ExportTransInfo @TransNo VarChar(20) AS
 
DECLARE @transline as varchar(255)
DECLARE @DestinationFile as varchar(255)
 
SET @TransLine = (SELECT    LEFT(RTRIM(LTRIM(TRANS_NO)) + REPLICATE(' ', 10), 10) + 
     LEFT(STATUS, 1) + 
    LEFT(ISNULL(CONVERT(varchar(19), TRANSACTION_START_DATE, 120), REPLICATE(' ', 19)), 19) + 
    LEFT(ISNULL(CONVERT(varchar(19), TRANSACTION_COMPLETE_DATE, 120), REPLICATE(' ', 19)), 19) + 
    LEFT(RTRIM(LTRIM(VEHICLE_ID)) + REPLICATE(' ', 20), 20) + 
    LEFT(RTRIM(LTRIM(CARRIER_ID)) + REPLICATE(' ', 20), 20) AS TransLine
FROM         overdrive.TRANSACTION_TABLE
WHERE     (overdrive.TRANSACTION_TABLE.TRANS_NO = @TransNo))
 
SET @DestinationFile = 'C:\'+@TransNo+'.odrive'
 
EXEC sp_AppendToFile @DestinationFile, @transline
GO

La requête SELECT récupère une chaine comme ceci:
12 V2009-05-08 10:26:58 VEH-123 TR-TEST

qui doit être inscrite dans un fichier texte (par sp_AppendToFile - qui fonctionne bien)

===================

jusqu'à maintenant tout s'exécute bien

jusqu'à ce que je décide d'ajouter un champ dans la requête SELECT qui met un JOIN vers une autre table, dans ce cas, rien n'est inscrit dans le fichier. Mais par contre la requête toute seule s'exécute bien. Et si je fait un OSQL pour exécuter la store Procédure, le fichier contient quelque chose, c'est seulement lorsque je passe par le trigger (aussitôt qu'un record est inséré, rien n'apparait dans le fichier). Et en plus, si j'essaie le trigger sur UPDATE au lieu d'INSERT, ça marche. bizarre ?!?!

Quelqu'un a une idée ?

Désolé pour la requête un peu complexe, c'est une concaténation de champs pour avoir un seul champ retourné dans ma variable @TransLine.

ça fai 2 jours que je suis là dessus sans succès.

=========================

Voici la Stored Procedure avec la requête contenant le join qui me donne rien dans mon fichier lorsqu'exécuté à partir du Trigger INSERT.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE PROCEDURE sp_ExportTransInfo @TransNo VarChar(20) AS
 
DECLARE @transline as varchar(255)
DECLARE @DestinationFile as varchar(255)
 
/*SET @TransLine = (SELECT Trans_No from  overdrive.TRANSACTION_TABLE
             WHERE overdrive.TRANSACTION_TABLE.TRANS_NO = @TransNo)*/
 
SET @TransLine = (SELECT     LEFT(RTRIM(LTRIM(overdrive.TRANSACTION_TABLE.TRANS_NO)) + REPLICATE(' ', 10), 10) + LEFT(overdrive.TRANSACTION_TABLE.STATUS, 1) 
                      + LEFT(ISNULL(CONVERT(varchar(19), overdrive.TRANSACTION_TABLE.TRANSACTION_START_DATE, 120), REPLICATE(' ', 19)), 19) 
                      + LEFT(ISNULL(CONVERT(varchar(19), overdrive.TRANSACTION_TABLE.TRANSACTION_COMPLETE_DATE, 120), REPLICATE(' ', 19)), 19) 
                      + LEFT(RTRIM(LTRIM(overdrive.TRANSACTION_TABLE.VEHICLE_ID)) + REPLICATE(' ', 20), 20) 
                      + LEFT(RTRIM(LTRIM(overdrive.TRANSACTION_TABLE.CARRIER_ID)) + REPLICATE(' ', 20), 20) 
                      + overdrive.TRANSACTION_DETAIL.PRODUCT_ID AS TransLine
FROM         overdrive.TRANSACTION_TABLE INNER JOIN
                      overdrive.TRANSACTION_DETAIL ON overdrive.TRANSACTION_TABLE.TRANS_NO = overdrive.TRANSACTION_DETAIL.TRANS_NO
WHERE     (overdrive.TRANSACTION_TABLE.TRANS_NO = @TransNo))
 
SET @DestinationFile = 'C:\'+@TransNo+'.odrive'
 
EXEC sp_AppendToFile @DestinationFile, @transline
GO

Merci !

08/05/2009, 18h36
agemis31

Bonsoir,

Ca me semble normal:

Vous avez un trigger sur after insert sur la table maître.
Danc ce trigger, je ne vois pas d'insertion dans la table détail -> inner join ne renvoie rien.

Attention. Vous raisonnez à la ligne: vous risquez d'avoir des problème si on insère plus d'une ligne dans la table maître en même temps, votre requête de texte posera aussi problème si vous avez plusieurs détails.

@+

Bonjour,

Votre trigger est faux, puisqu'il n'est pas ensembliste.
Vous pouvez voir dans ce billet les erreurs que cela peut produire ;)

Pour écrire donc cela proprement, je vous propose de passer une liste de valeurs séparées par un "pipe" par exemple à votre procédure stockée, qui découpera cette chaîne pour en récupérer les valeurs et effectuer la requête.

Pour cela nous avons besoin d'une fonction de découpage :

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
CREATE FUNCTION dbo.Split(@RawData VARCHAR(1024), @SplitOn CHAR(1))
	RETURNS @RtnValue TABLE 
	(
		Id INT IDENTITY(1, 1),
		Data VARCHAR(16)
	)
WITH SCHEMABINDING
AS  
BEGIN 
	DECLARE @Cnt INT SET @Cnt = 1
 
	WHILE CHARINDEX(@SplitOn, @RawData) > 0
	BEGIN
		INSERT INTO @RtnValue (data)
		SELECT LTRIM(RTRIM(SUBSTRING(@RawData, 1, CHARINDEX(@SplitOn, @RawData) - 1)))
 
		SELECT @RawData = SUBSTRING(@RawData, CHARINDEX(@SplitOn, @RawData) + 1, LEN(@RawData))
		SET @Cnt = @Cnt + 1
	END
 
	INSERT INTO @RtnValue (data)
	SELECT LTRIM(RTRIM(@RawData)) AS Data
 
	RETURN
END

Dès lors votre trigger devient :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TRIGGER TG_EXPORT_TO_MAIT
	ON [overdrive].[TRANSACTION_TABLE]
FOR INSERT
AS
BEGIN
	DECLARE @TransNumbers VARCHAR(1024)
 
	SELECT @TransNumbers = ISNULL(@TransNumbers, '') + TRANS_NO + '|'
	FROM INSERTED
 
	EXEC dbo.sp_ExportTransInfo @TransNumbers
END

C'est bizarre qu'il manque une insertion dans la table TRANSACTION_DETAIL ... mais c'est ce qui semble expliquer les problèmes de jointure ;)

Votre procédure serait alors :

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 PROCEDURE dbo.sp_ExportTransInfo
	@TransNo VARCHAR(1024)
BEGIN
	DECLARE @TransLine AS VARCHAR(2048)
	DECLARE @DestinationFile AS VARCHAR(255)
 
	SELECT @TransLine = ISNULL(@TransLine, '')
						+ LEFT(RTRIM(LTRIM(TRANS.TRANS_NO)) + REPLICATE(' ', 10), 10)
						+ LEFT(TRANS.STATUS, 1)
						+ LEFT(ISNULL(CONVERT(VARCHAR(19), TRANS.TRANSACTION_START_DATE, 120), REPLICATE(' ', 19)), 19)
						+ LEFT(ISNULL(CONVERT(VARCHAR(19), TRANS.TRANSACTION_COMPLETE_DATE, 120), REPLICATE(' ', 19)), 19)
						+ LEFT(RTRIM(LTRIM(TRANS.VEHICLE_ID)) + REPLICATE(' ', 20), 20)
						+ LEFT(RTRIM(LTRIM(TRANS.CARRIER_ID)) + REPLICATE(' ', 20), 20)
						+ DETAIL.PRODUCT_ID 
						+ ',' AS TransLine
	FROM overdrive.TRANSACTION_TABLE AS TRANS
	INNER JOIN overdrive.TRANSACTION_DETAIL AS DETAIL ON TRANS.TRANS_NO = DETAIL.TRANS_NO
	INNER JOIN dbo.Split AS SPLIT ON CAST(SPLIT.Data AS INT) = TRANS.TRANS_NO
 
	SET @DestinationFile = 'C:\'+ @TransNo + '.odrive'
 
	EXEC dbo.sp_AppendToFile @DestinationFile, @TransLine
END

Je pense que sp_AppendToFile est une procédure stockée d'assembly, et si c'est le cas vous auriez tout interêt à passer la concaténation des valeurs de TRANSACTION_TABLE.TRANS_NO du trigger directement à cette procédure, qui se chargera du reste ;)

Pensez à la balide code quand vous postez (bouton #) ;)

@++ ;)

J'obient une erreur sur la nouvelles sp_ExportTransInfo:

Citation:

Error 156: Incorrect Syntax near the Keyword 'AS'

Mais je ne sais pas quel 'AS' est fautif.

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
 
CREATE PROCEDURE sp_ExportTransInfo @TransNo VarChar(20) AS
 
DECLARE @transline as varchar(255)
DECLARE @DestinationFile as varchar(255)
 
/*SET @TransLine = (SELECT     LEFT(RTRIM(LTRIM(overdrive.TRANSACTION_TABLE.TRANS_NO)) + REPLICATE(' ', 10), 10) + LEFT(overdrive.TRANSACTION_TABLE.STATUS, 1) 
                      + LEFT(ISNULL(CONVERT(varchar(19), overdrive.TRANSACTION_TABLE.TRANSACTION_START_DATE, 120), REPLICATE(' ', 19)), 19) 
                      + LEFT(ISNULL(CONVERT(varchar(19), overdrive.TRANSACTION_TABLE.TRANSACTION_COMPLETE_DATE, 120), REPLICATE(' ', 19)), 19) 
                      + LEFT(RTRIM(LTRIM(overdrive.TRANSACTION_TABLE.VEHICLE_ID)) + REPLICATE(' ', 20), 20) 
                      + LEFT(RTRIM(LTRIM(overdrive.TRANSACTION_TABLE.CARRIER_ID)) + REPLICATE(' ', 20), 20) 
                      + overdrive.TRANSACTION_DETAIL.PRODUCT_ID AS TransLine
FROM         overdrive.TRANSACTION_TABLE INNER JOIN
                      overdrive.TRANSACTION_DETAIL ON overdrive.TRANSACTION_TABLE.TRANS_NO = overdrive.TRANSACTION_DETAIL.TRANS_NO
WHERE     (overdrive.TRANSACTION_TABLE.TRANS_NO = @TransNo))*/
 
SELECT @TransLine = ISNULL(@TransLine, '')
						+ LEFT(RTRIM(LTRIM(TRANS.TRANS_NO)) + REPLICATE(' ', 10), 10)
						+ LEFT(TRANS.STATUS, 1)
						+ LEFT(ISNULL(CONVERT(VARCHAR(19), TRANS.TRANSACTION_START_DATE, 120), REPLICATE(' ', 19)), 19)
						+ LEFT(ISNULL(CONVERT(VARCHAR(19), TRANS.TRANSACTION_COMPLETE_DATE, 120), REPLICATE(' ', 19)), 19)
						+ LEFT(RTRIM(LTRIM(TRANS.VEHICLE_ID)) + REPLICATE(' ', 20), 20)
						+ LEFT(RTRIM(LTRIM(TRANS.CARRIER_ID)) + REPLICATE(' ', 20), 20)
						+ DETAIL.PRODUCT_ID 
						+ ',' AS TransLine
	FROM overdrive.TRANSACTION_TABLE 
	INNER JOIN overdrive.TRANSACTION_DETAIL ON overdrive.TRANSACTION_TABLE.TRANS_NO = overdrive.TRANSACTION_DETAIL.TRANS_NO
	INNER JOIN dbo.Split AS SPLIT ON CAST(SPLIT.DATA AS INT) = overdrive.TRANSACTION_TABLE.TRANS_NO
 
 
SET @DestinationFile = 'C:\'+@TransNo+'.odrive'
 
EXEC sp_AppendToFile @DestinationFile, @transline
GO

En plus, je ne comprends pas trop comment la requête fait pour savoir de quelles transactions elle extrait les donnée puisque le "WHERE Trans_no = @TransNo" n'est plus présent.

C'est assez complexe je trouve, moi qui n'est habitué qu'à exécuter des requête de base... Mais j'essaie de comprendre, ça me donne rien de copier/coller sans le comprendre. Et là je manque le bout entre "Split.Data" versus le paramètre @TransNo (recevant @TransNumbers du trigger)...

En plus, mon nom de fichier @TransNo.ODrive doit seulement être appelé par un seul no. de transaction (pas tous les no. séparé par un "pipe").

Est-ce que le fait de mettre "BEGIN .... END" fait en sorte que ça boucle pour tous les enregistrements affectés ??

Aussi: la colonne TRANS_NO est de type varchar(20), pas un INT. Est-ce que ça a un impact sur le "SPLIT.DATA AS INT" ??

Vraiment plus complexe que ce que j'avais "imaginé" comme traitement.

OK après avoire testé en enlevant les 'AS' un à un, j'ai fini par trouver que le 'AS' de la ligne suivante n'allait pas là: (et c'est logique puisqu'on assign à la variable @TransLine)

Code:

+ ',' AS TransLine

Mais je ne comprend toujours pas l'appel de la fonction split, comment fait elle pour splitter la variable @TransNo , on dirait qu'il manque un bout ?

Si je test ça comme ça, lorsque j'ajoute une transaction, il y a même pas mon fichier (NoTrans.ODrive) qui se créé. c'est pire qu'avant.
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 CREATE PROCEDURE dbo.sp_ExportTransInfo @TransNo VARCHAR(1024) AS BEGIN DECLARE @TransLine AS VARCHAR(2048) DECLARE @DestinationFile AS VARCHAR(255) SELECT @TransLine = ISNULL(@TransLine, '') + LEFT(RTRIM(LTRIM(TRANS.TRANS_NO)) + REPLICATE(' ', 10), 10) + LEFT(TRANS.STATUS, 1) + LEFT(ISNULL(CONVERT(VARCHAR(19), TRANS.TRANSACTION_START_DATE, 120), REPLICATE(' ', 19)), 19) + LEFT(ISNULL(CONVERT(VARCHAR(19), TRANS.TRANSACTION_COMPLETE_DATE, 120), REPLICATE(' ', 19)), 19) + LEFT(RTRIM(LTRIM(TRANS.VEHICLE_ID)) + REPLICATE(' ', 20), 20) + LEFT(RTRIM(LTRIM(TRANS.CARRIER_ID)) + REPLICATE(' ', 20), 20) + DETAIL.PRODUCT_ID + ',' FROM overdrive.TRANSACTION_TABLE AS TRANS INNER JOIN overdrive.TRANSACTION_DETAIL AS DETAIL ON TRANS.TRANS_NO = DETAIL.TRANS_NO INNER JOIN dbo.Split AS SPLIT ON CAST(SPLIT.DATA AS INT) = TRANS.TRANS_NO SET @DestinationFile = 'C:\'+ @TransNo + '.odrive' EXEC dbo.sp_AppendToFile @DestinationFile, @TransLine END GO
J'vais continuer de chercher et de tester avec un plus petite requête pour voir.

Bon là, j'ai réussi à faire fonctionner la StoreProcedure "sp_ExportTransInfo"
mais seulement qnad je fait un EXEC par OSQL en DOS.
Si elle s'exécute par le trigger, je me retrouve avec le même problème initial: Mon fichier se créé mais est vide lorsq eu je fais une transaction (INSERT).
Sauf que après je suis capablethe faire un EXEC de la store procédure et le fichier contient les infos.

Mon code actuel:
== Stored Procedure ==
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 CREATE PROCEDURE dbo.sp_ExportTransInfo @TransNo VARCHAR(1024) AS BEGIN DECLARE @TransLine AS VARCHAR(2048) DECLARE @DestinationFile AS VARCHAR(255) SELECT @TransLine = ISNULL( @TransLine, '') + TRANS.TRANS_NO + DETAIL.PRODUCT_ID FROM overdrive.TRANSACTION_TABLE AS TRANS INNER JOIN overdrive.TRANSACTION_DETAIL AS DETAIL ON TRANS.TRANS_NO = DETAIL.TRANS_NO INNER JOIN dbo.Split(@TransNo, '|') ON (dbo.SPLIT.DATA = TRANS.TRANS_NO) SET @DestinationFile = 'C:\Test.ODrive' EXEC dbo.sp_AppendToFile @DestinationFile, @TransLine END GO
=== Trigger INSERT sur table TRANSACTION ===
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE TRIGGER TG_EXPORT_TO_MAIT ON [overdrive].[TRANSACTION_TABLE] FOR INSERT AS BEGIN DECLARE @TransNumbers VARCHAR(1024) SELECT @TransNumbers = ISNULL(@TransNumbers, '') + TRANS_NO + '|' FROM INSERTED EXEC dbo.sp_ExportTransInfo @TransNumbers END
=== Fonction SPLIT qui split les no. de transaction selon un caractère de séparation -- ici le "pipe" (|) et retourne une table ===
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 CREATE FUNCTION dbo.Split(@RawData VARCHAR(1024), @SplitOn CHAR(1)) RETURNS @RtnValue TABLE ( Id INT IDENTITY(1, 1), DATA VARCHAR(16) ) WITH SCHEMABINDING AS BEGIN DECLARE @Cnt INT SET @Cnt = 1 WHILE CHARINDEX(@SplitOn, @RawData) > 0 BEGIN INSERT INTO @RtnValue (DATA) SELECT LTRIM(RTRIM(SUBSTRING(@RawData, 1, CHARINDEX(@SplitOn, @RawData) - 1))) SELECT @RawData = SUBSTRING(@RawData, CHARINDEX(@SplitOn, @RawData) + 1, LEN(@RawData)) SET @Cnt = @Cnt + 1 END INSERT INTO @RtnValue (DATA) SELECT LTRIM(RTRIM(@RawData)) AS DATA RETURN END
=== Et la fonction qui écrit dans le fichier ===
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 CREATE PROCEDURE sp_AppendToFile(@FileName varchar(255), @Text1 varchar(255)) AS DECLARE @FS int, @OLEResult int, @FileID int EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject' --Open a file execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1 IF @OLEResult <> 0 PRINT 'OpenTextFile' --Write Text1 execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1 IF @OLEResult <> 0 PRINT 'WriteLine' EXECUTE @OLEResult = sp_OADestroy @FileID EXECUTE @OLEResult = sp_OADestroy @FS GO
J'viens de passer 3 heures à essayer de faire fonctionner ça pour revenir au résultat initial: Le fichier s'écrit (vide) sur le INSERT aussitôt qu'il y a le JOIN impliqué dans la requête.

:arf:

Et en passant, je n'ai pas vraiment besoin de fonction "SPLIT" puisque j'ai seulement une transaction inscrite à la fois, et un seul système qui écrit dans cette base de donnée. mais par contre je peux avoir plusieurs TRANSACTION_DETAIL pour un TRANSACTION_TABLE après un UPDATE qui sera fera plus tard.

Est-ce que je dois mettre aussi un TRIGGER sur la table "TRANSACTION_DETAIL" ? Alors là j'aurai 2 Trigger ?? ou est-ce qu'un seul trigger peut couvrir 2 tables en même temps ???

J'me trouve tellement "newbe" dans ça.

09/05/2009, 00h46
agemis31

Bonsoir,

Apparement, vous essayer de maintenir vos fichiers textes synchrones avec la base.
D'une part, ce n'est peut être pas la bonne solution: en avez vous besoin ?
D'autre part, en partant sur l'approche trigger, vous allez devoir gérer des triggers sur les deux tables, et ceci pour les ajouts, modifications et suppressions, en ce qui concerne ces deux dernières, ajouter une ligne n'est pas la bonne solution puisque si j'ai bien compris, il s'agit d'un export et pas d'un historique.

Il serait peut être plus judicieux d'utiliser bcp (ou autre), pour exporter votre fichier texte, quand c'est nécessaire. Donc d'écrire une procédure stockée d'export.

@+
11/05/2009, 14h21
nadeau-1

Merci pour le truc sur BCP. Je l'utiliserai lorsque nécessaire. Puisque l'export
que je veux faire doit être "en temps réel" de façon automatique. D'où la raison d'utiliser un trigger car c'est la façon la plus synchrone avec la base de donnée.

L'exportation deoit être synchrone puisque il s'agit d'un export vers un système ERP qui doit traiter des transaction vs des commandes en temps réel.

J'ai finilement créé mon trigger sur le INSERT et UPDATE de ma table détail.
Et seulement sur le UPDATE de ma table TRANSACTION. Le résultat est correct sauf que mon fichier contient des lignes dupliqués. Mais je crois que c'est le plus proche de ce que je veux avoir. Je crois maintenant que je vais mem tourner vers une application .net en background qui esporte aussitôt mon fichier de transaction créé (basé sur un Trigger Insert et Update de la table Transaction).