| /*Branch History*/ |
| call create_table_if_doesnt_exist('OPENK.BRACNH_HIST', 'CREATE TABLE OPENK.BRANCH_HIST AS (SELECT * FROM OPENK.BRACNH)'); |
| DELETE FROM OPENK.BRANCH_HIST; |
| DROP sequence OPENK.BRACNH_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 FUNCTION process_branch_audit() RETURNS TRIGGER AS $object_audit$ BEGIN IF (TG_OP = 'DELETE') THEN INSERT INTO {0}.branch_hist SELECT nextval('branch_hist_hist_id_seq'), 'D', now(), OLD.*; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO {0}.branch_hist SELECT nextval('branch_hist_hist_id_seq'), 'U', now(), NEW.*; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO {0}.branch_hist SELECT nextval('branch_hist_hist_id_seq'), 'I', now(), NEW.*; END IF; RETURN NULL; END; $object_audit$ LANGUAGE plpgsql; |
| DROP TRIGGER IF EXISTS branch_audit on {0}.branch; |
| CREATE TRIGGER branch_audit AFTER INSERT OR UPDATE OR DELETE ON {0}.branch FOR EACH ROW EXECUTE PROCEDURE process_branch_audit(); |
