Oracle Database Discussions

Hi to all respected gurus.

Once again I am here on this platform with my new problem, related to "BEFORE UPDATE TRIGGER".

I want to track the every update in "FFL_ITA_DMAS" table, into a separate table.

I've created following script but whenever I do update any field in "FFL_ITA_DMAS", two entries are being inserted into tracking table. One is with old data and second is with new data.

Please help.

---*******************************************************************************************************************************************************

CREATE TABLE FFL_ITA_DMAS_AUDIT
(
AUDIT_ID NUMBER,
DMASID NUMBER,
OLD_EMPNAME VARCHAR2(100),
NEW_EMPNAME VARCHAR2(100),
OLD_DEPARTMENT VARCHAR2(50),
NEW_DEPARTMENT VARCHAR2(50),
OLD_MACHINENAME VARCHAR2(15),
NEW_MACHINENAME VARCHAR2(15),
OLD_REMARKS VARCHAR2(100),
NEW_REMARKS VARCHAR2(100),
OLD_EMPID NUMBER,
NEW_EMPID NUMBER,
OLD_FGIFRECEIVED CHAR(1),
NEW_FGIFRECEIVED CHAR(1),
OLD_FGIFSENT CHAR(1),
NEW_FGIFSENT CHAR(1),
OLD_TYPEID NUMBER,
NEW_TYPEID NUMBER,
OLD_UPDATE_BY VARCHAR2(25),
NEW_UPDATE_BY VARCHAR2(25),
OLD_UPDATE_DATE DATE,
NEW_UPDATE_DATE DATE,
CONSTRAINT PK_FFLITADMASAUDIT_AUDITID PRIMARY KEY (AUDIT_ID)
);

CREATE SEQUENCE FFL_ITADMAS_AUDIT_SEQ
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;

CREATE OR REPLACE TRIGGER FFLITADMASAUDIT_BEFORE_UPDATE
BEFORE UPDATE ON FFL_ITA_DMAS
FOR EACH ROW
BEGIN
-- Insert record into FFL_ITAINV_AUDIT table
INSERT INTO FFL_ITA_DMAS_AUDIT
(DMASID,
OLD_EMPNAME, NEW_EMPNAME,
OLD_DEPARTMENT, NEW_DEPARTMENT,
OLD_MACHINENAME, NEW_MACHINENAME,
OLD_REMARKS, NEW_REMARKS,
OLD_EMPID, NEW_EMPID,
OLD_FGIFRECEIVED, NEW_FGIFRECEIVED,
OLD_FGIFSENT, NEW_FGIFSENT,
OLD_TYPEID, NEW_TYPEID,
OLD_UPDATE_BY, NEW_UPDATE_BY,
OLD_UPDATE_DATE, NEW_UPDATE_DATE)
VALUES
(:OLD.DMAS_ID,
:OLD.EMP_NAME, :NEW.EMP_NAME,
:OLD.DEPARTMENT, :NEW.DEPARTMENT,
:OLD.MACHINE_NAME, :NEW.MACHINE_NAME,
:OLD.REMARKS, :NEW.REMARKS,
:OLD.EMP_ID, :NEW.EMP_ID,
:OLD.FGIF_RECEIVED, :NEW.FGIF_RECEIVED,
:OLD.FGIF_SENT, :NEW.FGIF_SENT,
:OLD.TYPE_ID, :NEW.TYPE_ID,
:OLD.LAST_UPDATE_BY, :NEW.LAST_UPDATE_BY,
:OLD.LAST_UPDATE_DATE, :NEW.LAST_UPDATE_DATE);
END;
--**************************************************************************************************************************************************