Condition in Trigger

Asked

Viewed 477 times

0

About the following Rigger:

CREATE TRIGGER [dbo].[TG_TESTE] 
   ON  [MINHABASE].[dbo].[TB_DOCUMENTOS] 
   AFTER UPDATE
AS 

DECLARE @ID INT
DECLARE @DOC INT
DECLARE @QTD FLOAT

SELECT TOP 1 @ID = ID, @DOC = DOCUMENTO, @QTD = QUANTIDADE
FROM dbo.TB_DOCUMENTOS 
WHERE OPERACAO = 177
AND STATUS = 1
ORDER BY ID DESC

-- CONDICAO

BEGIN

SET NOCOUNT ON;

UPDATE dbo.TB_DOCUMENTOS
SET PESO = 1000
WHERE ID = @ID

END

In the query, I filter and define the variables @ID, @DOC, @QTD.

When that query DOES NOT RETURN a record, Trigger will run the update within the BEGIN, even if I don’t have a condition above ?

sql sql-server

2 answers

1


You can use a conditional to ensure the integrity of the code, in this case it will not perform the update but will try, which will generate a process...

IF @ID != '' THEN
UPDATE dbo.TB_DOCUMENTOS
  SET PESO = 1000
  WHERE ID = @ID
END IF

  • He will try to update anything then !? Better I put the conditions of WHERE in Trigger’s IF then !?

  • select will be done anyway, even more streamlined with Where. the update Where is needed.

  • select yes. Ok. The update will be executed if it brings no record of the select (as you said in the reply), so he tries to apply a update in no record ? It will not affect anything, but it will run !?

0

In the sql-server you can use IF, that is, the structure of proc can be maintained, and where you left "reserved" for -- CONDICAO, between the following:

IF @ID IS NOT NULL
BEGIN
   ...
END

  • the part of making the condition, quiet, the question is: The update will be executed if it doesn’t bring any record of select ? If yes, then it tries to apply an update to any record ? It will not affect anything, but it will run !?

  • 1

    If you do the validation before, it does not perform, since it does not enter the block. But in the matter of processing, I don’t know how sql-server proceeds when trying a update "in no way"

Browser other questions tagged sql sql-server

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