CREATE TABLE STATS (SID NUMBER(4), FNAME VARCHAR(100), LNAME VARCHAR2(100), YOB NUMBER(4), HEIGHT NUMBER(5,2), WEIGHT NUMBER(5,2), CITY VARCHAR2(100), STATE VARCHAR2(100));
/
INSERT INTO STATS VALUES (1022,'John','Doe',1974,180,185,'Eagle','Alaska');
INSERT INTO STATS VALUES (1045,'Will','Smith',1984,190,220,'San Diego','California');
INSERT INTO STATS VALUES (1123,'Matt','Ferguson',1968,182,179,'Derby','Kansas');
INSERT INTO STATS VALUES (1205,'Bill','Farm',1977,175,165,'Sanford','Maine');
/
CREATE TABLE STATS_RESULT (SID NUMBER(4), LNAME VARCHAR2(100), YOB NUMBER(4), HEIGHT NUMBER(5,2), WEIGHT NUMBER(5,2), INS_RISK NUMBER(5,2), CALC_RISK NUMBER(5,2));
/
INSERT INTO STATS_RESULT VALUES (1022,'Doe',1974,180,185,22.5,34.52);
INSERT INTO STATS_RESULT VALUES (1045,'Smith',1984,190,220,42.87,55.23);
INSERT INTO STATS_RESULT VALUES (1123,'Ferguson',1968,182,179,65,54.02);
INSERT INTO STATS_RESULT VALUES (1205,'Farm',1977,175,165,33.54,19);
/
COMMIT;

-- Create the CDC (Change Data Capture) table
CREATE TABLE CDC_STATS
(
  SID          NUMBER(4)                        NOT NULL,
  LNAME        VARCHAR2(100 BYTE)               NOT NULL,
  YOB          NUMBER(4)                        NOT NULL,
  ACTION_DATE  DATE                             NOT NULL,
  ACTION       VARCHAR2(1 BYTE)                 NOT NULL
);


CREATE UNIQUE INDEX CDC_STATS_PK ON CDC_STATS(SID, LNAME, YOB);
CREATE INDEX CDC_STATS_I1 ON CDC_STATS(ACTION_DATE);

ALTER TABLE CDC_STATS ADD (
  CONSTRAINT CDC_STATS_PK
  PRIMARY KEY
  (SID, LNAME, YOB)
  USING INDEX CDC_STATS_PK
  ENABLE VALIDATE);

-- Attach the auditing trigger to the table

CREATE OR REPLACE TRIGGER Cdc_stats_t1
    AFTER DELETE OR INSERT OR UPDATE
    ON Stats
    REFERENCING NEW AS New OLD AS Old
    FOR EACH ROW
DECLARE
    Lcaction   VARCHAR2 (1);
    Lnsid      NUMBER (4);
    Lnlname    VARCHAR2 (100);
    Lnyob      NUMBER (4);
BEGIN
    -- Handle the primary key being modified
    IF     UPDATING
       AND (   :old.Sid <> :new.Sid
            OR :old.Lname <> :new.Lname
            OR :old.Yob <> :new.Yob)
    THEN
        UPDATE Cdc_stats A
           SET A.Action = 'D', A.Action_date = SYSDATE
         WHERE A.Sid = :old.Sid AND A.Lname = :old.Lname AND A.Yob = :old.Yob;

        -- if the original row isn't in the CDC then insert it so it can be deleted
        IF SQL%ROWCOUNT = 0
        THEN
            INSERT INTO Cdc_stats (Sid,
                                   Lname,
                                   Yob,
                                   Action_date,
                                   Action)
                 VALUES (:old.Sid,
                         :old.Lname,
                         :old.Yob,
                         SYSDATE,
                         'D');
        END IF;

        Lcaction := 'I';
        Lnsid := :new.Sid;
        Lnlname := :new.Lname;
        Lnyob := :new.Yob;
    ELSIF INSERTING
    THEN
        Lcaction := 'I';
        Lnsid := :new.Sid;
        Lnlname := :new.Lname;
        Lnyob := :new.Yob;
    ELSIF UPDATING
    THEN
        Lcaction := 'U';
        Lnsid := :new.Sid;
        Lnlname := :new.Lname;
        Lnyob := :new.Yob;
    ELSIF DELETING
    THEN
        Lcaction := 'D';
        Lnsid := :old.Sid;
        Lnlname := :old.Lname;
        Lnyob := :old.Yob;
    END IF;

    -- store the CDC information
    UPDATE Cdc_stats A
       SET A.Action = Lcaction, A.Action_date = SYSDATE
     WHERE A.Sid = Lnsid AND A.Lname = Lnlname AND A.Yob = Lnyob;

    -- if the original row isn't in the CDC then insert
    IF SQL%ROWCOUNT = 0
    THEN
        INSERT INTO Cdc_stats (Sid,
                               Lname,
                               Yob,
                               Action_date,
                               Action)
             VALUES (Lnsid,
                     Lnlname,
                     Lnyob,
                     SYSDATE,
                     Lcaction);
    END IF;
END Cdc_stats_t1;
/