Is it possible to compare the OLD and NEW value in FOR-LOOP within a trigger

Question:

I have to log all modifications made by the user at the field level with a timestamp and user ID. I could write a lot of hard-coded triggers. But is it possible in Oracle to iterate on the table structure and compare the :OLD and :NEW value in for-loop within a trigger?

Answer:

Sure, there is a way to do this, but there's nothing very elegant about it; you will need to write (or generate) a loop to perform these checks. :OLD and :NEW are "pseudo-records," which means they have the same structure as a record based on the table from which the trigger was fired, but they do not share all the properties and features of real records. Specifically, they cannot be passed as arguments to programs. The bottom line is that you will need to put code like this in your trigger:
IF ( :OLD.EMPLOYEE_ID != :NEW.EMPLOYEE_ID
OR (:OLD.EMPLOYEE_ID IS NULL AND :NEW.EMPLOYEE_ID IS NOT NULL)
OR (:OLD.EMPLOYEE_ID IS NOT NULL AND :NEW.EMPLOYEE_ID IS NULL))
THEN
-- Logic to handle changed value here...
END IF;

You could also choose an alternative Boolean expression as follows:
IF (:OLD.EMPLOYEE_ID = :NEW.EMPLOYEE_ID)
OR (:OLD.EMPLOYEE_ID IS NULL AND :NEW.EMPLOYEE_ID IS NULL)
THEN
NULL;
ELSE
-- Logic to handle changed value here...
END IF;
Of course, whichever approach you take, writing that sort of code for 25 columns can get very tedious very quickly, so I think your best bet is to generate the conditional logic you would need. I have added to the download page a utility procedure named genoldnewcompare that will generate IF statements like the ones you see above to help you get the job done. Here is an example of calling that procedure:
BEGIN
genoldnewcompare (tab_in => 'EMPLOYEE'
,sch_in => USER
,display_in => TRUE
,file_in => NULL
,dir_in => NULL
);
END;
/