> wouldn't a better situation be ADDING a record that is one higher, and
> then doing a select MAX()?
>
> The different triggers could do delete on the old records.
>
In my case that would not apply, because what I had was a need to keep a
"sequence" counter for each employee, so I added a column
("expense_report_seq") to the employee table:
CREATE TABLE paid.employee (
employee_pk serial,
person_pk int4 NOT NULL,
employee_identifier varchar(24),
hire_date date,
termination_date date,
health_insurance_code_pk int4,
performance_review_date date,
emergency_contact_pk int4,
labor_category_pk int4,
expense_report_seq int4 DEFAULT 0);
The incremented value of the expense_report_seq column is then inserted
in the expense_pk column for a new row in the expense table, thus keeping
a separate sequence for each employee:
CREATE TABLE paid.expense (
project_pk int4 NOT NULL,
organization_pk int4 NOT NULL,
employee_pk int4 NOT NULL,
expense_pk int4 NOT NULL,
expense_report_date date DEFAULT now() NOT NULL,
expense_date date DEFAULT now() NOT NULL,
CONSTRAINT expense_pkey PRIMARY KEY (project_pk, organization_pk,
employee_pk, expense_pk),
CONSTRAINT expense_fkey FOREIGN KEY (employee_pk) REFERENCES employee
(employee_pk)
) WITHOUT OIDS;
Then there is the trigger:
CREATE TRIGGER expense_bit BEFORE INSERT ON paid.expense FOR EACH ROW
EXECUTE PROCEDURE expense_bit();
where
CREATE FUNCTION paid.expense_bit() RETURNS trigger AS '
BEGIN
SELECT INTO NEW.expense_pk expense_report_next(new.employee_pk);
RETURN new;
END;
' LANGUAGE 'plpgsql' VOLATILE;
where
CREATE FUNCTION paid.expense_report_next(int4) RETURNS int4 AS '
DECLARE
l_employee_pk ALIAS FOR $1;
BEGIN
UPDATE employee
SET expense_report_seq = (expense_report_seq + 1)
WHERE employee_pk = l_employee_pk;
RETURN (SELECT expense_report_seq FROM employee WHERE employee_pk =
l_employee_pk) ;
END;' LANGUAGE 'plpgsql' VOLATILE;
Seems to work o.k., but this is not a large database with gazillions of
transactions.
~Berend Tober
