0

I have a trigger associated to a table:

ALTER TRIGGER [dbo].[FECR_TRIMPRIME_RETENCIONES] ON some_table

The definition is this: 

ALTER TRIGGER [dbo].[FECR_TRIMPRIME_RETENCIONES] ON FECR_TRETENCIONES_IC
FOR UPDATE AS
   IF UPDATE (RET_PRINT)
   BEGIN
       DECLARE @statusOld varchar, @status varchar,
               @id int, @folioInt varchar(80),
               @cveRet varchar(5),@cmd varchar(8000), @args varchar(8000)

       SELECT @status = ISNULL(RET_PRINT,'NULL'),
              @id = ISNULL(RET_ID,0),
              @folioInt = ISNULL(RET_FOLIO_INT,'NULL'),
              @cveRet =  ISNULL(RET_CVE_RETENC,'NULL')              
       FROM inserted    

       PRINT @id
       PRINT @folioInt
       PRINT @cveRet

       IF (@status = '' OR @status = 'NULL')
       BEGIN
              PRINT 'No se puede generar la retencion, no es un estado válido.'
       END      

       SELECT @statusOld = RET_PRINT 
       FROM Deleted

       IF(@statusOld = 'N')
       BEGIN
            IF(@status = 'S')
            BEGIN
                    PRINT 'IMPRIME'
                    SET @args = '"'+CAST(@id AS VARCHAR(50))+'" "'+@cveRet+'" "'+@folioInt+'"'
                    --PRINT @args
                                SET @cmd = 'C:\ICCR\GRCR.exe '+@args
                    select @cmd
                                            --PRINT @cmd
                    EXEC master..xp_cmdshell @cmd 
                END
        END
    ELSE
        BEGIN
            PRINT 'La retencion ya fue impresa.'
        END
  END

And when I do an update, the process never ends and I have to manually stop the SQL Server Service.

My log file prints this line:

A severe error occurred on the current command. The results, if any, should be discarded.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Your trigger has **MAJOR** flaw in that you seem to assume it'll be called **once per row** - that is **not** the case. The trigger will fire **once per statement**, so if your `UPDATE` statements affects 25 rows, you'll get the trigger fired **once**, but then `Inserted` and `Deleted` will each contain 25 rows. Which of those 25 rows will your code selects? It's non-deterministic, and all other 24 rows are **ignored**. You need to rewrite your trigger to take this into account! – marc_s Feb 04 '15 at 05:50
  • I'm aware of it, and I make sure than whenever I do an update, I affect just one row. –  Feb 04 '15 at 16:13

0 Answers0