How to look at the field that is being changed in a Procedure?

Asked

Viewed 48 times

0

I created a precedent where after running or updating in a table the flag is marked S automatically, causing this item to go up to an integration and integration after the return of success mark the same flag as N, but the problem is that I ended up falling into a loop with this, every time this table undergoes changes by integration and ends up marking the flag as N to Procedure comes and marks as S, because it is looking at the table and not a specific field.

You can only look at the field that has changed, in the case @price?

Below is an excerpt from my code, I would like to look only for changes changed in the @price field, it is possible?

IF @P_TIPOEVENTO = @AFTER_UPDATE
 
BEGIN

  SET @CODPROD = SANKHYA.EVP_GET_CAMPO_INT(@P_IDSESSAO, 'CODPROD') 
  SET @NUTAB  = SANKHYA.EVP_GET_CAMPO_INT(@P_IDSESSAO, 'NUTAB')  
  
    SELECT  @PRICE=VLRVENDA,
            @AD_ENVIAMKTPLACE=ISNULL(AD_ENVIAMKTPLACE,'N')  
    FROM    TGFEXC   
    WHERE   CODPROD= @CODPROD  AND NUTAB = @NUTAB     


    IF @AD_ENVIAMKTPLACE = 'N'
          BEGIN
          UPDATE TGFEXC SET AD_ENVIAMKTPLACE = 'S' WHERE @CODPROD = @CODPROD AND NUTAB = @NUTAB
          END

   END   ```
sql sql-server

  • Hello Gabriel, would this query be the "integration"? I could not understand the relationship of your code with the field you specified "@price/@price".

  • 1

    Use a third letter to indicate that it has been integrated. The cycle can be N -> S -> I, where I means S and that it has been integrated.

  • i would make a table in memory with only the records I should treat at that time, and would do an update later with a Join involving only them, thus ensures the status before the update.

  • 1

    I would do it by Trigger , as it would have control of the affected columns , however by Procedure if it has this control too , as is this call ?

  • I created a Rigger and it worked.

1 answer

1


Can be done using the function Update.

Example:

if Update(VLRVENDA)
BEGIN
// Seu código
END;

More information on official documentation.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.