A trigger that triggers a change in a specific field Firebird 3.0

Question

SET TERM ^ ;
CREATE TRIGGER ADDPWDHASHHISTORY FOR USERS ACTIVE
AFTER INSERT OR UPDATE POSITION 3
AS 
BEGIN
    
    INSERT INTO USERSPWDHASHHISTORY 
    (id,
    USERID, 
     FIO, 
     PWDHASH,
     ATTIME)
     values (gen_id(GEN_PWDHASHHIS_ID,1) , new.id, new.fio, new.pwdhash, CURRENT_TIMESTAMP);
END^
SET TERM ; ^

This trigger is triggered to change any field in the USERS table. I need to change it, to trigger only on changing the PWDNASH field. Please help

Answer 1

A trigger on insert or update will fire for any insert or update on the table. If you want to have conditional behaviour on specific fields, you will need to do that in the trigger body.

In this case, you will need to do something like:

SET TERM ^ ;
CREATE TRIGGER ADDPWDHASHHISTORY FOR USERS ACTIVE
AFTER INSERT OR UPDATE POSITION 3
AS 
BEGIN
    if (inserting or new.PWDHASH is distinct from old.PWDHASH) then
        INSERT INTO USERSPWDHASHHISTORY 
        (id,
        USERID, 
         FIO, 
         PWDHASH,
         ATTIME)
         values (gen_id(GEN_PWDHASHHIS_ID,1) , new.id, new.fio, new.pwdhash, CURRENT_TIMESTAMP);
END^
SET TERM ; ^

That is, the inserting condition will be true when the trigger was fired for an insert, while the new.PWDHASH is distinct from old.PWDHASH checks if the value has changed in an update. You could also use the condition inserting or updating and new.PWDHASH is distinct from old.PWDHASH, but that is not really necessary, so I opted for the shorter version.

See also:

• INSERTING
• IS [NOT] DISTINCT FROM
• OLD and NEW Context Variables