A UNIQUE TRIGGER ON TWO COLUMNS - ORACLE PL/SQL PROGRAMMING
Posted on 2008-10-13
In a table called NEREPSTA_CONFIG, I have a column called REPSTA_SFTP_PASSWORD (type VARCHAR2), on which I've built the following trigger:
CREATE OR REPLACE TRIGGER MASTER.REPSTA_SOURCE
BEFORE INSERT OR UPDATE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
:NEW.repsta_sftp_password := cripta (:NEW.repsta_sftp_password);
So, when a new record is inserted, on commit the NEREPSTA_SFTP_PASSWORD column is encrypted with che "CRIPTA" function. Moreover, when the field NEREPSTA_SFTP_PASSWORD is updated, on commit it's encrypted with the same "CRIPTA" function.
Now please suppose to ALTER the NEREPSTA_CONFIG table, adding a new column called NGNEER_SFTP_PASSWORD: I would like to modify the trigger in order to use the CRIPTA stored function ONLY on the field updated or inserted, not on both fields inconditionally: only the modified field has to be cripted with the CRIPTA function. Of course, if you INSERT a NEW record, both the fields NEREPSTA_SFTP_PASSWORD and NGNEER_SFTP_PASSWORD has to be crypted.