SQL Server - Trigger does not execute after a given field

Question

SQL Server - Trigger does not execute after a given field

Navigation

#1 by (1 votes)

0

I have the trigger below:

CREATE TRIGGER AtualizaDataAlteracaoSegmentos
ON dbo.Segmentos
AFTER INSERT,UPDATE  
AS
BEGIN 
    Begin tran  
      update Segmentos 
      set DataAlteracao = GETDATE()
       where Id = (select ID from inserted);

    Commit;
END;

Is there any way I can update this field without activating the trigger again?

In Oracle I know I could do in BEFORE and instead of giving the update in this way assign the value of NEW.dataChange however in SQL Server I do not know how to proceed.

sql sql-server trigger

asked by anonymous 17.07.2018 / 19:16

1 answer

reactphp - Undefined index Mask Currency Input readonly

score 1 · Accepted Answer

First you can disable TRIGGERS recursion, how bad this is done at the database level:

ALTER DATABASE NomeDoBanco SET RECURSIVE_TRIGGERS OFF

Documentation: RECURSIVE_TRIGGERS

Second , you can treat in TRIGGER if the field is no longer being updated, like this:

IF NOT UPDATE(DataAlteracao) 
BEGIN
   update Segmentos 
      set DataAlteracao = GETDATE()
    where Id = (select ID from inserted);
END

Documentation: UPDATE ()

Third , you can still check if the update is not coming from a TRIGGER. For this, you can check the value of the TRIGGER_NESTLEVEL function, which returns the nesting level of the triggers. If returned, zero means it was not triggered by a TRIGGER:

IF TRIGGER_NESTLEVEL() <= 1
BEGIN
   update Segmentos 
      set DataAlteracao = GETDATE()
   where Id = (select ID from inserted);
END

Documentation: TRIGGER_NESTLEVEL

Some of these solutions should suit you