asked on

A UNIQUE TRIGGER ON TWO COLUMNS - ORACLE PL/SQL PROGRAMMING

Good evening!
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
OF REPSTA_SFTP_PASSWORD
ON MASTER.NEREPSTA_CONFIG
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
:NEW.repsta_sftp_password := cripta (:NEW.repsta_sftp_password);
END;
/

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.

Any clues??

ASKER CERTIFIED SOLUTION

MikeOM_DBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CRISTIANO_CORRADI

ASKER

Thanks for your help: I've modified your source code as follows.

500 points for you ;-)

CREATE OR REPLACE TRIGGER MASTER.repsta_source
   BEFORE INSERT OR UPDATE OF repsta_sftp_password, ngneer_sftp_password
   ON MASTER.nerepsta_config
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
BEGIN
   IF INSERTING
   THEN
      BEGIN
         :NEW.repsta_sftp_password := cripta (:NEW.repsta_sftp_password);
         :NEW.ngneer_sftp_password := cripta (:NEW.ngneer_sftp_password);
      END;
   END IF;
 
   IF UPDATING ('REPSTA_SFTP_PASSWORD')
   THEN
      :NEW.repsta_sftp_password := cripta (:NEW.repsta_sftp_password);
   END IF;
 
   IF UPDATING ('NGNEER_SFTP_PASSWORD')
   THEN
      :NEW.ngneer_sftp_password := cripta (:NEW.ngneer_sftp_password);
   END IF;
END;
/

Open in new window

CRISTIANO_CORRADI

ASKER

Thank you very much for your fast, kind help ;-)