oKBereitschaftsplanungBackend/src/main/resources/META-INF/sql/oracle/hist/BER_hist.sql

-- DROP HISTORY TABLES
DROP TABLE OPENK.ADDRESS_HIST;
DROP TABLE OPENK.BRANCH_HIST;
DROP TABLE OPENK.CALENDAR_HIST;
DROP TABLE OPENK.CONTACT_DATA_HIST;
DROP TABLE OPENK.INT_BRANCHES_FOR_GROUP_HIST;
DROP TABLE OPENK.INT_GROUPS_IN_LIST_HIST;
DROP TABLE OPENK.INT_LOCATION_HAS_POSTCODE_HIST;
DROP TABLE OPENK.INT_REGIONS_IN_LOCATION_HIST;
DROP TABLE OPENK.LOCATION_HIST;
DROP TABLE OPENK.LOCATION_FOR_BRANCH_HIST;
DROP TABLE OPENK.ORGANISATION_HIST;
DROP TABLE OPENK.POSTCODE_HIST;
DROP TABLE OPENK.REGION_HIST;
DROP TABLE OPENK.STANDBY_DURATION_HIST;
DROP TABLE OPENK.STANDBY_GROUP_HIST;
DROP TABLE OPENK.STANDBY_LIST_HIST;
DROP TABLE OPENK.STANDBY_PLANNING_STATUS_HIST;
DROP TABLE OPENK.STANDBY_SCHEDULE_BODY_HIST;
DROP TABLE OPENK.STANDBY_SCHEDULE_HEADER_HIST;
DROP TABLE OPENK.STANDBY_SCHEDULE_HISTORY_HIST;
DROP TABLE OPENK.STANDBY_STATUS_HIST;
DROP TABLE OPENK.STANDBY_USER_HIST;
DROP TABLE OPENK.USER_FUNCTION_HIST;
DROP TABLE OPENK.USER_HAS_USER_FUNCTION_HIST;
DROP TABLE OPENK.USER_IN_REGION_HIST;
DROP TABLE OPENK.USER_IN_STANDBY_GROUP_HIST;
DROP TABLE OPENK.INT_STBY_GROUP_HAS_FUNC_HIST;

