Re: A trigger question

Chansup Byun wrote:
> Hi,
>
> I'm trying to set up a trigger to prevent any duplicate entries into
> my table. But I couldn't figure out how to make my trigger work.
>
> I have a PostgreSQL 8.1.5 installation.
> I created the following table:
>
> CREATE TABLE attendance (
> attendance_id serial,
> service_date timestamp DEFAULT NOW(),
> service_type varchar(18),
> attendance_count integer,
> CONSTRAINT attendance_pk primary key (attendance_id)
> );
>
> INSERT INTO attendance (service_date, service_type, attendance_count )
> VALUES ('2006-05-01', 'First Service', 100);
> INSERT INTO attendance (service_date, service_type, attendance_count )
> VALUES ('2006-05-01', 'Second Service', 120);
>
> Now I create the following:
>
> CREATE FUNCTION history_check_fn() RETURNS trigger AS $history_check_fn$
> BEGIN
> -- Check that service_date and service_type are given
> IF NEW.service_date == OLD.service_date AND
> NEW.service_type == OLD.service_type THEN
> RAISE EXCEPTION 'Duplicate entry';
> END IF;
> RETURN;
> END;
> $history_check_fn$ LANGUAGE plpgsql;
>
> CREATE TRIGGER history_check_tr
> BEFORE INSERT OR UPDATE ON attendance
> FOR EACH ROW EXECUTE PROCEDURE history_check_fn();
>
> When I tried to insert a duplicate entry [same service_date and
> service_type], I got the following error:
>
> INSERT INTO attendance (service_date, service_type, attendance_count )
> VALUES ('2005-05-01', 'Second Service', 10);
> ERROR: record "old" is not assigned yet
> DETAIL: The tuple structure of a not-yet-assigned record is
> indeterminate.
> CONTEXT: PL/pgSQL function "history_check_fn" line 3 at if
>
> I couldn't figure out what I'm doing incorrectly.
> I appreciate if anyone can give me some advices.
>
I don't think OLD gets assigned for inserts. You should use TG_OP to
determine whether it is an insert or update calling the trigger.

http://www.postgresql.org/docs/8.2/static/plpgsql-trigger.html

However, you might simply be better off defining some unique indexes to
prevent duplicates.

-Jonathan