-- base table to track changes to
create table xyz (
  xyz_id        number primary key,
  xyz_col       varchar2(10),
  DATE_CREATED  DATE                            NOT NULL,
  CREATED_BY    VARCHAR2(30)                    NOT NULL,
  DATE_CHANGED  DATE                            NOT NULL,
  CHANGED_BY    VARCHAR2(30)                    NOT NULL
);

-- ddl to track history on XYZ
CREATE SEQUENCE XYZ_H_S;

CREATE TABLE XYZ_H (
   XYZ_H_ID                        NUMBER              NOT NULL,
   XYZ_ID                          NUMBER              NOT NULL,
   XYZ_COL                         VARCHAR2(10)        ,
   DATE_CREATED                    DATE                NOT NULL,
   CREATED_BY                      VARCHAR2(30)        NOT NULL,
   DATE_CHANGED                    DATE                NOT NULL,
   CHANGED_BY                      VARCHAR2(30)        NOT NULL,
   DELETE_IND                      VARCHAR2(1)         NOT NULL);
--
ALTER TABLE XYZ_H
ADD CONSTRAINT XYZ_H_PK PRIMARY KEY (XYZ_H_ID);
--
CREATE OR REPLACE TRIGGER xyz_h
   AFTER UPDATE OR DELETE ON xyz FOR EACH ROW
BEGIN
   IF DELETING THEN
      INSERT INTO xyz_h
                  (xyz_h_id, xyz_id, xyz_col, date_created, created_by,
                   date_changed, changed_by, delete_ind
                  )
           VALUES (xyz_h_s.NEXTVAL, :OLD.xyz_id, :OLD.xyz_col, :OLD.date_created, :OLD.created_by,
                   :OLD.date_changed, :OLD.changed_by, 'Y'
                  );
   ELSE
      INSERT INTO xyz_h
                  (xyz_h_id, xyz_id, xyz_col, date_created, created_by,
                   date_changed, changed_by, delete_ind
                  )
           VALUES (xyz_h_s.NEXTVAL, :OLD.xyz_id, :OLD.xyz_col, :OLD.date_created, :OLD.created_by,
                   :OLD.date_changed, :OLD.changed_by, 'N'
                  );
   END IF;
END;
/