In Trigger we have INSERTED and DELETED. Why not "UPDATED"?

Question

Example scenario:

I have the table/fields:

• documentos
  • id
  • documento
  • pessoa
  • item
  • qtd
  • valor

The system gives INSERT and then UPDATE on this table in a continuous sequence (which would not come to the case).

What I’d like is:

When you do the UPDATE, check some parameters and if the condition is true, the Trigger do another UPDATE afterward.

What I did:

ALTER TRIGGER [dbo].[TG_TEST1] 
   ON  [CORP].[dbo].[DOCUMENTOS] 
   AFTER UPDATE

AS

    DECLARE @DOC_ID INT
    DECLARE @DOC_QTD FLOAT

    SELECT TOP 1 @DOC_ID = ID, @DOC_QTD = QTD
    FROM dbo.DOCUMENTOS
    WHERE PESSOA = 3
    AND ITEM = 1
    ORDER BY ID DESC

    IF (@DOC_ID IS NOT NULL)

BEGIN

    SET NOCOUNT OFF;

    UPDATE dbo.DOCUMENTOS
    SET QTD = (@DOC_QTD * 1000)
    WHERE ID = @DOC_ID

END

---

Problem:

The problem is that I believe SELECT TOP 1 is causing some internal problem in the application (which should not be posted, as it is a specific application).

I saw you have the INSERTED and DELETED, but there is no UPDATED that I can already pick up the record that was changed.

---

Doubt:

I’d like to know, how would I get the ID of the record being made the UPDATE, in addition to other data to filter some values and make the condition whether it will be realized the other UPDATE or not?

Answer 1

If the Trigger for INSERT, INSERTED will have records and DELETED will be empty

If the Trigger for DELETE, INSERTED will be empty and DELETED will have records

If the Trigger for UPDATE, INSERTED and DELETED will have records

In the cases of Updates, the old information (pre-change) will be inside the DELETED table, the new information (changed) will be inside INSERTED.

In his Trigger you can do so:

IF EXISTS(SELECT ID FROM DELETED)
BEGIN
    IF EXISTS(SELECT ID FROM INSERTED )
    BEGIN
        /* é um UPDATE */     
    END
    ELSE
    BEGIN
        /* é um DELETE */     
    END
END
ELSE
BEGIN
    IF EXISTS(SELECT ID FROM INSERTED )
    BEGIN
        /* é um INSERT */     
    END
END

Another way that I also use is to play first for a variable which action is occurring, example:

SET @ACTION = 'I'; -- DEFINE INSERT POR PADRÃO
IF EXISTS(SELECT * FROM DELETED)
BEGIN
    SET @ACTION = 
        CASE
            WHEN EXISTS(SELECT * FROM INSERTED) THEN 'U' -- FOI UM UPDATE
            ELSE 'D' -- FOI UM DELETE
        END
END

IF @ACTION = 'I'
BEGIN           
--CÓDIGO INSERT
END

IF @ACTION = 'U'
BEGIN           
--CÓDIGO UPDATE
END

IF @ACTION = 'D'
BEGIN           
--CÓDIGO DELETE
END

Edit

As seen by José Diz, the procedure is of type AFTER UPDATE, so this indicates that it will always be an UPDATE

Answer 2

(...) how I would take the ID of the record being made the UPDATE, and other data to filter some values and make the condition whether the other UPDATE will be performed or not

Since the [TG_TEST1] trigger procedure is declared as AFTER UPDATE, then it is certain that it will only be triggered in cases where there is UPDATE instruction for the CORP.dbo.DOCUMENTS table. In this case, the virtual tables INSERTED and DELETED are available, in a 1:1 relation between them. In the first (INSERTED) will be the images of the lines that have been changed, after the UPDATE execution; in the second (DELETED) the images of the lines changed, but before the UPDATE execution.

It is necessary to be aware that a trigger procedure can be triggered to treat none, one or more lines. The first step is to check that there is not a single line changed. Here is a way:

...
-- verifica se houve alteração
IF not exists (SELECT * from INSERTED)
  return;
...

For this above check either test the virtual table INSERTED or DELETED, because between them there is a 1:1 ratio when the trigger procedure is only of type AFTER UPDATE.

The rest of the code should be constructed considering that there may be more than one altered line. You must then access the DELETED and/or INSERTED tables to get which rows were changed in the associated table.

Just as an example, here’s a code derived template posted on the topic:

...
UPDATE Doc
    set QTD = Ins.QTD * 1000
    from INSERTED as Ins
         inner join DELETED as Del on Ins.ID = Del.ID
         inner dbo.DOCUMENTOS as Doc on Doc.ID = Ins.ID
    where ... 
...

In the WHERE clause filters should be added.

Put the pieces together, you get something like:

-- código #1 v2
CREATE TRIGGER dbo.TG_TEST1 
     on CORP.dbo.DOCUMENTOS 
     after UPDATE as

begin
-- verifica se houve alteração
IF not exists (SELECT * from INSERTED)
  return;

--
UPDATE Doc
    set QTD = Ins.QTD * 1000
    from INSERTED as Ins
         inner join DELETED as Del on Ins.ID = Del.ID
         inner dbo.DOCUMENTOS as Doc on Doc.ID = Ins.ID
    where ... ;

end;
go

---

Reading suggestion:
- article Traps in trigger procedure programming (Trigger)

Answer 3

I found the question very curious and went to research. That link was what made the most sense to me.

The idea here is to create a way to control the changed file(s) (s) (variable/table) and then return your(s) id(s) (for example). I understood then that one can use something like this:

DECLARE @alteracoes table([id_dept] int, [nome_antigo] varchar(50), [nome_novo] varchar(50));

UPDATE departamento
SET nome = 'nome_teste_novo'
OUTPUT id as id_dept, nome as nome_antigo, 'nome_teste_novo' as nome_novo
INTO @alteracoes
WHERE nome = 'nome_teste'

UPDATE dep
SET dep.nome = alt.nome_novo
FROM departamento as dep
INNER JOIN @alteracoes as alt ON dep.id = alt.id_dept

--aqui, @alteracoes contém os itens alterados
--select id_dept from @alteracoes