Trigger to update modification date in Firebird

Asked

Viewed 855 times

1

I have a table of clients with the fields of Registration Date of the Registry and Date of Registry Modification someone would know how to create a trigger updating the column of Date of Modification of that customer table every time a row is changed, so far I’ve thought of something like this here:

CREATE trigger TBCLIENTE_DATAMODIFICACAO for TB_CLIENTE
active before update position 0
AS
begin
  update TB_CLIENTE set TB_CLIENTE.DATA_MODIFICACAO = current_date where TB_CLIENTE.CODIGO = ????;
end

Where the ???? is a paramêtro that I can not put properly.

firebird trigger

3 answers

2

According to Julio’s statement, execute a statement of update in the Trigger is not a very good idea. If this were allowed in your case you would get into loop trying to update the last modification date. That said, you can always use a syntax modifying the variable value new in the Trigger:

CREATE trigger TBCLIENTE_DATAMODIFICACAO for TB_CLIENTE
active before update position 0
AS
begin
  if (new.DATA_MODIFICACAO is null)
    then new.DATA_MODIFICACAO = current_date;
end

See that I used a if to check whether the DATA_MODIFICACAO is not filled in. Using this if the user can enter a modified date manually if desired. If this is not desirable (i.e., you want the DATA_MODIFICACAO is replaced by current_date) just remove the if.

Source: The Firebird FAQ - How to get the timestamp of last record change?

  • I will try to do a similar procedure here, changing the situation and will soon give feedback

1


Good people, thanks to the tip of friend Anthony Accioly, I managed to solve my problem, I believe that my strategy worked well with the following code:

CREATE trigger TBCLIENTE_DATAMODIFICACAO for TB_CLIENTE
active before update position 0
AS
begin
  if (new.DATA_MODIFICACAO = CURRENT_DATE) then
    begin
      /*NÃO FAZ NADA PORQUE A DATA FOI ATUALIZADA*/
    end
  else
    begin
      update TB_CLIENTE set TB_CLIENTE.DATA_MODIFICACAO = CURRENT_DATE where TB_CLIENTE.CODIGO = new.CODIGO;
    end
end

With that the IF checks whether the Date of Modification in the record update is equal to the current date, if the expression is true means that the record has already been modified today, then it ignores the statement otherwise it updates the modification date to the current date

  • Lima, I still think that setar new.DATA_MODIFICACAO is a better strategy than the update. This avoids rotating the Trigger to do nothing a second time.

  • Could you give me a practical example Antony?

  • My answer is the practical example. The only difference in your case is that you should change the if (new.DATA_MODIFICACAO is null) for if (new.DATA_MODIFICACAO <> CURRENT_DATE). This way you always persist the current date without going twice through the Trigger (That is, without making a update extra).

-1

You cannot do this because sgbd itself will prevent it. If you could, it would create an infinite loop!

  • Humm... This statement is not entirely true. Surely run a statement of update would create a loop. On the other hand, as this is a Rigger before update we can simply exchange the value of new.

Browser other questions tagged firebird trigger

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