| /*Address History*/ |
| call create_table_if_doesnt_exist('OPENK.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 FUNCTION process_address_audit() RETURNS TRIGGER AS $object_audit$ BEGIN IF (TG_OP = 'DELETE') THEN INSERT INTO {0}.address_hist SELECT nextval('address_hist_hist_id_seq'), 'D', now(), OLD.*; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO {0}.address_hist SELECT nextval('address_hist_hist_id_seq'), 'U', now(), NEW.*; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO {0}.address_hist SELECT nextval('address_hist_hist_id_seq'), 'I', now(), NEW.*; END IF; RETURN NULL; END; $object_audit$ LANGUAGE plpgsql; |
| DROP TRIGGER IF EXISTS address_audit on {0}.address; |
| CREATE TRIGGER address_audit AFTER INSERT OR UPDATE OR DELETE ON {0}.address FOR EACH ROW EXECUTE PROCEDURE process_address_audit(); |
