SQL Server - Trigger does not run after a given field

Asked

Viewed 505 times

0

I’ve got Trigger down: 

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 Trigger again?

In Oracle know what could do in BEFORE and instead of giving the update so assign the value of NEW.dataAlteracao however in SQL Server do not know how to proceed.

sql sql-server trigger

  • In the case of INSERT, simply declare the Date change column with the default value of CURRENT_TIMESTAMP. This already eliminates the need for the trigger procedure to treat AFTER INSERT.

1 answer

1


Yes, there is.
First you can disable recursiveness in TRIGGERS, the bad that this is done at the database level:

ALTER DATABASE NomeDoBanco SET RECURSIVE_TRIGGERS OFF

Documentation: RECURSIVE_TRIGGERS

According to, you can treat in TRIGGER if the field is no longer being updated, thus:

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 function value TRIGGER_NESTLEVEL, that returns the nesting level of the triggers. If returned zero means that 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

Any of these solutions should suit you

Browser other questions tagged sql sql-server trigger

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