Trigger to update modification date date in firebird
I have a client table with the fields of Registration date of the registry and Registration modification date would anyone know how to create a trigger that updates the column of modification date of this client table every time a row is changed, so far I 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 ???? is a parameter that I can't put correctly.
3 answers
As per Julio's statement, running a statement from update on the trigger is not a very good idea. If this was allowed in your case you would enter loop trying to update the date of the last modification. That said, You can always use a syntax by modifying the value of the variable 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 if the DATA_MODIFICACAO is not filled. Using this if the user can log in with a modification date manually if desired. If this is not desirable (i.e., you want DATA_MODIFICACAO to be replaced by current_date) just remove if.
Source: The Firebird FAQ - How to get the timestamp of last record change?
Good Guys, thanks to tip from friend Anthony Accioly, I managed to solve my problem, I believe 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 this the if checks if the modification date in the record update is equal to the current date, if the expression is true it means that the record has already been modified today, then it ignores the statement otherwise it updates the modification date to the current
You will not be able to do this because the DBMS itself will prevent it. If you could, it would create an infinite loop!