CREATE TABLE categories (
	id integer PRIMARY KEY,
	name text,
	description text
);

CREATE SEQUENCE change_number_seq;

CREATE TABLE categories_changes (
	changeNumber integer PRIMARY KEY DEFAULT nextval('change_number_seq'),
	changeTime timestamp with time zone NOT NULL,
	changeAction varchar(10) NOT NULL,
	changeUser varchar(20) NOT NULL,
	id integer NOT NULL
);

-- Used for defining the trigger.
CREATE LANGUAGE plpgsql;

-- I don't think we're supposed to store anything _except_ the
-- primary key in the "changes" table.  This might mean I need to
-- specify OLD.id instead of OLD.* where appropriate.
CREATE OR REPLACE FUNCTION categories_changes() RETURNS TRIGGER AS $cat_change$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO categories_changes SELECT 	nextval('change_number_seq'),
							CURRENT_TIMESTAMP(0), 'DELETE',
							user, OLD.id;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO categories_changes SELECT 	nextval('change_number_seq'),
							CURRENT_TIMESTAMP(0), 'MODIFY',
							user, NEW.id;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO categories_changes SELECT 	nextval('change_number_seq'),
							CURRENT_TIMESTAMP(0), 'MODIFY',
							user, NEW.id;
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$cat_change$ LANGUAGE plpgsql;

CREATE TRIGGER cat_change
AFTER INSERT OR UPDATE OR DELETE ON categories
    FOR EACH ROW EXECUTE PROCEDURE categories_changes();