--address_hist
CREATE TABLE OPENK.ADDRESS_HIST AS (SELECT * FROM OPENK.ADDRESS);
DELETE FROM OPENK.ADDRESS_HIST;
DROP sequence OPENK.ADDRESS_HIST_SEQ_ID;
CREATE sequence OPENK.ADDRESS_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.ADDRESS_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.ADDRESS_HIST ADD CONSTRAINT PK_ADDRESS_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.ADDRESS_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.ADDRESS REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.ADDRESS_HIST( id, community, community_suffix, housenumber, latitude, longitude, postcode, street, url_map, wgs_84_zone, operation, stamp) VALUES(:new.id, :new.community, :new.community_suffix, :new.housenumber, :new.latitude, :new.longitude, :new.postcode, :new.street, :new.url_map, :new.wgs_84_zone, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.ADDRESS_HIST(id, community, community_suffix, housenumber, latitude, longitude, postcode, street, url_map, wgs_84_zone, operation, stamp) VALUES(:new.id, :new.community, :new.community_suffix, :new.housenumber, :new.latitude, :new.longitude, :new.postcode, :new.street, :new.url_map, :new.wgs_84_zone, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.ADDRESS_HIST(id, community, community_suffix, housenumber, latitude, longitude, postcode, street, url_map, wgs_84_zone, operation, stamp) VALUES(:old.id, :old.community, :old.community_suffix, :old.housenumber, :old.latitude, :old.longitude, :old.postcode, :old.street, :old.url_map, :old.wgs_84_zone, 'D', SYSDATE); END IF; END;

--branch_hist
CREATE TABLE OPENK.BRANCH_HIST AS (SELECT * FROM OPENK.BRANCH);
DELETE FROM OPENK.BRANCH_HIST;
DROP sequence OPENK.BRANCH_HIST_SEQ_ID;
CREATE sequence OPENK.BRANCH_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.BRANCH_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.BRANCH_HIST ADD CONSTRAINT PK_BRANCH_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.BRANCH_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.BRANCH REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.BRANCH_HIST( id, title, operation, stamp) VALUES(:new.id, :new.title, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.BRANCH_HIST(id, title, operation, stamp) VALUES(:new.id, :new.title, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.BRANCH_HIST(id, title, operation, stamp) VALUES(:old.id, :old.title, 'D', SYSDATE); END IF; END;

--CALENDAR_hist
CREATE TABLE OPENK.CALENDAR_HIST AS (SELECT * FROM OPENK.CALENDAR);
DELETE FROM OPENK.CALENDAR_HIST;
DROP sequence OPENK.CALENDAR_HIST_SEQ_ID;
CREATE sequence OPENK.CALENDAR_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.CALENDAR_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.CALENDAR_HIST ADD CONSTRAINT PK_CALENDAR_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.CALENDAR_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.CALENDAR REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.CALENDAR_HIST( id, non_business_date, modification_date, title, repeat, shorttext, valid_from, valid_to, operation, stamp) VALUES(:new.id, :new.non_business_date, :new.modification_date, :new.title, :new.repeat, :new.shorttext, :new.valid_from, :new.valid_to, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.CALENDAR_HIST( id, non_business_date, modification_date, title, repeat, shorttext, valid_from, valid_to, operation, stamp) VALUES(:new.id, :new.non_business_date, :new.modification_date, :new.title, :new.repeat, :new.shorttext, :new.valid_from, :new.valid_to, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.CALENDAR_HIST( id, non_business_date, modification_date, title, repeat, shorttext, valid_from, valid_to, operation, stamp) VALUES(:old.id, :old.non_business_date, :old.modification_date, :old.title, :old.repeat, :old.shorttext, :old.valid_from, :old.valid_to, 'D', SYSDATE); END IF; END;

--CONTACT_DATA_hist
CREATE TABLE OPENK.CONTACT_DATA_HIST AS (SELECT * FROM OPENK.CONTACT_DATA);
DELETE FROM OPENK.CONTACT_DATA_HIST;
DROP sequence OPENK.CONTACT_DATA_HIST_SEQ_ID;
CREATE sequence OPENK.CONTACT_DATA_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.CONTACT_DATA_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.CONTACT_DATA_HIST ADD CONSTRAINT PK_CONTACT_DATA_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.CONTACT_DATA_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.CONTACT_DATA REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.CONTACT_DATA_HIST( id, cellphone, email, is_private, pager, phone, radiocomm, operation, stamp) VALUES(:new.id, :new.cellphone, :new.email, :new.is_private, :new.pager, :new.phone, :new.radiocomm, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.CONTACT_DATA_HIST( id, cellphone, email, is_private, pager, phone, radiocomm, operation, stamp) VALUES(:new.id, :new.cellphone, :new.email, :new.is_private, :new.pager, :new.phone, :new.radiocomm, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.CONTACT_DATA_HIST( id, cellphone, email, is_private, pager, phone, radiocomm, operation, stamp) VALUES(:old.id, :old.cellphone, :old.email, :old.is_private, :old.pager, :old.phone, :old.radiocomm, 'D', SYSDATE); END IF; END;

--INT_BRANCHES_FOR_GROUP_hist
CREATE TABLE OPENK.INT_BRANCHES_FOR_GROUP_HIST AS (SELECT * FROM OPENK.INT_BRANCHES_FOR_GROUPS);
DELETE FROM OPENK.INT_BRANCHES_FOR_GROUP_HIST;
DROP sequence OPENK.INT_BRAN_FOR_GROUP_HI_SEQ_ID;
CREATE sequence OPENK.INT_BRAN_FOR_GROUP_HI_SEQ_ID increment by 1;
ALTER TABLE OPENK.INT_BRANCHES_FOR_GROUP_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.INT_BRANCHES_FOR_GROUP_HIST ADD CONSTRAINT PK_INT_BRANCHES_FOR_GROUP_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.INT_BRANCHES_FOR_GROUP_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.INT_BRANCHES_FOR_GROUPS REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.INT_BRANCHES_FOR_GROUP_HIST( branch_id, standby_group_id, operation, stamp) VALUES(:new.branch_id, :new.standby_group_id, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.INT_BRANCHES_FOR_GROUP_HIST( branch_id, standby_group_id, operation, stamp) VALUES(:new.branch_id, :new.standby_group_id, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.INT_BRANCHES_FOR_GROUP_HIST( branch_id, standby_group_id, operation, stamp) VALUES(:old.branch_id, :old.standby_group_id, 'D', SYSDATE); END IF; END;

--INT_GROUPS_IN_LIST_hist
CREATE TABLE OPENK.INT_GROUPS_IN_LIST_HIST AS (SELECT * FROM OPENK.STANDBY_LIST_HAS_STANDBY_GROUP);
DELETE FROM OPENK.INT_GROUPS_IN_LIST_HIST;
DROP sequence OPENK.INT_GROUPS_IN_LIST_HIST_SEQ_ID;
CREATE sequence OPENK.INT_GROUPS_IN_LIST_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.INT_GROUPS_IN_LIST_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.INT_GROUPS_IN_LIST_HIST ADD CONSTRAINT PK_INT_GROUPS_IN_LIST_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.INT_GROUPS_IN_LIST_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.STANDBY_LIST_HAS_STANDBY_GROUP REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.INT_GROUPS_IN_LIST_HIST( id, position, standby_group_id, standby_list_id, operation, stamp) VALUES(:new.id, :new.position, :new.standby_group_id, :new.standby_list_id, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.INT_GROUPS_IN_LIST_HIST( id, position, standby_group_id, standby_list_id, operation, stamp) VALUES(:new.id, :new.position, :new.standby_group_id, :new.standby_list_id, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.INT_GROUPS_IN_LIST_HIST( id, position, standby_group_id, standby_list_id, operation, stamp) VALUES(:old.id, :old.position, :old.standby_group_id, :old.standby_list_id, 'D', SYSDATE); END IF; END;

--INT_LOCATION_HAS_POSTCODE_hist
CREATE TABLE OPENK.INT_LOCATION_HAS_POSTCODE_HIST AS (SELECT * FROM OPENK.INT_LOCATION_HAS_POSTCODE);
DELETE FROM OPENK.INT_LOCATION_HAS_POSTCODE_HIST;
DROP sequence OPENK.INT_LOC_HAS_POSTC_HIST_SEQ_ID;
CREATE sequence OPENK.INT_LOC_HAS_POSTC_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.INT_LOCATION_HAS_POSTCODE_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.INT_LOCATION_HAS_POSTCODE_HIST ADD CONSTRAINT PK_INT_LOC_HAS_POSTCODE_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.INT_LOC_HAS_POSTCODE_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.INT_LOCATION_HAS_POSTCODE REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.INT_LOCATION_HAS_POSTCODE_HIST( location_id, postcode_id, operation, stamp) VALUES(:new.location_id, :new.postcode_id,'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.INT_LOCATION_HAS_POSTCODE_HIST( location_id, postcode_id, operation, stamp) VALUES(:new.location_id, :new.postcode_id,'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.INT_LOCATION_HAS_POSTCODE_HIST( location_id, postcode_id, operation, stamp) VALUES(:old.location_id, :old.postcode_id,'D', SYSDATE); END IF; END;

--INT_REGIONS_IN_LOCATION_hist
CREATE TABLE OPENK.INT_REGIONS_IN_LOCATION_HIST AS (SELECT * FROM OPENK.INT_REGIONS_IN_LOCATION);
DELETE FROM OPENK.INT_REGIONS_IN_LOCATION_HIST;
DROP sequence OPENK.INT_REGIONS_IN_LOC_HIST_SEQ_ID;
CREATE sequence OPENK.INT_REGIONS_IN_LO_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.INT_REGIONS_IN_LOCATION_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.INT_REGIONS_IN_LOCATION_HIST ADD CONSTRAINT PK_INT_REGIONS_IN_LOC_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.INT_REGIONS_IN_LOCATION_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.INT_REGIONS_IN_LOCATION REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.INT_REGIONS_IN_LOCATION_HIST( location_id, region_id, operation, stamp) VALUES(:new.location_id, :new.region_id, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.INT_REGIONS_IN_LOCATION_HIST( location_id, region_id, operation, stamp) VALUES(:new.location_id, :new.region_id, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.INT_REGIONS_IN_LOCATION_HIST( location_id, region_id, operation, stamp) VALUES(:old.location_id, :old.region_id, 'D', SYSDATE); END IF; END;

--LOCATION_hist
CREATE TABLE OPENK.LOCATION_HIST AS (SELECT * FROM OPENK.LOCATION);
DELETE FROM OPENK.LOCATION_HIST;
DROP sequence OPENK.LOCATION_HIST_SEQ_ID;
CREATE sequence OPENK.LOCATION_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.LOCATION_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.LOCATION_HIST ADD CONSTRAINT PK_LOCATION_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.LOCATION_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.LOCATION REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.LOCATION_HIST( id, community, district, wgs_84_latitude_district, wgs_84_longitude_district, shorttext, title, wgs_84_zone_district, operation, stamp) VALUES(:new.id, :new.community, :new.district, :new.wgs_84_latitude_district, :new.wgs_84_longitude_district, :new.shorttext, :new.title, :new.wgs_84_zone_district, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.LOCATION_HIST( id, community, district, wgs_84_latitude_district, wgs_84_longitude_district, shorttext, title, wgs_84_zone_district, operation, stamp) VALUES(:new.id, :new.community, :new.district, :new.wgs_84_latitude_district, :new.wgs_84_longitude_district, :new.shorttext, :new.title, :new.wgs_84_zone_district, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.LOCATION_HIST( id, community, district, wgs_84_latitude_district, wgs_84_longitude_district, shorttext, title, wgs_84_zone_district, operation, stamp) VALUES(:old.id, :old.community, :old.district, :old.wgs_84_latitude_district, :old.wgs_84_longitude_district, :old.shorttext, :old.title, :old.wgs_84_zone_district, 'D', SYSDATE); END IF; END;

--LOCATION_FOR_BRANCH_hist
CREATE TABLE OPENK.LOCATION_FOR_BRANCH_HIST AS (SELECT * FROM OPENK.LOCATION_FOR_BRANCH);
DELETE FROM OPENK.LOCATION_FOR_BRANCH_HIST;
DROP sequence OPENK.LOC_FOR_BRANCH_HIST_SEQ_ID;
CREATE sequence OPENK.LOC_FOR_BRANCH_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.LOCATION_FOR_BRANCH_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.LOCATION_FOR_BRANCH_HIST ADD CONSTRAINT PK_LOCATION_FOR_BRANCH_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.LOCATION_FOR_BRANCH_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.LOCATION_FOR_BRANCH REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.LOCATION_FOR_BRANCH_HIST( id, valid_from, valid_to, branch_id, location_id, operation, stamp) VALUES(:new.id, :new.valid_from, :new.valid_to, :new.branch_id, :new.location_id, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.LOCATION_FOR_BRANCH_HIST( id, valid_from, valid_to, branch_id, location_id, operation, stamp) VALUES(:new.id, :new.valid_from, :new.valid_to, :new.branch_id, :new.location_id, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.LOCATION_FOR_BRANCH_HIST( id, valid_from, valid_to, branch_id, location_id, operation, stamp) VALUES(:old.id, :old.valid_from, :old.valid_to, :old.branch_id, :old.location_id, 'D', SYSDATE); END IF; END;

--ORGANISATION_hist
CREATE TABLE OPENK.ORGANISATION_HIST AS (SELECT * FROM OPENK.ORGANISATION);
DELETE FROM OPENK.ORGANISATION_HIST;
DROP sequence OPENK.ORGANISATION_HIST_SEQ_ID;
CREATE sequence OPENK.ORGANISATION_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.ORGANISATION_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.ORGANISATION_HIST ADD CONSTRAINT PK_ORGANISATION_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.ORGANISATION_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.ORGANISATION REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.ORGANISATION_HIST( id, orga_name, address_id, operation, stamp) VALUES(:new.id, :new.orga_name, :new.address_id, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.ORGANISATION_HIST( id, orga_name, address_id, operation, stamp) VALUES(:new.id, :new.orga_name, :new.address_id, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.ORGANISATION_HIST( id, orga_name, address_id, operation, stamp) VALUES(:old.id, :old.orga_name, :old.address_id, 'D', SYSDATE); END IF; END;

--POSTCODE_hist
CREATE TABLE OPENK.POSTCODE_HIST AS (SELECT * FROM OPENK.POSTCODE);
DELETE FROM OPENK.POSTCODE_HIST;
DROP sequence OPENK.POSTCODE_HIST_SEQ_ID;
CREATE sequence OPENK.POSTCODE_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.POSTCODE_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.POSTCODE_HIST ADD CONSTRAINT PK_POSTCODE_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.POSTCODE_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.POSTCODE REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.POSTCODE_HIST( id, postcode, operation, stamp) VALUES(:new.id, :new.postcode, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.POSTCODE_HIST( id, postcode, operation, stamp) VALUES(:new.id, :new.postcode, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.POSTCODE_HIST( id, postcode, operation, stamp) VALUES(:old.id, :old.postcode, 'D', SYSDATE); END IF; END;

--REGION_hist
CREATE TABLE OPENK.REGION_HIST AS (SELECT * FROM OPENK.REGION);
DELETE FROM OPENK.REGION_HIST;
DROP sequence OPENK.REGION_HIST_SEQ_ID;
CREATE sequence OPENK.REGION_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.REGION_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.REGION_HIST ADD CONSTRAINT PK_REGION_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.REGION_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.REGION REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.REGION_HIST( id, region_name, operation, stamp) VALUES(:new.id, :new.region_name, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.REGION_HIST( id, region_name, operation, stamp) VALUES(:new.id, :new.region_name, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.REGION_HIST( id, region_name, operation, stamp) VALUES(:old.id, :old.region_name, 'D', SYSDATE); END IF; END;

--STANDBY_DURATION_hist
CREATE TABLE OPENK.STANDBY_DURATION_HIST AS (SELECT * FROM OPENK.STANDBY_DURATION);
DELETE FROM OPENK.STANDBY_DURATION_HIST;
DROP sequence OPENK.STANDBY_DURATION_HIST_SEQ_ID;
CREATE sequence OPENK.STANDBY_DURATION_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.STANDBY_DURATION_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.STANDBY_DURATION_HIST ADD CONSTRAINT PK_STANDBY_DURATION_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.STANDBY_DURATION_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.STANDBY_DURATION REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.STANDBY_DURATION_HIST( id, modification_date, next_user_in_next_duration, valid_day_from, valid_day_to, valid_from, valid_to, standby_group_id, operation, stamp) VALUES(:new.id, :new.modification_date, :new.next_user_in_next_duration, :new.valid_day_from, :new.valid_day_to, :new.valid_from, :new.valid_to, :new.standby_group_id, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.STANDBY_DURATION_HIST( id, modification_date, next_user_in_next_duration, valid_day_from, valid_day_to, valid_from, valid_to, standby_group_id, operation, stamp) VALUES(:new.id, :new.modification_date, :new.next_user_in_next_duration, :new.valid_day_from, :new.valid_day_to, :new.valid_from, :new.valid_to, :new.standby_group_id, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.STANDBY_DURATION_HIST( id, modification_date, next_user_in_next_duration, valid_day_from, valid_day_to, valid_from, valid_to, standby_group_id, operation, stamp) VALUES(:old.id, :old.modification_date, :old.next_user_in_next_duration, :old.valid_day_from, :old.valid_day_to, :old.valid_from, :old.valid_to, :old.standby_group_id, 'D', SYSDATE); END IF; END;

--STANDBY_GROUP_hist
CREATE TABLE OPENK.STANDBY_GROUP_HIST AS (SELECT * FROM OPENK.STANDBY_GROUP);
DELETE FROM OPENK.STANDBY_GROUP_HIST;
DROP sequence OPENK.STANDBY_GROUP_HIST_SEQ_ID;
CREATE sequence OPENK.STANDBY_GROUP_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.STANDBY_GROUP_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.STANDBY_GROUP_HIST ADD CONSTRAINT PK_STANDBY_GROUP_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.STANDBY_GROUP_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.STANDBY_GROUP REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.STANDBY_GROUP_HIST( id, extend_standby_time, modification_date, next_user_in_next_cycle, note, title, operation, stamp) VALUES(:new.id, :new.extend_standby_time, :new.modification_date, :new.next_user_in_next_cycle, :new.note, :new.title, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.STANDBY_GROUP_HIST( id, extend_standby_time, modification_date, next_user_in_next_cycle, note, title, operation, stamp) VALUES(:new.id, :new.extend_standby_time, :new.modification_date, :new.next_user_in_next_cycle, :new.note, :new.title, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.STANDBY_GROUP_HIST( id, extend_standby_time, modification_date, next_user_in_next_cycle, note, title, operation, stamp) VALUES(:old.id, :old.extend_standby_time, :old.modification_date, :old.next_user_in_next_cycle, :old.note, :old.title, 'D', SYSDATE); END IF; END;

--STANDBY_LIST_hist
CREATE TABLE OPENK.STANDBY_LIST_HIST AS (SELECT * FROM OPENK.STANDBY_LIST);
DELETE FROM OPENK.STANDBY_LIST_HIST;
DROP sequence OPENK.STANDBY_LIST_HIST_SEQ_ID;
CREATE sequence OPENK.STANDBY_LIST_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.STANDBY_LIST_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.STANDBY_LIST_HIST ADD CONSTRAINT PK_STANDBY_LIST_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.STANDBY_LIST_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.STANDBY_LIST REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.STANDBY_LIST_HIST( id, modification_date, title, operation, stamp) VALUES(:new.id, :new.modification_date, :new.title, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.STANDBY_LIST_HIST( id, modification_date, title, operation, stamp) VALUES(:new.id, :new.modification_date, :new.title, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.STANDBY_LIST_HIST( id, modification_date, title, operation, stamp) VALUES(:old.id, :old.modification_date, :old.title, 'D', SYSDATE); END IF; END;

--STANDBY_PLANNING_STATUS_hist
CREATE TABLE OPENK.STANDBY_PLANNING_STATUS_HIST AS (SELECT * FROM OPENK.STANDBY_PLANNING_STATUS);
DELETE FROM OPENK.STANDBY_PLANNING_STATUS_HIST;
DROP sequence OPENK.STBY_PLAN_STATUS_HIST_SEQ_ID;
CREATE sequence OPENK.STBY_PLAN_STATUS_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.STANDBY_PLANNING_STATUS_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.STANDBY_PLANNING_STATUS_HIST ADD CONSTRAINT PK_STBY_PLAN_STATUS_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.STANDBY_PLANNING_STATUS_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.STANDBY_PLANNING_STATUS REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.STANDBY_PLANNING_STATUS_HIST( id, title, operation, stamp) VALUES(:new.id,:new.title, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.STANDBY_PLANNING_STATUS_HIST( id, title, operation, stamp) VALUES(:new.id, :new.title, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.STANDBY_PLANNING_STATUS_HIST( id, title, operation, stamp) VALUES(:old.id, :old.title, 'D', SYSDATE); END IF; END;

--STANDBY_SCHEDULE_BODY_hist
CREATE TABLE OPENK.STANDBY_SCHEDULE_BODY_HIST AS (SELECT * FROM OPENK.STANDBY_SCHEDULE_BODY);
DELETE FROM OPENK.STANDBY_SCHEDULE_BODY_HIST;
DROP sequence OPENK.STBY_SCHEDULE_BODY_HIST_SEQ_ID;
CREATE sequence OPENK.STBY_SCHEDULE_BODY_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.STANDBY_SCHEDULE_BODY_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.STANDBY_SCHEDULE_BODY_HIST ADD CONSTRAINT PK_STANDBY_SCHEDULE_BODY_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.STANDBY_SCHEDULE_BODY_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.STANDBY_SCHEDULE_BODY REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.STANDBY_SCHEDULE_BODY_HIST( id, modification_date, modification_by, modification_cause, valid_from, valid_to, standby_group_id, status_id, user_id, operation, stamp) VALUES(:new.id, :new.modification_date, :new.modification_by, :new.modification_cause, :new.valid_from, :new.valid_to, :new.standby_group_id, :new.status_id, :new.user_id, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.STANDBY_SCHEDULE_BODY_HIST(id, modification_date, modification_by, modification_cause, valid_from, valid_to, standby_group_id, status_id, user_id, operation, stamp) VALUES(:new.id, :new.modification_date, :new.modification_by, :new.modification_cause, :new.valid_from, :new.valid_to, :new.standby_group_id, :new.status_id, :new.user_id, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.STANDBY_SCHEDULE_BODY_HIST(id, modification_date, modification_by, modification_cause, valid_from, valid_to, standby_group_id, status_id, user_id, operation, stamp) VALUES(:old.id, :old.modification_date, :old.modification_by, :old.modification_cause, :old.valid_from, :old.valid_to, :old.standby_group_id, :old.status_id, :old.user_id, 'D', SYSDATE); END IF; END;

--STANDBY_SCHEDULE_HEADER_hist
CREATE TABLE OPENK.STANDBY_SCHEDULE_HEADER_HIST AS (SELECT * FROM OPENK.STANDBY_SCHEDULE_HEADER);
DELETE FROM OPENK.STANDBY_SCHEDULE_HEADER_HIST;
DROP sequence OPENK.STBY_SCHEDULE_HDR_HIST_SEQ_ID;
CREATE sequence OPENK.STBY_SCHEDULE_HDR_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.STANDBY_SCHEDULE_HEADER_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.STANDBY_SCHEDULE_HEADER_HIST ADD CONSTRAINT PK_STBY_SCHEDULE_HEADER_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.STANDBY_SCHEDULE_HEADER_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.STANDBY_SCHEDULE_HEADER REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.STANDBY_SCHEDULE_HEADER_HIST( id, modification_date, title, valid_from, valid_to, status_id, operation, stamp) VALUES(:new.id, :new.modification_date, :new.title, :new.valid_from, :new.valid_to, :new.status_id, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.STANDBY_SCHEDULE_HEADER_HIST( id, modification_date, title, valid_from, valid_to, status_id, operation, stamp) VALUES(:new.id, :new.modification_date, :new.title, :new.valid_from, :new.valid_to, :new.status_id, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.STANDBY_SCHEDULE_HEADER_HIST( id, modification_date, title, valid_from, valid_to, status_id, operation, stamp) VALUES(:old.id, :old.modification_date, :old.title, :old.valid_from, :old.valid_to, :old.status_id, 'D', SYSDATE); END IF; END;

--STANDBY_SCHEDULE_HISTORY_hist
CREATE TABLE OPENK.STANDBY_SCHEDULE_HISTORY_HIST AS (SELECT * FROM OPENK.STANDBY_SCHEDULE_HISTORY);
DELETE FROM OPENK.STANDBY_SCHEDULE_HISTORY_HIST;
DROP sequence OPENK.STBY_SCHEDULE_HI_HIST_SEQ_ID;
CREATE sequence OPENK.STBY_SCHEDULE_HI_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.STANDBY_SCHEDULE_HISTORY_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.STANDBY_SCHEDULE_HISTORY_HIST ADD CONSTRAINT PK_STBY_SCHEDULE_HI_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.STANDBY_SCHEDULE_HISTORY_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.STANDBY_SCHEDULE_HISTORY REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.STANDBY_SCHEDULE_HISTORY_HIST( id, changed_by, file_data, file_hash_value, modification_date, schedule_header, operation, stamp) VALUES(:new.id, :new.changed_by, :new.file_data, :new.file_hash_value, :new.modification_date, :new.schedule_header, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.STANDBY_SCHEDULE_HISTORY_HIST( id, changed_by, file_data, file_hash_value, modification_date, schedule_header, operation, stamp) VALUES(:new.id, :new.changed_by, :new.file_data, :new.file_hash_value, :new.modification_date, :new.schedule_header, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.STANDBY_SCHEDULE_HISTORY_HIST( id, changed_by, file_data, file_hash_value, modification_date, schedule_header, operation, stamp) VALUES(:old.id, :old.changed_by, :old.file_data, :old.file_hash_value, :old.modification_date, :old.schedule_header, 'D', SYSDATE); END IF; END;

--STANDBY_STATUS_hist
CREATE TABLE OPENK.STANDBY_STATUS_HIST AS (SELECT * FROM OPENK.STANDBY_STATUS);
DELETE FROM OPENK.STANDBY_STATUS_HIST;
DROP sequence OPENK.STANDBY_STATUS_HIST_SEQ_ID;
CREATE sequence OPENK.STANDBY_STATUS_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.STANDBY_STATUS_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.STANDBY_STATUS_HIST ADD CONSTRAINT PK_STANDBY_STATUS_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.STANDBY_STATUS_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.STANDBY_STATUS REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.STANDBY_STATUS_HIST( id, title, operation, stamp) VALUES(:new.id, :new.title, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.STANDBY_STATUS_HIST(id, title, operation, stamp) VALUES(:new.id, :new.title, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.STANDBY_STATUS_HIST(id, title, operation, stamp) VALUES(:old.id, :old.title, 'D', SYSDATE); END IF; END;

--STANDBY_USER_hist
CREATE TABLE OPENK.STANDBY_USER_HIST AS (SELECT * FROM OPENK.STANDBY_USER);
DELETE FROM OPENK.STANDBY_USER_HIST;
DROP sequence OPENK.STANDBY_USER_HIST_SEQ_ID;
CREATE sequence OPENK.STANDBY_USER_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.STANDBY_USER_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.STANDBY_USER_HIST ADD CONSTRAINT PK_STANDBY_USER_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.STANDBY_USER_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.STANDBY_USER REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.STANDBY_USER_HIST( id, firstname, hr_number, is_company, lastname, modification_date, notes, user_key, valid_from, valid_to, business_contact_id, organisation_id, private_address_id, private_contact_id, operation, stamp) VALUES(:new.id, :new.firstname, :new.hr_number, :new.is_company, :new.lastname, :new.modification_date, :new.notes, :new.user_key, :new.valid_from, :new.valid_to, :new.business_contact_id, :new.organisation_id, :new.private_address_id, :new.private_contact_id, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.STANDBY_USER_HIST( id, firstname, hr_number, is_company, lastname, modification_date, notes, user_key, valid_from, valid_to, business_contact_id, organisation_id, private_address_id, private_contact_id, operation, stamp) VALUES(:new.id, :new.firstname, :new.hr_number, :new.is_company, :new.lastname, :new.modification_date, :new.notes, :new.user_key, :new.valid_from, :new.valid_to, :new.business_contact_id, :new.organisation_id, :new.private_address_id, :new.private_contact_id, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.STANDBY_USER_HIST( id, firstname, hr_number, is_company, lastname, modification_date, notes, user_key, valid_from, valid_to, business_contact_id, organisation_id, private_address_id, private_contact_id, operation, stamp) VALUES(:old.id, :old.firstname, :old.hr_number, :old.is_company, :old.lastname, :old.modification_date, :old.notes, :old.user_key, :old.valid_from, :old.valid_to, :old.business_contact_id, :old.organisation_id, :old.private_address_id, :old.private_contact_id, 'D', SYSDATE); END IF; END;

--USER_FUNCTION_hist
CREATE TABLE OPENK.USER_FUNCTION_HIST AS (SELECT * FROM OPENK.USER_FUNCTION);
DELETE FROM OPENK.USER_FUNCTION_HIST;
DROP sequence OPENK.USER_FUNCTION_HIST_SEQ_ID;
CREATE sequence OPENK.USER_FUNCTION_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.USER_FUNCTION_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.USER_FUNCTION_HIST ADD CONSTRAINT PK_USER_FUNCTION_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.USER_FUNCTION_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.USER_FUNCTION REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.USER_FUNCTION_HIST( id, function_name, operation, stamp) VALUES (:new.id, :new.function_name, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.USER_FUNCTION_HIST( id, function_name, operation, stamp) VALUES(:new.id, :new.function_name, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.USER_FUNCTION_HIST(id, function_name, operation, stamp) VALUES(:old.id, :old.function_name, 'D', SYSDATE); END IF; END;

--USER_HAS_USER_HAS_USER_FUNCTION_hist
CREATE TABLE OPENK.USER_HAS_USER_FUNCTION_HIST AS (SELECT * FROM OPENK.USER_HAS_USER_FUNCTION);
DELETE FROM OPENK.USER_HAS_USER_FUNCTION_HIST;
DROP sequence OPENK.USER_HAS_USER_FUNC_HIST_SEQ_ID;
CREATE sequence OPENK.USER_HAS_USER_FUNC_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.USER_HAS_USER_FUNCTION_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.USER_HAS_USER_FUNCTION_HIST ADD CONSTRAINT PK_USER_HAS_USER_FUNCTION_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.USER_HAS_USER_FUNCTION_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.USER_HAS_USER_FUNCTION REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.USER_HAS_USER_FUNCTION_HIST( id, user_id, user_function_id, operation, stamp) VALUES (:new.id, :new.user_id, :new.user_function_id, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.USER_HAS_USER_FUNCTION_HIST( id, user_id, user_function_id, operation, stamp) VALUES(:new.id, :new.user_id, :new.user_function_id, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.USER_HAS_USER_FUNCTION_HIST(id, user_id, user_function_id, operation, stamp) VALUES(:old.id, :old.user_id, :old.user_function_id, 'D', SYSDATE); END IF; END;

--USER_IN_REGION_hist
CREATE TABLE OPENK.USER_IN_REGION_HIST AS (SELECT * FROM OPENK.USER_IN_REGION);
DELETE FROM OPENK.USER_IN_REGION_HIST;
DROP sequence OPENK.USER_IN_REGION_HIST_SEQ_ID;
CREATE sequence OPENK.USER_IN_REGION_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.USER_IN_REGION_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.USER_IN_REGION_HIST ADD CONSTRAINT PK_USER_IN_REGION_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.USER_IN_REGION_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.USER_IN_REGION REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.USER_IN_REGION_HIST( id, region_id, user_id, operation, stamp) VALUES(:new.id, :new.region_id, :new.user_id, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.USER_IN_REGION_HIST( id, region_id, user_id, operation, stamp) VALUES(:new.id, :new.region_id, :new.user_id, 'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.USER_IN_REGION_HIST( id, region_id, user_id, operation, stamp) VALUES(:old.id, :old.region_id, :old.user_id, 'D', SYSDATE); END IF; END;

--USER_IN_STANDBY_GROUP_hist
CREATE TABLE OPENK.USER_IN_STANDBY_GROUP_HIST AS (SELECT * FROM OPENK.USER_IN_STANDBY_GROUP);
DELETE FROM OPENK.USER_IN_STANDBY_GROUP_HIST;
DROP sequence OPENK.USER_IN_STBY_GROUP_HIST_SEQ_ID;
CREATE sequence OPENK.USER_IN_STBY_GROUP_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.USER_IN_STANDBY_GROUP_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.USER_IN_STANDBY_GROUP_HIST ADD CONSTRAINT PK_USER_IN_STANDBY_GROUP_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.USER_IN_STANDBY_GROUP_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.USER_IN_STANDBY_GROUP REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.USER_IN_STANDBY_GROUP_HIST( id, position, valid_from, valid_to, standby_group_id, user_id, operation, stamp) VALUES(:new.id, :new.position, :new.valid_from, :new.valid_to, :new.standby_group_id, :new.user_id, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.USER_IN_STANDBY_GROUP_HIST( id, position, valid_from, valid_to, standby_group_id, user_id, operation, stamp) VALUES(:new.id, :new.position, :new.valid_from, :new.valid_to, :new.standby_group_id, :new.user_id,  'U', SYSDATE); ELSIF (DELETING) THEN INSERT INTO OPENK.USER_IN_STANDBY_GROUP_HIST( id, position, valid_from, valid_to, standby_group_id, user_id, operation, stamp) VALUES(:old.id, :old.position, :old.valid_from, :old.valid_to, :old.standby_group_id, :old.user_id,  'D', SYSDATE); END IF; END;

--INT_STANDBY_GROUP_HAS_FUNCTION_hist
CREATE TABLE OPENK.INT_STBY_GROUP_HAS_FUNC_HIST AS (SELECT * FROM OPENK.INT_STANDBYGROUP_HAS_FUNCTION);
DELETE FROM OPENK.INT_STBY_GROUP_HAS_FUNC_HIST;
DROP sequence OPENK.INT_GROUP_HAS_FUNC_HIST_SEQ_ID;
CREATE sequence OPENK.INT_GROUP_HAS_FUNC_HIST_SEQ_ID increment by 1;
ALTER TABLE OPENK.INT_STBY_GROUP_HAS_FUNC_HIST ADD(HIST_ID number(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, operation char(1), stamp timestamp);
ALTER TABLE OPENK.INT_STBY_GROUP_HAS_FUNC_HIST ADD CONSTRAINT PK_INT_GROUP_HAS_FUNCTION_HIST PRIMARY KEY (HIST_ID);
CREATE OR REPLACE TRIGGER OPENK.INT_GROUP_HAS_FUNCTION_AUDIT BEFORE INSERT OR DELETE OR UPDATE ON OPENK.INT_STANDBYGROUP_HAS_FUNCTION REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF (INSERTING) THEN INSERT INTO OPENK.INT_STBY_GROUP_HAS_FUNC_HIST( group_id, user_function_id, operation, stamp) VALUES(:new.group_id, :new.user_function_id, 'I', SYSDATE); ELSIF (UPDATING) THEN INSERT INTO OPENK.INT_STBY_GROUP_HAS_FUNC_HIST( group_id, user_function_id, operation, stamp) VALUES(:old.group_id, :old.user_function_id, 'D', SYSDATE); END IF; END;