ktbst events

sql_examples.sql

-- create audit_log table
CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,		-- autoincrementing id - this is how ISP can reliably query this table
    object_type VARCHAR(255), 		-- transaction, customer, account
    object_id BIGINT,			-- initial object id
    product VARCHAR(20) NULL,		-- SET, FI, etc or null for non-transactions
    side VARCHAR(10) NULL,		-- BUY, SELL, etc or null
    action VARCHAR(10) NOT NULL,	-- INSERT, UPDATE, DELETE
    timestamp TIMESTAMP NOT NULL,	-- timestamp of the event
    old_row_data JSON NULL,		-- old row data or null (for inserts)
    new_row_data JSON NULL		-- new row data or null (for deletes)
);


-- trigger
CREATE OR REPLACE FUNCTION log_inserts() RETURNS TRIGGER AS $logs_insert_trigger$
	BEGIN
		IF (TG_OP = 'DELETE') THEN
            -- TODO
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            -- TODO
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
      	    INSERT INTO audit_log (
		        object_type,
		        object_id,
		        product,
		        side,
		        action,
		        timestamp,
		        old_row_data,
		        new_row_data        
		    )
		    VALUES(
		    	'logs', 		-- object type
		    	NEW.id,			-- object id
		        NULL,			-- product
		        NULL, 			-- side
		        'INSERT', 		-- action
		        CURRENT_TIMESTAMP, 	-- when did it happen
	 	        null, 			-- previous state is null beause it's a new row
		        to_json(NEW) 		-- new state of row, NEW is an instance of new row. to_json function convert whole row into json string.
		    );
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
	END;
$logs_insert_trigger$ LANGUAGE plpgsql;

CREATE TRIGGER logs_insert_trigger
AFTER INSERT ON logs FOR EACH ROW EXECUTE PROCEDURE log_inserts();