Maintain history with a trigger

It is very easy to create a trigger history table without modifying the applications inserting/updating or deleting from the core table. In my case, I had to maintain a history of MAIN_ORDER_PROCESS_PARAMS table without modifying the application. MAIN_ORDER_PROCESS_PARAM_HIST is my slave (history) table which silently copies records on update/ delete from the code table.

It is important to note that DATE_CHANGED should be made a primary key in the history table. Also, another important point is that the insert/update to core table should also update DATE_CHANGED.

You may modify the procedure to also capture current record or inserts.

DROP TRIGGER ARCHIVE_MOPP;
CREATE OR REPLACE TRIGGER ARCHIVE_MOPP
  BEFORE DELETE OR UPDATE
  ON MAIN_ORDER_PROCESS_PARAMS
  REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
MESSAGE VARCHAR2 (1000) := ' ';
/******************************************************************************
NAME: ARCHIVE_MOPP
PURPOSE: To record the changes to the MAIN_ORDER_PROCESS_PARAMS table
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 09/16/2009 VMS 1. Created this trigger.
******************************************************************************/
BEGIN
INSERT INTO MAIN_ORDER_PROCESS_PARAM_HIST (ORDER_NUM, PARAM_NAME, PARAM_VALUE, USERNAME, DATE_CHANGED
)
   VALUES (:NEW.ORDER_NUM, :NEW.PARAM_NAME, :NEW.PARAM_VALUE, :NEW.USERNAME, :NEW.DATE_CHANGED
);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    ROLLBACK;
  WHEN OTHERS THEN
    ROLLBACK;
  MESSAGE := MESSAGE || 'SQLCODE: ' || SQLCODE || ' SQLERRM:' || SUBSTR (SQLERRM, 1, 100); 
  BMS_OUTPUT.put_line ('ERROR: ' || MESSAGE);
  html_email ('email address','ARCHIVE_MOPP','Error encountered in ARCHIVE_MOPP trigger','Error encountered in ARCHIVE_MOPP trigger',MESSAGE); 
  /*I have it set to send email to our group if errors are encountered*/
RAISE;
END ARCHIVE_MOPP;
/