Trigger avec variable dans une requete
Bonjour à tous,
Je suis un problème de syntaxe dans mon trigger. Voici le code
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
| BEGIN
declare @PINCODE as varchar(10);
declare @PCVISVALIDE as varchar(10);
declare @PCVNUM as varchar(50);
declare @shell as varchar(8000);
declare ins_cur cursor FAST_FORWARD for (select PCVNUM from inserted);
declare @pcvnumcde as varchar(50);
set @pcvnumcde = (SELECT PCVNUM from inserted);
declare @numdevis as varchar(50);
set @numdevis = (SELECT TOP (1) Entete_Devis.PCVNUM AS Numdevis FROM PIECEVENTELIGNES AS Ligne_Devis INNER JOIN
PIECEVENTES AS Entete_Devis ON Ligne_Devis.PCVID = Entete_Devis.PCVID INNER JOIN
PIECEVENTELIGNES AS Ligne_Cde ON Ligne_Devis.PLVID = Ligne_Cde.PLVIDORG INNER JOIN
PIECEVENTES AS Entete_CDE ON Ligne_Cde.PCVID = Entete_CDE.PCVID
where Entete_CDE.PCVNUM=''+@pcvnumcde+'');
IF UPDATE (PCVNUM)
BEGIN
SELECT @PINCODE=I.PINCODE, @PCVNUM=I.PCVNUM FROM INSERTED I;
IF @PINCODE = 'CDECLI'
set @shell = 'echo ' + @numdevis + ';1''>> "H:\Site Web\Out\orders_in\"accepted' + @numdevis + '.csv';
exec master..xp_cmdshell @shell;
end
end; |
Si j'exécute ce trigger, je n'obtiens pas de fichier dans mon dossier. Par contre, si je mets le code suivant, j'obtiens un fichier mais avec comme nom ma requête.
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
| BEGIN
declare @PINCODE as varchar(10);
declare @PCVISVALIDE as varchar(10);
declare @PCVNUM as varchar(50);
declare @shell as varchar(8000);
declare ins_cur cursor FAST_FORWARD for (select PCVNUM from inserted);
declare @pcvnumcde as varchar(50);
set @pcvnumcde = (SELECT PCVNUM from inserted);
declare @numdevis as varchar(50);
set @numdevis ='(SELECT TOP (1) Entete_Devis.PCVNUM AS Numdevis FROM PIECEVENTELIGNES AS Ligne_Devis INNER JOIN
PIECEVENTES AS Entete_Devis ON Ligne_Devis.PCVID = Entete_Devis.PCVID INNER JOIN
PIECEVENTELIGNES AS Ligne_Cde ON Ligne_Devis.PLVID = Ligne_Cde.PLVIDORG INNER JOIN
PIECEVENTES AS Entete_CDE ON Ligne_Cde.PCVID = Entete_CDE.PCVID
where Entete_CDE.PCVNUM='''+@pcvnumcde+')';
IF UPDATE (PCVNUM)
BEGIN
SELECT @PINCODE=I.PINCODE, @PCVNUM=I.PCVNUM FROM INSERTED I;
IF @PINCODE = 'CDECLI'
set @shell = 'echo ' + @numdevis + ';1''>> "H:\Site Web\Out\orders_in\"accepted' + @numdevis + '.csv';
exec master..xp_cmdshell @shell;
end
end; |
Pourriez vous m'aider s'il vous plait?
D'avance, merci.
Julien