In trigger we have INSERTED, DELETED, BUT and "UPDATED"?

4

Example scenario:

I have the table / fields:

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

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

What would you like to see:

When you do UPDATE , check some parameters and if the condition is true, the trigger makes another UPDATE after.

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 that SELECT TOP 1 is causing some internal problem in the application (which should not be post, since it is a specific application).

I saw that it has INSERTED and DELETED , but there is no UPDATED that I can already get the record that has changed.

Doubt:

I'd like to know, how would I get the ID of the record being done UPDATE , plus other data to filter some values and make the condition if the other UPDATE or not?

    
asked by anonymous 28.08.2018 / 15:27

3 answers

2
   / p>

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

You must be aware that a trigger procedure can be triggered to treat none, one or more lines. The first step is to check if there is not a single row changed. Here's a way:

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

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

The remainder of the code must be constructed considering that more than one row may exist. You should then access the DELETED and / or INSERTED tables to see which rows have changed in the associated table.

Just as an example, here is a template derived from the code posted in 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 you should add the filters.

Putting together the parts, we have 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

Suggested Reading:
 - Article Traps in Programming Trigger Procedures    ( trigger )

    
28.08.2018 / 16:43
4

If trigger is INSERT, INSERT will have records and DELETED will be empty

If trigger is DELETE, INSERTED will be empty and DELETED will have

If trigger is UPDATE, INSERTED and DELETED will have records

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

In your trigger you can do this:

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 I also use is to first play to a variable which action is occurring, eg

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, logo this indicates that it will always be an UPDATE

    
28.08.2018 / 15:42
1

I found the question very curious and went to search. This link made the most sense to me.

The idea here is to create a way to control the changed file (s) (variable / table) and then return your id (s) (for example). I understood then that you 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
    
28.08.2018 / 15:48